Thread: dblink question about duplicate connection

Dear all,
i have a question about dblink connection.I use it in a trigger function :


CREATE OR REPLACE FUNCTION setuser()
  RETURNS trigger AS
$BODY$

DECLARE

BEGIN
  IF (TG_OP = 'INSERT') THEN



     PERFORM dblink_connect('conn_test', 'dbname=database_test');



     PERFORM dblink_exec('conn_test','insert into users
      ......


Somentimes happend that an error accours in insert data and when i try to run
new insert posgresql return and error like duplicate connection.
I would like to change my functions in this way:

CREATE OR REPLACE FUNCTION setuser()
  RETURNS trigger AS
$BODY$

DECLARE

BEGIN
  IF (TG_OP = 'INSERT') THEN

     If 'conn_test' exist  PERFORM dblink_disconnect('conn_test');
     else

     PERFORM dblink_connect('conn_test', 'dbname=database_test');



     PERFORM dblink_exec('conn_test','insert into users



How can write my new function?

Thanks


Francesco Boccacci

On 08/02/2011 05:36 AM, francescoboccacci@libero.it wrote:
> Somentimes happend that an error accours in insert data and when i try to run
> new insert posgresql return and error like duplicate connection.
> I would like to change my functions in this way:

>      If 'conn_test' exist  PERFORM dblink_disconnect('conn_test');
>      else
>
>      PERFORM dblink_connect('conn_test', 'dbname=database_test');

> How can write my new function?

See dblink_get_connections():
http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-get-connections.html

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support