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

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

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!

Simpler is better ;o)

Thanks for the input...

On Thursday 01 December 2005 10:31 pm, David Fetter saith: