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