[SQL] FW: trigger problem

[Jan Verheyden]

Hi,

I keep looking for myself and tried as well the following code:

'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
                create view remote as
                                select *
                                                from dblink('myconnect','select 
uid from test')
                                                as t1(pat_id text);
perform * from remote where pat_id like '|| query_literal(NEW.pat_id) ||';
return new;
perform dblink_disconnect('myconnect');'

And again I get the same error message as below..

Is this because it tries to make connection for each row in the column??

Thanks,

Jan

From: Jan Verheyden
Sent: Thursday, August 06, 2009 11:09 AM
To: 'pgsql-sql@postgresql.org'
Subject: trigger problem

Hi,

If I try this to run in a trigger function

'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'update test set uploaded = 1 where uid =' || 
quote_literal(NEW.pat_id) || ' ');
return new;
perform dblink_disconnect('myconnect');'



I get the message

'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement "SELECT  dblink_connect('myconnect','dbname=postgres 
password=uzleuven')"
PL/pgSQL function "test_update_trigger" line 2 at perform'

This happens only in one of my two databases, anyone an idea?


Regards,
Jan