[GENERAL] PL/pgSQL and SETOF

From: Cultural Sublimation <cultural_sublimation(at)yahoo.com>
Date: Fri Nov 30 2007 - 12:09:28 EST

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 Received on Fri Nov 30 12:10:06 2007