Function returning SETOF

From: Terry Lee Tucker <terry(at)esc1(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Function returning SETOF
Date: 2005-12-01 17:32:02
Message-ID: 200512011232.02664.terry@esc1.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Terry Lee Tucker 2005-12-01 17:40:07 Re: Function returning SETOF
Previous Message Richard Huxton 2005-12-01 17:25:22 Re: 8.1, OID's and plpgsql