Function returning SETOF
This is a discussion on Function returning SETOF within the Pgsql General forums, part of the PostgreSQL category; --> List, I have a simple function: CREATE OR REPLACE FUNCTION parse_string (TEXT, TEXT) RETURNS SETOF TEXT AS ' DECLARE ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-09-2008, 07:14 AM
| ||||
| ||||
 Function returning SETOF List, I have a simple function: CREATE OR REPLACE FUNCTION parse_string (TEXT, TEXT) RETURNS SETOF TEXT AS ' DECLARE str ALIAS FOR $1; -- the string to parse delimiter ALIAS FOR $2; -- the delimiter field TEXT; -- return value from split_part idx INTEGER DEFAULT 1; -- field counter funcName TEXT DEFAULT ''parse_string''; -- function name dbg BOOLEAN DEFAULT True; -- debug print flag BEGIN IF dbg THEN RAISE NOTICE ''% ()'', funcName; END IF; SELECT INTO field split_part (str, delimiter, idx); WHILE field != '''' LOOP RETURN NEXT field; idx = idx + 1; SELECT INTO field split_part (str, delimiter, idx); END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; As you can see, I'm using split_part to parse the string in a loop. I want this thing to return the set of values that make up the fields in the string. When I call the function from psql here is the error I'm getting: rnd=# select parse_string ('1/2/3/4/5', '/'); NOTICE: parse_string () ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "parse_string" line 14 at return next Then I tried this approach and got the same error: rnd=# select ARRAY(SELECT parse_string ('1/2/3/4/5', '/')); NOTICE: parse_string () ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "parse_string" line 14 at return next Version Information: rnd=# select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) I'm sure that I'm doing something stupid. Any input would be appreciated... ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster      |
|
04-09-2008, 07:14 AM
| |||
| |||
| Re: Function returning SETOF I knew I was doing something stupid. Right after I sent this I realized I was calling incorrectly. This works: rnd=# select * from parse_string ('1/2/3/4/5', '/'); NOTICE: parse_string () parse_string -------------- 1 2 3 4 5 (5 rows) And so does this: rnd=# select ARRAY(SELECT * from parse_string ('1/2/3/4/5', '/')); NOTICE: parse_string () ?column? ------------- {1,2,3,4,5} (1 row) On Thursday 01 December 2005 12:32 pm, Terry Lee Tucker saith: > List, > > I have a simple function: > CREATE OR REPLACE FUNCTION parse_string (TEXT, TEXT) RETURNS SETOF TEXT AS > ' DECLARE > str ALIAS FOR $1; -- the string to parse > delimiter ALIAS FOR $2; -- the delimiter > field TEXT; -- return value from split_part > idx INTEGER DEFAULT 1; -- field counter > funcName TEXT DEFAULT ''parse_string''; -- function name > dbg BOOLEAN DEFAULT True; -- debug print flag > BEGIN > IF dbg THEN > RAISE NOTICE ''% ()'', funcName; > END IF; > SELECT INTO field split_part (str, delimiter, idx); > WHILE field != '''' LOOP > RETURN NEXT field; > idx = idx + 1; > SELECT INTO field split_part (str, delimiter, idx); > END LOOP; > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > As you can see, I'm using split_part to parse the string in a loop. I want > this thing to return the set of values that make up the fields in the > string. When I call the function from psql here is the error I'm getting: > rnd=# select parse_string ('1/2/3/4/5', '/'); > NOTICE: parse_string () > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "parse_string" line 14 at return next > > Then I tried this approach and got the same error: > rnd=# select ARRAY(SELECT parse_string ('1/2/3/4/5', '/')); > NOTICE: parse_string () > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "parse_string" line 14 at return next > > Version Information: > rnd=# select version(); > version > --------------------------------------------------------------------------- >----------------------------------- PostgreSQL 7.4.6 on > i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat > Linux 3.2.3-49) > > I'm sure that I'm doing something stupid. Any input would be appreciated... > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Quote: 80 "Government is not the solution to our problem. Government is the problem." --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
|
04-09-2008, 07:14 AM
| |||
| |||
| Re: Function returning SETOF Terry Lee Tucker wrote: >List, > >I have a simple function: >CREATE OR REPLACE FUNCTION parse_string (TEXT, TEXT) RETURNS SETOF TEXT AS ' >DECLARE > str ALIAS FOR $1; -- the string to parse > delimiter ALIAS FOR $2; -- the delimiter > field TEXT; -- return value from split_part > idx INTEGER DEFAULT 1; -- field counter > funcName TEXT DEFAULT ''parse_string''; -- function name > dbg BOOLEAN DEFAULT True; -- debug print flag >BEGIN > IF dbg THEN > RAISE NOTICE ''% ()'', funcName; > END IF; > SELECT INTO field split_part (str, delimiter, idx); > WHILE field != '''' LOOP > RETURN NEXT field; > idx = idx + 1; > SELECT INTO field split_part (str, delimiter, idx); > END LOOP; > RETURN; >END; >' LANGUAGE 'plpgsql'; > >As you can see, I'm using split_part to parse the string in a loop. I want >this thing to return the set of values that make up the fields in the string. > > Why not try a temp table and a ref cursor? dump the split values into the temp table and return the ref cursor. Tony Caduto AM Software Design Home of PG Lightning Admin http://www.amsoftwaredesign.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
|
04-09-2008, 07:15 AM
| |||
| |||
| Re: Function returning SETOF On Thu, Dec 01, 2005 at 12:32:02PM -0500, Terry Lee Tucker wrote: > List, > > I have a simple function: I have a simpler one  CREATE OR REPLACE FUNCTION split_to_rows(TEXT, TEXT) /* Descriptive name */ RETURNS SETOF TEXT STRICT LANGUAGE sql AS $$ SELECT (string_to_array($1, $2))[s.i] FROM generate_series( 1, array_upper(string_to_array($1, $2), 1) ) AS s(i); $$; Cheers, D > CREATE OR REPLACE FUNCTION parse_string (TEXT, TEXT) RETURNS SETOF TEXT AS ' > DECLARE > str ALIAS FOR $1; -- the string to parse > delimiter ALIAS FOR $2; -- the delimiter > field TEXT; -- return value from split_part > idx INTEGER DEFAULT 1; -- field counter > funcName TEXT DEFAULT ''parse_string''; -- function name > dbg BOOLEAN DEFAULT True; -- debug print flag > BEGIN > IF dbg THEN > RAISE NOTICE ''% ()'', funcName; > END IF; > SELECT INTO field split_part (str, delimiter, idx); > WHILE field != '''' LOOP > RETURN NEXT field; > idx = idx + 1; > SELECT INTO field split_part (str, delimiter, idx); > END LOOP; > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > As you can see, I'm using split_part to parse the string in a loop. I want > this thing to return the set of values that make up the fields in the string. > When I call the function from psql here is the error I'm getting: > rnd=# select parse_string ('1/2/3/4/5', '/'); > NOTICE: parse_string () > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "parse_string" line 14 at return next > > Then I tried this approach and got the same error: > rnd=# select ARRAY(SELECT parse_string ('1/2/3/4/5', '/')); > NOTICE: parse_string () > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "parse_string" line 14 at return next > > Version Information: > rnd=# select version(); > version > -------------------------------------------------------------------------------------------------------------- > PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 > 20030502 (Red Hat Linux 3.2.3-49) > > I'm sure that I'm doing something stupid. Any input would be appreciated... > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
|
04-09-2008, 07:15 AM
| ||||
| ||||
| Re: Function returning SETOF Simpler is better ;o) Thanks for the input... On Thursday 01 December 2005 10:31 pm, David Fetter saith: > On Thu, Dec 01, 2005 at 12:32:02PM -0500, Terry Lee Tucker wrote: > > List, > > > > I have a simple function: > > I have a simpler one > > CREATE OR REPLACE FUNCTION split_to_rows(TEXT, TEXT) /* Descriptive name */ > RETURNS SETOF TEXT > STRICT > LANGUAGE sql > AS $$ > SELECT (string_to_array($1, $2))[s.i] > FROM generate_series( > 1, > array_upper(string_to_array($1, $2), 1) > ) AS s(i); > $$; > > Cheers, > D > ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
Linear Mode
