Re: call syntax for record returning stored function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: markw(at)osdl(dot)org
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: call syntax for record returning stored function
Date: 2004-03-22 20:52:33
Message-ID: 8023.1079988753@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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.

regards, tom lane

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message markw 2004-03-22 21:54:53 Re: call syntax for record returning stored function
Previous Message Dave Cramer 2004-03-22 20:36:50 Re: Postmaster not dropping connections on tomcat restart.