Re: 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