From: Richard Huxton <dev@archonet.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-novice@postgresql.org
Subject: Re: dblink inside plpgsql function
Date: Tue, 03 Jul 2007 13:58:26 +0100
Message-ID: <468A47F2.2030000@archonet.com> (raw)
In-Reply-To: <1c23c8e70707030550j69f1911dj6cf33ab0a7db4776@mail.gmail.com>
References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
<162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com>
<468A36F0.A3DD.0030.0@indicator.be>
<1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com>
<468A41CD.2070208@archonet.com>
<1c23c8e70707030550j69f1911dj6cf33ab0a7db4776@mail.gmail.com>
Loredana Curugiu wrote:
> CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
> reminder_services AS'
>
> DECLARE r reminder_services%ROWTYPE;
>
> BEGIN
> PERFORM dblink_connect(''dbname=''||$1);
> PERFORM dblink_disconnect($1);
> RETURN;
> END;
> ' LANGUAGE plpgsql;
>
> Now I get the errors:
>
> connection "eu" not available
> CONTEXT: SQL statement "SELECT dblink_disconnect( $1 )"
> PL/pgSQL function "getreminderservices" line 21 at perform
Well, it's complaining that a connection called "eu" isn't available in
the dblink_disconnect() call. I don't use dblink much myself, so I
approached this problem by looking in the documentation.
According to the docs, there are two ways to call dblink_disconnect()
"
Synopsis
dblink_disconnect()
dblink_disconnect(text connname)
Inputs
connname
if an argument is given, it is used as a name for a persistent
connection to close; otherwiase the unnamed connection is closed
"
So - did we open a connection called "eu"? Looking at the docs again, it
appears no! If we use the one-argument version of dblink_connect() we
get an unnamed connection.
So - either change the dblink_disconnect so there is no argument:
dblink_disconnect()
Or change the connection
dblink_connect($1, ''dbname='' || $1)
I'd do the first one, since you don't care what the connection is called
and are closing it at the end of the function.
HTH
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: dblink inside plpgsql function
In-Reply-To: <468A47F2.2030000@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox