Skip site navigation (1) Skip section navigation (2)

Re: call syntax for record returning stored function

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 (view raw or flat)
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

In response to

pgsql-jdbc by date

Next:From: Paul ThomasDate: 2004-03-22 22:31:38
Subject: Re: Postmaster not dropping connections on tomcat restart.
Previous:From: Tom LaneDate: 2004-03-22 20:52:33
Subject: Re: call syntax for record returning stored function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group