Thread: dblink inside plpgsql function

Hello

Every SELECT statement in PL/pgSQL have to be forward to variables. In
plpgsql you can you use select only like

select into variables columns from ...

propably better version is (i haven't installed dblink and can't to test it)

CREATE OR REPLACE FUNCTION GetReminderServices( varchar ) RETURNS
SETOF reminder_services AS'
DECLARE r record;
BEGIN   PERFORM dblink_connect(''dbname=''||$1);   FOR r IN SELECT * FROM dblink(''SELECT * FROM reminder_services'')
          AS reminder_services( uid INT,                                    theme_uid INT,
     activity_MT_amount INT,                                    activity_min_days INT,
 activity_max_months INT,                                    inactivity_days INT,
limit_remindersINT,                                    limit_months INT,
scanning_timeTIMESTAMP WITH TIME ZONE,                                    reminder_time TIMESTAMP WITH TIME ZONE,
                            message TEXT)   LOOP     RETURN NEXT r;   END LOOP;   PERFORM dblink_disconnect($1);
RETURN
END;
' LANGUAGE plpgsql;

regards
Pavel

2007/7/3, Loredana Curugiu <loredana.curugiu@gmail.com>:
> Hi everybody,
>
>  I created the following function:
>
>  CREATE OR REPLACE FUNCTION GetReminderServices( varchar ) RETURNS SETOF
> reminder_services AS'
>  BEGIN
>      SELECT dblink_connect(''dbname=''||$1);
>      SELECT * FROM dblink(''SELECT * FROM reminder_services'')
>                 AS reminder_services( uid INT,
>                                       theme_uid INT,
>                                       activity_MT_amount
> INT,
>                                       activity_min_days
> INT,
>                                       activity_max_months
> INT,
>                                       inactivity_days INT,
>                                       limit_reminders INT,
>                                       limit_months INT,
>                                       scanning_time
> TIMESTAMP WITH TIME ZONE,
>                                       reminder_time
> TIMESTAMP WITH TIME ZONE,
>                                       message TEXT);
>      SELECT dblink_disconnect($1);
>      RETURN;
>  END;
>  ' LANGUAGE plpgsql;
>
>  When I call this function as SELECT * FROM GetReminderServices('eu');
>  I get the following errors:
>  ERROR:  query has no destination for result data
>  HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
>  CONTEXT:  PL/pgSQL function "getreminderservices" line 2 at SQL statement
>
>  Does somebody know which is the problem?
>
>
>  Best,
>       Loredana
>
>
>
>

I created the following function<br /><br />CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
reminder_servicesAS'<br /> DECLARE r reminder_services%ROWTYPE;<br /> BEGIN<br />     SELECT
dblink_connect(''dbname=''||$1);<br/>     FOR r IN SELECT * FROM dblink(''SELECT * FROM reminder_services'')<br />
              AS columns( uid INT,<br />                                      theme_uid INT,<br />
                                    activity_MT_amount INT,<br />                                     
activity_min_daysINT,<br />                                      activity_max_months INT,<br />
                                    inactivity_days INT,<br />                                      limit_reminders
INT,<br/>                                      limit_months INT,<br />                                     
scanning_timeTIMESTAMP WITH TIME ZONE,<br />                                      reminder_time TIMESTAMP WITH TIME
ZONE,<br/>                                      message TEXT)<br />     LOOP<br />     RETURN NEXT r;<br />     END
LOOP;<br/>     SELECT dblink_disconnect($1);<br />     RETURN; <br /> END;<br /> ' LANGUAGE plpgsql; <br /><br /><br />
andI get the same errors. I think it is a problem with the dblink because<br /> the following function it works fine if
Icall SELECT * FROM getReminders().<br /><br /> CREATE OR REPLACE FUNCTION getReminders() RETURNS SETOF
reminder_servicesAS'<br /><br /> DECLARE r reminder_services%ROWTYPE;<br /><br /> BEGIN<br />     FOR r IN SELECT *
FROMreminder_services<br />     LOOP<br />     RETURN NEXT r;<br />     END LOOP;<br />     RETURN; <br /> END;<br /> '
LANGUAGEplpgsql; <br /><br /><br /><br /><br /><br /> 

Loredana Curugiu wrote:
> I created the following function
> 
> CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
> reminder_services AS'
> DECLARE r reminder_services%ROWTYPE;
> BEGIN
>    SELECT dblink_connect(''dbname=''||$1);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

> and I get the same errors. I think it is a problem with the dblink because
> the following function it works fine if I call SELECT * FROM 
> getReminders().

You still haven't fixed the line above. The same rules apply to all SELECTs

--   Richard Huxton  Archonet Ltd

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
unnamedconnection 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