Call dblink from a trigger function

Question

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 :-(

• omanthey: @dezso - Thx that did the trick :-)

Answer 1

As you have nothing dynamically built in your query, you don't have to use EXECUTE at all.

Inside the function body, you can simply do an INSERT if temp_table already exists:

INSERT INTO temp_table (fielda, fieldb, fielbc)
SELECT a, b, c 
 FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw', 
 E'SELECT fielda, fieldb, fieldc FROM data_table')
 AS t(a, b, c);

If the table is not there yet, you can replace the INSERT line with

CREATE TEMPORARY TABLE AS
...

Note that in your present approach (SELECT ... INTO temp_table) the table that is being created is not a temporary table, and on the next run the trigger will run into an error saying the table already exists.

• omanthey: Thx again now it's running smooth and fine :-)