Re: Options for select from function returning record? - Mailing list pgsql-general

Thanks for your help, Stephan.

On 11/06/03, Stephan Szabo (sszabo@megazone23.bigpanda.com) wrote:
>
> On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:
>
> > I'm interested to know what options there are in selecting values from a
> > function returning a RECORD.

> You can use a list like n_id, t_description instead of * in the select I
> believe just as usual.

That works. Thanks!
>
> > Also is it possible to ever truncate this sort of select as
...
> > ommitting the "AS"?
>
> Not for a function returning records currently.  If the type is known and
> constant, you can instead make a composite type with CREATE TYPE AS and
> have the function return those rather than record.

I thought that making a type makes the query simpler (for the client
application). The example below uses the %rowtype row variable type (and
works!). Is this a recommended approach?

Kind regards,
Rory


CREATE TYPE view_board as (
        brdtitle varchar, brddescrip varchar, brdtype INT2,
        brdid INTEGER, imgsrc varchar, imgid INT2, imgwidth INT2,
        imgheight INT2, itemscreate boolean, commentcreate boolean,
        personcreate boolean, boardcreate boolean, shareable boolean, loggedin boolean
        );

CREATE OR REPLACE FUNCTION fn_v1_board_view_board2
    (integer, integer) RETURNS view_board
    AS '
DECLARE
    boardid                ALIAS for $1;
    personid               ALIAS for $2;
    recone                 RECORD;
    resulter               view_board%rowtype;
BEGIN
    SELECT INTO
        recone
        n_id, t_description, t_name, n_type, n_id_photo
    FROM
        boards
    WHERE
        n_id = boardid;

    resulter.brdtitle               := recone.t_name;
    resulter.brddescrip             := recone.t_description;
    resulter.brdtype                := recone.n_type;
    resulter.brdid                  := recone.n_id;
    resulter.imgsrc                 := ''5'';
    resulter.imgid                  := 12;
    resulter.imgwidth               := NULL;
    resulter.imgheight              := NULL;
    resulter.itemscreate            := ''t'';
    resulter.commentcreate          := ''t'';
    resulter.personcreate           := ''t'';
    resulter.boardcreate            := ''t'';

    RETURN resulter;

END;'
    LANGUAGE plpgsql;