PL/pgSQL and SETOF

[Cultural Sublimation <cultural_sublimation@xxxxxxxxx>]

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