From: | markw(at)osdl(dot)org |
---|---|
To: | tgl(at)sss(dot)pgh(dot)pa(dot)us |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: call syntax for record returning stored function |
Date: | 2004-03-22 21:54:53 |
Message-ID: | 200403222155.i2MLt6E25267@mail.osdl.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
On 22 Mar, Tom Lane wrote:
> markw(at)osdl(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Thomas | 2004-03-22 22:31:38 | Re: Postmaster not dropping connections on tomcat restart. |
Previous Message | Tom Lane | 2004-03-22 20:52:33 | Re: call syntax for record returning stored function |