Trouble returning setof

Поиск
Список
Период
Сортировка
Искать
От
Rory Campbell-Lange
Тема
Trouble returning setof
Дата
Msg-id
20030611222955.GF24912@campbell-lange.net
Список
Дерево обсуждения
Trouble returning setof Rory Campbell-Lange <rory@campbell-lange.net>
Re: Trouble returning setof Joe Conway <mail@joeconway.com>
I am trying to construct a function returning a setof %rowtype.

I'm clearly making some sort of basic error. The error message is:

temporary=> select fn_v1a_board_view_items (); 
WARNING:  Error occurred while executing PL/pgSQL function fn_v1a_board_view_items
WARNING:  while casting return value to function's return type
ERROR:  Set-valued function called in context that cannot accept a set

Help much appreciated!
rory


CREATE TYPE view_board_items as (
	itemid                       INTEGER,
	itemauthor                   INTEGER,
	itemtype                     INT2,
	itemtitle                    VARCHAR,
	itembody                     VARCHAR,
	imageid                      INTEGER,
	imagesrc                     VARCHAR,
	imagewidth                   INT2,
	imageheight                  INT2
);

CREATE OR REPLACE function fn_v1a_board_view_items
	() RETURNS setof view_board_items
    AS '
DECLARE
	resulter                     view_board_items%rowtype;
BEGIN

	FOR resulter IN
		SELECT 

			o.n_id 						       as itemid,
			o.n_creator                        as itemauthor,
			o.n_type                           as itemtype,
			o.t_name                           as itemtitle,
			o.t_description                    as itembody,
			p.n_id                             as imageid,
			p.t_path                           as imagesrc,
			p.n_width                          as imagewidth,
			p.n_height                         as imageheight
		FROM
			object_board b,
			objects o 
				LEFT OUTER JOIN photo p ON o.n_id_photo = p.n_id
				LEFT OUTER JOIN (
					SELECT 
						count(n_id) as comments, n_object_id
					FROM
						comments
					GROUP BY
						n_object_id) as comm
				ON o.n_id = comm.n_object_id
		WHERE
			b.n_board_id = 2
			AND
			b.n_object_id = o.n_id
			AND
			o.b_hidden = ''f''
		ORDER
			by o.dt_modified
		 LOOP
	
		RETURN NEXT
			resulter;

	END LOOP;

	-- no explicit return for setof functions
	RETURN; 

END;'
    LANGUAGE plpgsql;


-- 
Rory Campbell-Lange 


В списке pgsql-general по дате отправления
От: Darko Prenosil
Дата:
Сообщение: Re: DBCS
От: Ron Snyder
Дата:
FAQ