Forums PostgreSQL.fr

CREATE OR REPLACE FUNCTION za()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN

INSERT INTO za SELECT row_number() OVER (ORDER BY france_littoral.id) AS gid,
new.table_liste_emprise.nom_donnee,
france_littoral.id AS zone,
france_littoral.the_geom_wgs84
FROM france.france_littoral,
france.table_liste_emprise
WHERE st_intersects(france_littoral.the_geom_wgs84, table_liste_emprise.the_geom_wgs84);
RETURN NEW;
return null;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION za()
  OWNER TO postgres;
------------------------------------------------------------------------
CREATE TRIGGER zone_geo
INSTEAD OF INSERT ON zone_geographique
FOR EACH ROW
EXECUTE PROCEDURE za();

CREATE OR REPLACE FUNCTION france.maj_2()
  RETURNS void AS
$BODY$
DECLARE
    liste RECORD;
    geom RECORD;
BEGIN
    FOR liste IN SELECT tablename FROM pg_tables where tablename 
		 IN 
		(select tablename from pg_tables where schemaname ='france' and tablename in 
		(SELECT f_table_name FROM geometry_columns 
		where f_table_name not like 'france_littoral'))
    loop
      FOR geom IN SELECT type FROM geometry_columns where f_table_schema='france' AND f_table_name = liste.tablename
      loop
    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'france' AND TABLE_NAME = liste.tablename AND COLUMN_NAME = 'the_geom_wgs84')
THEN 
    execute'select addgeometrycolumn ('''','''||quote_ident(liste.tablename)||''',''the_geom_wgs84'',''4326'','''||quote_ident(geom.type)||''',2);
           update '||quote_ident(liste.tablename)||' set the_geom_wgs84= st_transform(the_geom_orig,4326);';
    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'france' AND TABLE_NAME = liste.tablename AND COLUMN_NAME = 'nom_donnee')
THEN 
    execute'ALTER TABLE '||quote_ident(liste.tablename)||' ADD COLUMN nom_donnee varchar(80);
	   update '||quote_ident(liste.tablename)||' set nom_donnee='''||quote_ident(liste.tablename)||''';';
    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'france' AND TABLE_NAME = liste.tablename AND COLUMN_NAME = 'id')
THEN
    execute'ALTER TABLE '||quote_ident(liste.tablename)||' ADD COLUMN id varchar(80);
           ALTER TABLE '||quote_ident(liste.tablename)||' drop constraint '||quote_ident(liste.tablename)||'_pkey;
           UPDATE '||quote_ident(liste.tablename)||' set id = nom_donnee||''_''||nextval(''france.id_seq''::regclass);
           ALTER TABLE '||quote_ident(liste.tablename)||' add constraint '||quote_ident(liste.tablename)||'_pkey primary key(id);';
    end if;
    end if;
    end if;
    end loop;
    end loop; 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION france.emprise_table()
  OWNER TO postgres;
 

ERREUR:  la colonne « POINT » n'existe pas
LINE 1: ...addgeometrycolumn ('','b','the_geom_wgs84','4326',"POINT",2)...
                                                             ^
QUERY:  select addgeometrycolumn ('','b','the_geom_wgs84','4326',"POINT",2);
           update b set the_geom_wgs84= st_transform(the_geom_orig,4326);
CONTEXT:  fonction PL/pgsql maj_2(), ligne 16 à instruction EXECUTE
********** Erreur **********

ERREUR: la colonne « POINT » n'existe pas
État SQL :42703
Contexte : fonction PL/pgsql maj_2(), ligne 16 à instruction EXECUTE