Re: dblink inside plpgsql function - Mailing list pgsql-sql

What Pavel mentions might indeed be an issue, but I think there's another one too.

I think you have to call your function giving more information to the statement

on what types will be returned.

 

Since you use dblink I assume that the table or view reminder_services you

are basically selecting from is in another database than the current one.

That would mean that the type reminder_services is not known in the current

database. In that case you should call your function just like your function calls

the dblink function: by adding an AS clause to your select. So NOT

SELECT * FROM GetReminderServices('eu');

but

SELECT * FROM GetReminderServices('eu') AS (

      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);

 

I think as an alternative you could define a type

CREATE TYPE reminder_services AS (

      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);

and then call your function like you did:

SELECT * FROM GetReminderServices('eu');

 

This second way would work with plperl but I haven't tested it with

plpgsql. Since I don't have dblink installed I haven't tested the first option

either. Good luck!

>>> "Pavel Stehule" <pavel.stehule@gmail.com> 2007-07-03 11:13 >>>
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_reminders INT,
                                     limit_months INT,
                                     scanning_time TIMESTAMP 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
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster