Thread: Function returning SETOF

Function returning SETOF

From

Terry Lee Tucker

Date:

01 December 2005, 16:33:17

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

Re: Function returning SETOF

From

Terry Lee Tucker

Date:

01 December 2005, 16:41:01

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

Re: Function returning SETOF

From

Tony Caduto

Date:

01 December 2005, 19:00:08

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

Re: Function returning SETOF

From

David Fetter

Date:

02 December 2005, 02:32:25

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!

Re: Function returning SETOF

From

Terry Lee Tucker

Date:

02 December 2005, 08:11:25

Simpler is better ;o)

Thanks for the input...

On Thursday 01 December 2005 10:31 pm, David Fetter saith:
> 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
>