Trouble returning setof - Mailing list pgsql-general

From Rory Campbell-Lange
Subject Trouble returning setof
Date
Msg-id 20030611222955.GF24912@campbell-lange.net
Whole thread Raw
Responses Re: Trouble returning setof
List 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@campbell-lange.net>
<www.campbell-lange.net>

pgsql-general by date:

Previous
From: "Darko Prenosil"
Date:
Subject: Re: DBCS
Next
From: Ron Snyder
Date:
Subject: Re: error restoring large objects during pg_restore (re