set-valued function called in context that cannot accept a set

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

Responses

Browse pgsql-general by date

  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