Thread: call syntax for record returning stored function

I'm having a little trouble figuring out the call syntax for calling a
pl/pgsql stored function that returns a record with
Connection.prepareCall().  I'm not getting the column definition list
correct.  A pointer to an example would be great, or an example for
something like the following:

    CREATE OR REPLACE FUNCTION home (INTEGER, INTEGER)
        RETURNS RECORD AS '
    DECLARE
        c_fname CHAR(15);
        pp_i_id1 INTEGER;
        rec RECORD;
    BEGIN
        ...
        SELECT c_fname::CHAR(15), pp_i_id1::INTEGER
        INTO rec;
        RETURN rec;
    END;
    ' LANGUAGE 'plpgsql';

Thanks!
--
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503) 626-2455 x 32 (office)
(503) 626-2436      (fax)
http://developer.osdl.org/markw/

markw@osdl.org writes:
> I'm having a little trouble figuring out the call syntax for calling a
> pl/pgsql stored function that returns a record with
> Connection.prepareCall().  I'm not getting the column definition list
> correct.  A pointer to an example would be great, or an example for
> something like the following:

>     CREATE OR REPLACE FUNCTION home (INTEGER, INTEGER)
>         RETURNS RECORD AS '
>     DECLARE
>         c_fname CHAR(15);
>         pp_i_id1 INTEGER;
>         rec RECORD;
>     BEGIN
>         ...
>         SELECT c_fname::CHAR(15), pp_i_id1::INTEGER
>         INTO rec;
>         RETURN rec;
>     END;
>     ' LANGUAGE 'plpgsql';

You could call that function like this:

regression=# select home.* from home(3,4) as (f1 char(15), f2 int);
 f1 | f2
----+----
    |
(1 row)

regression=# select h.* from home(3,4) as h (f1 char(15), f2 int);
 f1 | f2
----+----
    |
(1 row)

Note that the AS clause must provide column names as well as types
for the function output.  I think the word "AS" is optional in the
second case but not the first.

            regards, tom lane

On 22 Mar, Tom Lane wrote:
> markw@osdl.org writes:
>> I'm having a little trouble figuring out the call syntax for calling a
>> pl/pgsql stored function that returns a record with
>> Connection.prepareCall().  I'm not getting the column definition list
>> correct.  A pointer to an example would be great, or an example for
>> something like the following:
>
>>     CREATE OR REPLACE FUNCTION home (INTEGER, INTEGER)
>>         RETURNS RECORD AS '
>>     DECLARE
>>         c_fname CHAR(15);
>>         pp_i_id1 INTEGER;
>>         rec RECORD;
>>     BEGIN
>>         ...
>>         SELECT c_fname::CHAR(15), pp_i_id1::INTEGER
>>         INTO rec;
>>         RETURN rec;
>>     END;
>>     ' LANGUAGE 'plpgsql';
>
> You could call that function like this:
>
> regression=# select home.* from home(3,4) as (f1 char(15), f2 int);
>  f1 | f2
> ----+----
>     |
> (1 row)
>
> regression=# select h.* from home(3,4) as h (f1 char(15), f2 int);
>  f1 | f2
> ----+----
>     |
> (1 row)
>
> Note that the AS clause must provide column names as well as types
> for the function output.  I think the word "AS" is optional in the
> second case but not the first.

Perfect, thanks!

Mark