2

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

  • @dezso - Thx that did the trick :-) – omanthey Dec 14 '16 at 6:31
  • 1
    @ErwinBrandstetter now it is an answer, thanks – dezso Dec 14 '16 at 8:32
3

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.

|improve this answer|||||
  • Thx again now it's running smooth and fine :-) – omanthey Dec 14 '16 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.