Обсуждение: set-valued function called in context that cannot accept a set

This is using PG v8.1.

I have a "table function" in C called "unnest".  It takes "anyarray"
as its only argument and returns a set of "anyelement".  It's a handy
little function for turning arrays into sets.

You can use it in two different ways:
    SELECT * FROM unnest(ARRAY[1,2,3]);
or
    SELECT unnest(ARRAY[1,2,3]);

The latter is particularly handy when used like this:

# select unnest(ARRAY[1,2,3]), 'hi';
  unnest | ?column?
--------+----------
       1 | hi
       2 | hi
       3 | hi
(3 rows)


I decided that this function would be easy to rewrite in PL/PGSQL and
then I could stop compiling an extra library every time I install
Postgres.

CREATE OR REPLACE FUNCTION unnest2 (_a anyarray) RETURNS SETOF
anyelement LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
    i int;
    upper int;
BEGIN
    i := 0;
    upper := array_upper(_a, 1);
    FOR i IN 1..upper LOOP
        RETURN NEXT _a[i];
    END LOOP;
    RETURN;
END;
$$;

However, the PL/PGSQL version cannot be used as a column.  Doing so
does results in an error:

# select unnest2(ARRAY[1,2,3]), 'hi';
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "unnest2" line 8 at return next

Is the definition of "unnest2" wrong or is this just a limitation of
PL/PGSQL?  If this can't be done via PL/PGSQL in v8.1, what about v8.3
(or later)?

Any input will be greatly appreciated!

eric

On 06/03/2009 19:53, Eric B. Ridge wrote:
> # select unnest2(ARRAY[1,2,3]), 'hi';
> ERROR:  set-valued function called in context that cannot accept a set
> CONTEXT:  PL/pgSQL function "unnest2" line 8 at return next

When a function returns SETOF something, you need to treat it as if it
were a table, thus:

   select * from unnest2(...);

HTH,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Mar 6, 2009, at 3:27 PM, Raymond O'Donnell wrote:

> When a function returns SETOF something, you need to treat it as if it
> were a table, thus:
>
>   select * from unnest2(...);

Except that isn't true if the function is written in C.

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS 'unnest'
LANGUAGE 'C' IMMUTABLE;

    SELECT unnest(...);

works just fine using the above defined C function.

So my question is really, what's the difference and why doesn't this
work with PL/PGSQL functions?

eric

On Fri, Mar 06, 2009 at 03:33:30PM -0500, Eric B. Ridge wrote:
> So my question is really, what's the difference and why doesn't this
> work with PL/PGSQL functions?

because it is long-time limitation of setof-returning plplsql functions
(i think all pl/*).
it is removed in 8.4
http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On Mar 6, 2009, at 3:49 PM, hubert depesz lubaczewski wrote:

> On Fri, Mar 06, 2009 at 03:33:30PM -0500, Eric B. Ridge wrote:
>> So my question is really, what's the difference and why doesn't this
>> work with PL/PGSQL functions?
>
> because it is long-time limitation of setof-returning plplsql
> functions
> (i think all pl/*).
> it is removed in 8.4

I also now see that 8.4 has its own unnest function built in.
http://developer.postgresql.org/pgdocs/postgres/functions-array.html

One more reason to look forward to 8.4, I suppose.  Until then, I
suppose I'm sticking with my custom C library.  :(

Thanks!

eric