Thread: 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
Re: set-valued function called in context that cannot accept a set
From
hubert depesz lubaczewski
Date:
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