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
<rory@campbell-lange.net>
<www.campbell-lange.net>
