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

  • 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 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 at 11:58
  • Edit the question, please. Also describe your efforts at validating the query without the trigger overhead. – Vince May 3 at 12:11
  • 1
    hello, nobody can help me or put me on the track?? – fcka May 4 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.