2

I have a pb to make a trigger.

I have 3 tables and a georeferenced layer:

  • The common table contains: PKcom, INSEE
  • The cadastre table contains: PKcadastre, Fkbat, section, parcel
  • The building table contains: PKbat, FKcom

(The PKbat primary key of the building table is foreign key in the cadastre table because a building may be affected by several parcels).

  • The geometric layer contains (almost) all the geometries of the cadastral parcels (a parcel is identified by the common code and the cadastral reference).

When I add a building, I indicate the municipality and I note the cadastral reference(s) of the land in the database.

I would like to retrieve in the cadastre table the geometry corresponding to each “insee+section+parcel” of a new building. my trigger attempt below does not work...

CREATE OR REPLACE FUNCTION public.maj_cadastre()
    RETURNS trigger AS
    $BODY$
    BEGIN

    SELECT geom FROM public.geometry
     WHERE (SELECT (concat_ws ('-', substring (geo_parcelle,5,2)||
    substring (geo_parcelle,8,3), substring (geo_parcelle,14,2), substring (geo_parcelle,16,4)))
    from public.geometry) IN
     (SELECT UPPER(concat_ws('-',commune.insee, cadastre.section, cadastre.parcelle))
    FROM public.batiment as B INNER JOIN public.commune as C ON B.pk_com= C.pk_com
    AND INNER JOIN public.cadastre as R ON B.pk_bat= R.pk_bat)
     INTO cadastre.geom;
    RETURN NEW;
    END;
    $BODY$
    LANGUAGE plpgsql;

    CREATE TRIGGER maj_cadastre
    BEFORE INSERT
    ON public.cadastre
    FOR EACH ROW
    EXECUTE PROCEDURE maj_cadastre();

Ok, so I have 3 tables and a georeferenced layer.

At the beginning I have 3 tables in a bdd: commune, building and cadastral reference (a building is built on cadastral parcels of a commune and the buildings built "on horseback" on several communes are not desired).

-the common table contains: PKcom, INSEE -the cadastre table contains: PKref_cadas, Fkbat, section, parcel -the building table contains: PKbat, FKcom and then I have a geometric layer containing (almost) all the registered parcels.

For now, I can identify a given land (or cadastral ref).

in the bdd :

SELECT UPPER(concat_ws('-',insee, section, parcelle)) 
FROM public.ref_cadas 
NATURAL JOIN public.bien 
NATURAL JOIN public.commune 
WHERE bien.pk_bien = ref_cadas.pk_bien and bien.pk_com = commune.pk_com 

in the geometric layer:

SELECT (concat_ws ('-', substring (geo_parcelle,5,2)|| substring geo_parcelle,8,3), substring (geo_parcelle,14,2), substring (geo_parcelle,16,4))) 
FROM public.geo

I would now like to retrieve the geometry of (or) land (cadastral ref) when I register a new building.

A building is built in a city on a lot or on several lots / when the geometry exists, I would like to copy it in the table ref_cada.

Attached is a sketch to illustrate the purpose:

illustration

4
  • 1
    Please Edit the question to specify what error or other behavior you encounter quantifies "does not work". What's a 'pb'?
    – Vince
    May 3, 2019 at 10:51
  • Hello Vince, OK sorry. the error message says: ERROR: ERROR: "cadastre.geom" is not a known variable line : INTO cadastre.geom.
    – fcka
    May 3, 2019 at 11:58
  • Edit the question, please. Also describe your efforts at validating the query without the trigger overhead.
    – Vince
    May 3, 2019 at 12:11
  • 1
    hello, nobody can help me or put me on the track??
    – fcka
    May 4, 2019 at 13:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.