Обсуждение: SETOF modifier
Hi all
I have been trying to create a basic SQL function which returns a SETOF
values, without much luck. The docs make plenty of mention of the fact you
can return multiple values from a function, but unfortunately don't give
any examples as such. The syntax I thought would work is along the lines of
CREATE FUNCTION sp_testing() RETURNS setof text AS '
SELECT col1, col2, col3 FROM table;
' LANGUAGE 'sql';
I'd much appreciate anyone's help who has encountered this one before or
who knows the syntax!
regards
Jason Davis
DB Administrator/Programmer
www.tassie.net.au
Jason Davis <jdavis@tassie.net.au> writes:
> I have been trying to create a basic SQL function which returns a SETOF
> values, without much luck. The docs make plenty of mention of the fact you
> can return multiple values from a function, but unfortunately don't give
> any examples as such. The syntax I thought would work is along the lines of
> CREATE FUNCTION sp_testing() RETURNS setof text AS '
> SELECT col1, col2, col3 FROM table;
> ' LANGUAGE 'sql';
'setof' implies that the function can return multiple *rows*, not
multiple columns. The error message you're getting is not real helpful
in existing releases --- you see 'function declared to return text
returns multiple values in final retrieve', right? (The fact that it
says RETRIEVE not SELECT betrays the age of this code...) For 7.1 I've
reworded it as 'function declared to return text returns multiple
columns in final SELECT', which may be less confusing.
If you want to merge the results of three columns across all rows in
"table" into one undifferentiated result, a possible way is
CREATE FUNCTION sp_testing() RETURNS setof text AS '
SELECT col1 FROM table UNION ALL
SELECT col2 FROM table UNION ALL
SELECT col3 FROM table;
' LANGUAGE 'sql';
regards, tom lane
Tom, Ref : Monday, October 16, 2000 6:39:48 AM TL> Jason Davis <jdavis@tassie.net.au> writes: >> I have been trying to create a basic SQL function which returns a SETOF >> values, without much luck. The docs make plenty of mention of the fact you >> can return multiple values from a function, but unfortunately don't give >> any examples as such. The syntax I thought would work is along the lines of >> CREATE FUNCTION sp_testing() RETURNS setof text AS ' >> SELECT col1, col2, col3 FROM table; >> ' LANGUAGE 'sql'; TL> 'setof' implies that the function can return multiple *rows*, not TL> multiple columns. The error message you're getting is not real helpful TL> in existing releases --- you see 'function declared to return text TL> returns multiple values in final retrieve', right? (The fact that it TL> says RETRIEVE not SELECT betrays the age of this code...) For 7.1 I've TL> reworded it as 'function declared to return text returns multiple TL> columns in final SELECT', which may be less confusing. TL> If you want to merge the results of three columns across all rows in TL> "table" into one undifferentiated result, a possible way is TL> CREATE FUNCTION sp_testing() RETURNS setof text AS ' TL> SELECT col1 FROM table UNION ALL TL> SELECT col2 FROM table UNION ALL TL> SELECT col3 FROM table; TL> ' LANGUAGE 'sql'; And how do you get the effective results ? select sp_testing(); does not work. Where can I find documentation about : * returning multiple rows from a plpgsql function (if possible) ? * returning multiple values from a plpgsql function ? Thanks a LOT ! -- Jean-Christophe Boggio cat@thefreecat.org Independant Consultant and Developer Delphi, Linux, Oracle, Perl
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
I can't figure out how to do this. I want to do the following UPDATE newlink SET newfrom = substr(oldfrom,0,1) + '-' + substr(oldfrom,1) ... I want to do this: take the following variables CAFG CBTN CNPL CUSA and transform it to this: C-AFG C-BTN C-NPL C-USA Someone please help me! I've been through the user manual & I can't find out how to do it. Pgsql just tells me: Unable to identify an operator '+' for types 'text' and 'unknown' You will have to retype this query using an explicit cast _________________________________________________________________ Justin Long Network for Strategic Missions 1732 South Park Court Never retreat. Never surrender. Chesapeake, VA 23320, USA Never cut a deal with a dragon. 757-213-2055, ICQ 83384482 http://www.strategicnetwork.org Monday Morning Reality Check: reality-check-subscribe@egroups.com
On Wed, 18 Oct 2000, Justin Long wrote:
> I want to do the following
>
> UPDATE newlink SET newfrom = substr(oldfrom,0,1) + '-' + substr(oldfrom,1)
The concatenation operator is '||' (two pipe symbols).
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
My haircut is totally traditional!
The string concatenation operator in SQL is ||, not +.
Also, substr's start-index argument counts from 1 not 0. Otherwise
you've got the right idea...
regards, tom lane
This does not work either:
select 'a' + 'b';
ERROR: Unable to identify an operator '+' for types
'unknown' and 'unknown'
You will have to retype this query using an explicit
cast
But this does:
select 'a' || 'b';
?column?
----------
ab
(1 row)
Look at the Postgres manual section on String concatenation.
Bill
Justin Long wrote:
>
> I can't figure out how to do this.
>
> I want to do the following
>
> UPDATE newlink SET newfrom = substr(oldfrom,0,1) + '-' + substr(oldfrom,1)
>
> ... I want to do this:
>
> take the following variables
>
> CAFG
> CBTN
> CNPL
> CUSA
>
> and transform it to this:
>
> C-AFG
> C-BTN
> C-NPL
> C-USA
>
> Someone please help me! I've been through the user manual & I can't find out
> how to do it. Pgsql just tells me:
>
> Unable to identify an operator '+' for types 'text' and 'unknown'
> You will have to retype this query using an explicit cast
>
> _________________________________________________________________
> Justin Long Network for Strategic Missions
> 1732 South Park Court Never retreat. Never surrender.
> Chesapeake, VA 23320, USA Never cut a deal with a dragon.
> 757-213-2055, ICQ 83384482 http://www.strategicnetwork.org
> Monday Morning Reality Check: reality-check-subscribe@egroups.com
On Wed, 18 Oct 2000, Tom Lane wrote:
>> I can't figure out how to do this.
>>
>> I want to do the following
>>
>> UPDATE newlink SET newfrom = substr(oldfrom,0,1) + '-' + substr(oldfrom,1)
> The string concatenation operator in SQL is ||, not +.
>
> Also, substr's start-index argument counts from 1 not 0. Otherwise
> you've got the right idea...
>
> regards, tom lane
Tom,
The || operator needs parentheses to concat > 2 items in Pg 6.3.2.
Has this been fixed in subsequent versions?
If not, for Justin - this is what I do to concat more than
two items...
select (staff_fname || staff_lname) || ('-' || staff_ssn)
as "Social Security"
from personnel
where staff_id = 1000;
Cheers,
Tom Good
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------
Thomas Good <tomg@q8.nrnet.org> writes:
> The || operator needs parentheses to concat > 2 items in Pg 6.3.2.
> Has this been fixed in subsequent versions?
You're still on 6.3.2? Run, do not walk, to your nearest archive
site for an update ...
Yes, || is marked left-associative in more recent versions.
regression=# select 'a' || 'b' || 'c';
?column?
----------
abc
(1 row)
regards, tom lane
On Wed, 18 Oct 2000, Tom Lane wrote:
> Thomas Good <tomg@q8.nrnet.org> writes:
> > The || operator needs parentheses to concat > 2 items in Pg 6.3.2.
> > Has this been fixed in subsequent versions?
>
> You're still on 6.3.2? Run, do not walk, to your nearest archive
> site for an update ...
Roger that - I did and installed 7.0.2 this morning. As usual the
build went fine and to my amazement the versions of DBI and DBD
I had in place seem to work fine also! (It did take me awhile to
locate and install the pg man pages...but I got there.)
I do have a silly question tho: On RH 6.1 Linux the pg tarballs
that come from Lamar O. are 6.5.2. And they work fine - in fact
there is a command history function in psql that I like alot being
a bash user - even on FBSD and UnixWare.
Yet, I can't get command hx to work for 7.0.2 psql (on slackware 3.6).
Am I not seeing something obvious here?
TIA,
Tom Good
> Yes, || is marked left-associative in more recent versions.
>
> regression=# select 'a' || 'b' || 'c';
> ?column?
> ----------
> abc
> (1 row)
>
> regards, tom lane
--------------------------------------------------------------------
SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
IS Coordinator / DBA Phone: 718-354-5528
Fax: 718-354-5056
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |---------- linux The Power To Serve
--------------------------------------------------------------------
Thomas Good <tomg@q8.nrnet.org> writes:
> Yet, I can't get command hx to work for 7.0.2 psql (on slackware 3.6).
> Am I not seeing something obvious here?
If you compile it yourself, you need to be sure that libhistory's
include files are visible as well as its library .a or .so file.
Else configure will decide it can't build with history support.
(Some digging in the config.status file should tell you whether this
happened or not.) If you install libhistory from RPMs, make sure
you have its devel RPM as well as its runtime RPM.
regards, tom lane