From: | "Eric B(dot) Ridge" <ebr(at)tcdi(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | set-valued function called in context that cannot accept a set |
Date: | 2009-03-06 19:53:29 |
Message-ID: | 629E3D33-3BE3-4A6B-ADC3-B4C59A053F57@tcdi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2009-03-06 20:27:46 | Re: set-valued function called in context that cannot accept a set |
Previous Message | Greg Smith | 2009-03-06 18:31:54 | Re: Maximum transaction rate |