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(at)esc1(dot)com