Обсуждение: PL/pgSQL syntax/usage question

Hi *,

I'm starting with PL/pgSQL but can't exactly find out how to do
something like this:

CREATE FUNCTION textkey (text, int4) RETURNS text AS '
        DECLARE
                tabel ALIAS FOR $1;
                id ALIAS FOR $2;
                result record;
        BEGIN
                select lname into result from tabel
                where person_id=id;
                return result;
        END;
    ' LANGUAGE 'plpgsql';

csbase=> select textkey ('person',111) as result;
ERROR:  parser: parse error at or near "$1"

postmaster.log:

StartTransactionCommand
query: select textkey ('person',111) as result;
ProcessQuery
query: SELECT lname from $1 where person_id=$2
ERROR:  parser: parse error at or near "$1"
DEBUG:  Last error occured while executing PL/pgSQL function textkey
DEBUG:  line 6 at select into variables
AbortCurrentTransaction


How does one do things like this? Why aren't the $n variables expanded
in the logfile?

Maybe it is documented but I can't find it in chapter 11 of the Programmer's
Guide. Are there other places where PL/pgSQL is explained?

By the way: I'm using 6.5.2 on RedHat 6.1

Regards,
+-----------------------------------------------------------------------+
| Henk van Lingen, Systems Administrator,             <henkvl@cs.uu.nl> |
| Dept. of Computer Science, Utrecht University.  phone: +31-30-2535278 |
+----------------- http://www.cs.uu.nl/people/henkvl/ ------------------+

A couple of issues here...

First, your actual return type is 'record', but you declared it as
'text'.  If it were possible, you'd probably want something like this:

    select into result lname from tabel where ...
    return result.lname;

Second, IIRC, you cannot pass in a string representing the tablename
and then use it as a tablename in the select (though it would be quite
handy).  Not sure how to do it, though.

Cheers,
Ed Loehr


Henk van Lingen wrote:
>
> Hi *,
>
> I'm starting with PL/pgSQL but can't exactly find out how to do
> something like this:
>
> CREATE FUNCTION textkey (text, int4) RETURNS text AS '
>         DECLARE
>                 tabel ALIAS FOR $1;
>                 id ALIAS FOR $2;
>                 result record;
>         BEGIN
>                 select lname into result from tabel
>                 where person_id=id;
>                 return result;
>         END;
>     ' LANGUAGE 'plpgsql';
>
> csbase=> select textkey ('person',111) as result;
> ERROR:  parser: parse error at or near "$1"
>
> postmaster.log:
>
> StartTransactionCommand
> query: select textkey ('person',111) as result;
> ProcessQuery
> query: SELECT lname from $1 where person_id=$2
> ERROR:  parser: parse error at or near "$1"
> DEBUG:  Last error occured while executing PL/pgSQL function textkey
> DEBUG:  line 6 at select into variables
> AbortCurrentTransaction
>
> How does one do things like this? Why aren't the $n variables expanded
> in the logfile?
>
> Maybe it is documented but I can't find it in chapter 11 of the Programmer's
> Guide. Are there other places where PL/pgSQL is explained?

>
> Second, IIRC, you cannot pass in a string representing the tablename
> and then use it as a tablename in the select (though it would be quite
> handy).  Not sure how to do it, though.

I went through this a while ago: no way to pass a table name in. Only possibility
is to write a C routine using SPI and then anything is possible.

Adriaan

Adriaan Joubert wrote:
>
> >
> > Second, IIRC, you cannot pass in a string representing the tablename
> > and then use it as a tablename in the select (though it would be quite
> > handy).  Not sure how to do it, though.
>
> I went through this a while ago: no way to pass a table name in. Only possibility
> is to write a C routine using SPI and then anything is possible.

It occurs to me that you might be able to weave something together by
looking up the tablename in the pg_class or pg_tables system tables
and then building some sort of select query using the relation ID to
get into the table.  Haven't tried it, don't know if it could work,
but there's an idea for the truly desperate.

Cheers,
Ed Loehr