Skip site navigation (1) Skip section navigation (2)

Re: Re[2]: SETOF modifier

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jean-Christophe Boggio <cat(at)thefreecat(dot)org>
Cc: Jason Davis <jdavis(at)tassie(dot)net(dot)au>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Re[2]: SETOF modifier
Date: 2000-10-16 15:44:30
Message-ID: 26077.971711070@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: EduardoDate: 2000-10-16 16:50:05
Subject: Last serial inserted
Previous:From: Jean-Christophe BoggioDate: 2000-10-16 15:33:41
Subject: Re[2]: SETOF modifier

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group