For those who want to play on the bleeding edge of CVS, can someone provide the syntax for the recently-checked-in set-returning functions? I've got it figured out when I'm returning a many rows of single column, but not for many rows of several columns.

If someone can do this, and no one has put together docs on this feature, I'll volunteer to write this up.

Thanks!

- J.

-- 

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant 



Sat, 18 May 2002 20:51:31 -0000
"Joel Burton" <joel@joelburton.com>
Set-returning function syntax
Joel Burton wrote:
 > For those who want to play on the bleeding edge of CVS, can someone
 > provide the syntax for the recently-checked-in set-returning
 > functions? I've got it figured out when I'm returning a many rows of
 > single column, but not for many rows of several columns.

For multiple columns, you need a composite data type defined -- 
basically you need to create a table, even if it is an unused shell, 
which has the column names and data types of the returned tuple. See 
below for more.

 >
 > If someone can do this, and no one has put together docs on this
 > feature, I'll volunteer to write this up.

I hadn't gotten to the docs yet, but if you wanted to write something up 
that would be great! :) I'll certainly help too.

Attached is the script I've been using to test as I go. It shows the 
usage of SRFs in a variety of situations (note that the C function tests 
require contrib/dblink installed). There's also a description in one of 
my earlier posts. Here is a recap, edited to the latest reality:

How it currently works:
-----------------------
1. The SRF may be either marked as returning a set or not. A function 
not marked as returning a set simply produces one row.

2. The SRF may either return a base data type (e.g. TEXT) or a composite 
data type (e.g. pg_class). If the function returns a base data type, the 
single result column is named for the function. If the function returns 
a composite type, the result columns get the same names as the 
individual attributes of the type.

3. The SRF may be aliased in the FROM clause, but it also be left 
unaliased. If a function is used in the FROM clause with no alias, the 
function name is used as the relation name.

Hope that's a start.

Thanks,

Joe
Sat, 18 May 2002 15:16:03 -0700
Joe Conway <mail@joeconway.com>
Re: Set-returning function syntax
Does your SRF function allow to return a setof composite data type
using C function? If so, how can I write such that C function? I
couldn't find any example or explanation so far. You referred dblink,
but in my understanding it does not have any function that returns a
setof composite data type.
--
Tatsuo Ishii

> Attached is the script I've been using to test as I go. It shows the 
> usage of SRFs in a variety of situations (note that the C function tests 
> require contrib/dblink installed). There's also a description in one of 
> my earlier posts. Here is a recap, edited to the latest reality:
> 
> How it currently works:
> -----------------------
> 1. The SRF may be either marked as returning a set or not. A function 
> not marked as returning a set simply produces one row.
> 
> 2. The SRF may either return a base data type (e.g. TEXT) or a composite 
> data type (e.g. pg_class). If the function returns a base data type, the 
> single result column is named for the function. If the function returns 
> a composite type, the result columns get the same names as the 
> individual attributes of the type.
> 
> 3. The SRF may be aliased in the FROM clause, but it also be left 
> unaliased. If a function is used in the FROM clause with no alias, the 
> function name is used as the relation name.
> 
> Hope that's a start.
> 
> Thanks,
> 
> Joe

Sun, 19 May 2002 09:03:11 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Set-returning function syntax
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Does your SRF function allow to return a setof composite data type
> using C function? If so, how can I write such that C function?

The "setof" part is documented in src/backend/utils/fmgr/README.
There's no good documentation for returning tuples at the moment,
but basically you return a pointer to a TupleTableSlot.  (Re-use
the same slot on each call to avoid memory leakage.)  There's an
example in src/backend/executor/functions.c --- look at the uses
of funcSlot.

One reason this isn't documented is that it's really ugly.  It might
be a good idea to change it before we start having lots of user-written
code that depends on it ...

			regards, tom lane

Sat, 18 May 2002 20:17:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Set-returning function syntax 
Tatsuo Ishii wrote:
> Does your SRF function allow to return a setof composite data type
> using C function? If so, how can I write such that C function? I
> couldn't find any example or explanation so far. You referred dblink,
> but in my understanding it does not have any function that returns a
> setof composite data type.
>

I haven't written a C function yet that returns a composite type. You 
are correct that dblink does not have an example which returns composite 
type, because that wasn't even possible when I wrote the dblink code ;-)

At least initially, a C function returning a composite type will have to 
do alot of dirty work -- i.e. something like:
- manually form a tuple based on the return type relation attributes
- save the tuple in a tuple table slot
- return a pointer to the slot as a datum

I don't know what other complications may be lurking, but I will try to 
get a working example sometime this coming week and post it to HACKERS.

Joe




Sat, 18 May 2002 17:21:13 -0700
Joe Conway <mail@joeconway.com>
Re: Set-returning function syntax
Tatsuo Ishii wrote:
> Does your SRF function allow to return a setof composite data type
> using C function? If so, how can I write such that C function? I

Just to follow-up, here's a quick look at what works and what doesn't, 
at least using my test script.

SELECT * FROM myfunc();
Language 
RetSet 
RetType 
Status
--------------- ------- ------- ---------------------
C 
	t	b	OK
C 
	t	c	Not tested
C 
	f	b	OK
C 
	f	c	Not tested
SQL 
	t	b	OK
SQL 
	t	c	OK
SQL 
	f	b	OK
SQL 
	f	c	OK
PL/pgSQL 
t 
b 
No retset support
PL/pgSQL 
t 
c 
No retset support
PL/pgSQL 
f 
b 
OK
PL/pgSQL 
f 
c 
OK
-----------------------------------------------------
RetSet: t = function declared to return setof something
RetType: b = base type; c = composite type

Same cases work when a view is defined as SELECT * FROM myfunc().

Joe


Sat, 18 May 2002 17:56:14 -0700
Joe Conway <mail@joeconway.com>
Re: Set-returning function syntax
> The "setof" part is documented in src/backend/utils/fmgr/README.
> There's no good documentation for returning tuples at the moment,
> but basically you return a pointer to a TupleTableSlot.  (Re-use
> the same slot on each call to avoid memory leakage.)  There's an
> example in src/backend/executor/functions.c --- look at the uses
> of funcSlot.

That was almost same as I guessed:-)

> One reason this isn't documented is that it's really ugly.  It might
> be a good idea to change it before we start having lots of user-written
> code that depends on it ...

Sounds like a good idea.
--
Tatsuo Ishii

Sun, 19 May 2002 11:33:22 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Set-returning function syntax 