Re: Re[2]: SETOF modifier

Jean-Christophe Boggio <cat(at)thefreecat(dot)org> writes:
> And how do you get the effective results ?
> select sp_testing();
> does not work.

What version are you running? It works for me in 7.0.2 and in current
development sources:

play=> create table foo (col1 text, col2 text);
CREATE
play=> insert into foo values ('col1 row1', 'col2 row1');
INSERT 334858 1
play=> insert into foo values ('col1 row2', 'col2 row2');
INSERT 334859 1
play=> CREATE FUNCTION sp_testing() RETURNS setof text AS '
play'> SELECT col1 FROM foo UNION ALL
play'> SELECT col2 FROM foo
play'> ' LANGUAGE 'sql';
CREATE
play=> select sp_testing();
?column?
-----------
col1 row1
col1 row2
col2 row1
col2 row2
(4 rows)

In 7.0.* and earlier there are strict restrictions on what you can *do*
with the result; it pretty much is only useful as a standalone SELECT
item. For example,

play=> select sp_testing() || ' more';
ERROR: An operand to the '||' operator returns a set of text,
but '||' takes single values, not sets.

But in 7.1 this will do something reasonable:

regression=# select sp_testing() || ' more';
?column?
----------------
col1 row1 more
col1 row2 more
col2 row1 more
col2 row2 more
(4 rows)

regards, tom lane