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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
