set-valued function called in context that cannot accept a set
От | Eric B. Ridge |
---|---|
Тема | set-valued function called in context that cannot accept a set |
Дата | |
Msg-id | 629E3D33-3BE3-4A6B-ADC3-B4C59A053F57@tcdi.com обсуждение исходный текст |
Ответы |
Re: set-valued function called in context that cannot accept
a set
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: