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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
