Thread: Options for select from function returning record?

I'm interested to know what options there are in selecting values from a
function returning a RECORD.

For instance, in the query below:

temporary=> SELECT
                *
            FROM
                fn_v1_board_view_board (1, 1)
            AS (n_id integer, t_description varchar, t_name varchar,
                typer integer, n_id_photo integer);

it would be convenient to be able to omit some columns on occasion. Is
this possible, as if the record returned was like a table.

Also is it possible to ever truncate this sort of select as

temporary=> SELECT
                *
            FROM
                fn_v1_board_view_board (1, 1);

ommitting the "AS"?

Thanks,
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

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.
>
> For instance, in the query below:
>
> temporary=> SELECT
>                 *
>             FROM
>                 fn_v1_board_view_board (1, 1)
>             AS (n_id integer, t_description varchar, t_name varchar,
>                 typer integer, n_id_photo integer);
>
> it would be convenient to be able to omit some columns on occasion. Is
> this possible, as if the record returned was like a table.

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

> Also is it possible to ever truncate this sort of select as
>
> temporary=> SELECT
>                 *
>             FROM
>                 fn_v1_board_view_board (1, 1);
>
> 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.

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;

On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:

> On 11/06/03, Stephan Szabo (sszabo@megazone23.bigpanda.com) wrote:
> >
> > On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:
> >
> > > 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?

Right now, I'd suggest it for any case where the result definition would
be known at function creation time rather than only at call time (for
example, this wouldn't make sense for dblink).