| 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: | Whole Thread | Raw Message | 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 |