Trouble returning setof

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Trouble returning setof
Date: 2003-06-11 22:29:55
Message-ID: 20030611222955.GF24912@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Snyder 2003-06-11 22:40:35 Re: error restoring large objects during pg_restore (re
Previous Message Matthew Nuzum 2003-06-11 21:37:09 Re: Postgres performance comments from a MySQL user