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

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.