PL/pgSQL and SETOF - Mailing list pgsql-general

Hi,

I am having trouble getting a really simple PL/pgSQL function to work.
I am beginning to wonder if there is not a bug somewhere, or if Postgresql's
type system is not broken.  Anyway, suppose I have the following table and
type defined:

    CREATE TABLE items
        (
        item_id        int,
        item_name    text,
        item_etc    text
        );

    CREATE TYPE simple_item_t AS
        (
        item_id        int,
        item_name    text
        );


It's easy to create a SQL function that returns a set of simple items:

    CREATE FUNCTION get_items ()
    RETURNS SETOF simple_item_t
    LANGUAGE sql STABLE AS
    $$
        SELECT item_id, item_name FROM items;
    $$;


Now, all I want is to create the equivalent PL/pgSQL function.  Nothing
more, nothing less.  This is the simplest version I can come up with:

    CREATE FUNCTION get_items2 ()
    RETURNS SETOF simple_item_t
    LANGUAGE plpgsql STABLE AS
    $$
    DECLARE
        item    simple_item_t%ROWTYPE;
    BEGIN
        FOR item IN SELECT item_id, item_name FROM items
            LOOP
                RETURN NEXT item;
            END LOOP;
        RETURN;
    END
    $$;


Unfortunately it doesn't work!  Postgresql complains that "set-valued
function called in context that cannot accept a set".  Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?

Thanks in advance!
C.S.



      ____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs