Thread
-
SETOF modifier
Jason Davis <jdavis@tassie.net.au> — 2000-10-16T04:04:35Z
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 -
Re: SETOF modifier
Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-16T04:39:48Z
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 -
Re[2]: SETOF modifier
Jean-Christophe Boggio <cat@thefreecat.org> — 2000-10-16T15:33:41Z
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
-
Re: Re[2]: SETOF modifier
Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-16T15:44:30Z
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 -
Stupid question: concatenating strings
Justin Long <justinlong@strategicnetwork.org> — 2000-10-18T16:07:51Z
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
-
Re: Stupid question: concatenating strings
Brett W. McCoy <bmccoy@chapelperilous.net> — 2000-10-18T17:17:48Z
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! -
Re: Stupid question: concatenating strings
Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-18T17:21:48Z
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
-
Re: Stupid question: concatenating strings
Bill Morrow <wmorrow@home.com> — 2000-10-18T17:32:07Z
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 -
Re: Stupid question: concatenating strings
Thomas Good <tomg@q8.nrnet.org> — 2000-10-18T17:46:08Z
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 -------------------------------------------------------------------- -
Re: Stupid question: concatenating strings
Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-19T01:18:49Z
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
-
Re: Stupid question: concatenating strings
Thomas Good <tomg@q8.nrnet.org> — 2000-10-19T16:09:53Z
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 -------------------------------------------------------------------- -
Re: Stupid question: concatenating strings
Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-19T17:22:48Z
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