Set-returning function syntax

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

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

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

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)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

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

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

> 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