I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function.
The following statement I can execute from commandline:
SELECT fieldA, fieldB, fielbC INTO temp_table FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw', E'SELECT fieldA, fieldB, fieldC FROM data_table') AS temp_table(fieldA integer, fieldB integer, fieldC integer);
But when trying to embed this into a pgsql procedure it won't work.
I'm trying this by using the execute command:
CREATE OR REPLACE FUNCTION archive() RETURNS trigger AS $$ DECLARE BEGIN execute ' SELECT fieldA, fieldB, fielbC INTO temp_table FROM dblink(''dbname=dbname port=5432 host=a.b.c.d user=user password=pw'', E''SELECT fieldA, fieldB, fieldC FROM data_table'') AS temp_table(fieldA integer, fieldB integer, fieldC integer); '; return new; END; $$ LANGUAGE plpgsql;
I get an error telling me that
EXECUTE of SELECT ... INTO is not implemented and I could use
EXECUTE ... INTO or
EXECUTE CREATE TABLE ... AS
Does anybody has some experience with this? I'm struggling for hours now :-(