Jean-Christophe Boggio <cat@thefreecat.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