1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| CREATE OR REPLACE FUNCTION copy_microparcelle_row(INTEGER,TEXT) RETURNS INTEGER AS $$
DECLARE
var1 ALIAS FOR $1;
var2 ALIAS FOR $2;
v_query_part text;
col_name varchar;
newid int4;
BEGIN
--Recherche de la colonne clé primaire
SELECT INTO col_name at.attname
FROM
pg_constraint co
JOIN pg_class cl
ON cl.oid = co.conrelid AND co.contype = 'p'
JOIN pg_namespace ns
ON ns.oid = co.connamespace
JOIN pg_attribute at
ON at.attrelid = co.conrelid AND at.attnum = ANY( co.conkey )
WHERE cl.relname='microparcelle';
--Récupération de toutes les colonnes sauf la clé primaire
SELECT array_to_string((select array_agg(quote_ident( column_name )) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = 'microparcelle' AND COLUMN_NAME <> col_name AND COLUMN_NAME <> 'geom'),',') INTO v_query_part;
RAISE LOG 'INSERT INTO microparcelle (%,geom) SELECT %,ST_GeomFromWKB(decode(''%'',''hex''),27572) FROM microparcelle WHERE microparcelle_id=% RETURNING microparcelle_id',v_query_part,v_query_part,var2,var1;
EXECUTE 'INSERT INTO microparcelle (' || v_query_part ||',geom) SELECT '|| v_query_part || ',ST_GeomFromWKB(decode(''' || var2 ||''',''hex''),27572) FROM microparcelle WHERE microparcelle_id=' || var1 || ' RETURNING microparcelle_id' INTO newid;
RETURN newid;
END;
$$ LANGUAGE plpgsql; |
Partager