Thread: Question about function returning record

Hi!

I've got a generalized function

    getshadowrecord(schema (varchar),table (varchar), id (int4),
version_id (int))

which returns RECORD. As this function is supposed to work on any table
structure, I need to declare a column alias list when I do a SELECT on
it, like

    SELECT *
    FROM getshadowrecord('foo','article',683503,0) AS shadowrecord (

    id integer,
    type_id integer ,
    headline text,
    strapline text,
    [...]
    );

Now I'd like to make things easier for my developers by supplying sort
of alias functions for each table like

    CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
      IN "_id" int4,
      IN "_versid" int4)
    RETURNS foo.article_shadow AS
    $BODY$
            SELECT *
            FROM getshadowrecord('foo','article',$1,$2) AS
shadowrecord (
            id integer,
            type_id integer ,
            headline text,
            strapline text,
            [...]
            );
    $BODY$
    LANGUAGE SQL VOLATILE;

Using these alias functions, they can simply do a SELECT * FROM
foo.getshadow_article(id,version_id) without having to write the column
list.

As each of those alias functions would correspond exactly to one table,
I wonder if there is a more elegant alternative to explicitly declaring
the column list, something like this:

    CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
      IN "_id" int4,
      IN "_versid" int4)
    RETURNS foo.article_shadow AS
    $BODY$
            SELECT *
            FROM getshadowrecord('foo','article',$1,$2) AS
shadowrecord (foo.article_shadow%rowtype);
    $BODY$
    LANGUAGE SQL VOLATILE;

Unfortunately my example doesn't work, but I think you'll know what I'd
like to do. The only way I see to solve this so far, would be to use
pl/pgsql or pl/perl, issue a query to the information_schema.columns
table, then assemble the query string with the column list and execute
that. I'd like to know if there's some better way to implement this,
something that would somehow use the %rowtype construct.

Kind regards

   Markus

Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de

Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276

On Thu, May 7, 2009 at 7:37 AM, Markus Wollny <Markus.Wollny@computec.de> wrote:
> Hi!
>
> I've got a generalized function
>
>        getshadowrecord(schema (varchar),table (varchar), id (int4),
> version_id (int))
>
> which returns RECORD. As this function is supposed to work on any table
> structure, I need to declare a column alias list when I do a SELECT on
> it, like
>
>        SELECT *
>        FROM getshadowrecord('foo','article',683503,0) AS shadowrecord (
>
>        id integer,
>        type_id integer ,
>        headline text,
>        strapline text,
>        [...]
>        );
>
> Now I'd like to make things easier for my developers by supplying sort
> of alias functions for each table like
>
>        CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
>          IN "_id" int4,
>          IN "_versid" int4)
>        RETURNS foo.article_shadow AS
>        $BODY$
>                SELECT *
>                FROM getshadowrecord('foo','article',$1,$2) AS
> shadowrecord (
>                id integer,
>                type_id integer ,
>                headline text,
>                strapline text,
>                [...]
>                );
>        $BODY$
>        LANGUAGE SQL VOLATILE;
>
> Using these alias functions, they can simply do a SELECT * FROM
> foo.getshadow_article(id,version_id) without having to write the column
> list.
>
> As each of those alias functions would correspond exactly to one table,
> I wonder if there is a more elegant alternative to explicitly declaring
> the column list, something like this:
>
>        CREATE or REPLACE FUNCTION "foo"."getshadow_article"(
>          IN "_id" int4,
>          IN "_versid" int4)
>        RETURNS foo.article_shadow AS
>        $BODY$
>                SELECT *
>                FROM getshadowrecord('foo','article',$1,$2) AS
> shadowrecord (foo.article_shadow%rowtype);
>        $BODY$
>        LANGUAGE SQL VOLATILE;
>
> Unfortunately my example doesn't work, but I think you'll know what I'd
> like to do. The only way I see to solve this so far, would be to use
> pl/pgsql or pl/perl, issue a query to the information_schema.columns
> table, then assemble the query string with the column list and execute
> that. I'd like to know if there's some better way to implement this,
> something that would somehow use the %rowtype construct.
> Kind regards
>

what version postgres?

In recent versions (I think 8.3), you can have a function return
''text' like this:

create function  bar_or_foo(is_bar bool) returns text as
$$
  select case when is_bar then bar::text else foo::text end;
$$ language sql;

select bar_or_foo(true)::bar;

select bar_or_foo(false)::foo;

select (bar).* from (select bar_or_foo(true)::bar as bar);

merlin