Re: Options for select from function returning record?

Thanks for your help, Stephan.

On 11/06/03, Stephan Szabo (sszabo(at)megazone23(dot)bigpanda(dot)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;