Re: Question about function returning record

On Thu, May 7, 2009 at 7:37 AM, Markus Wollny <Markus(dot)Wollny(at)computec(dot)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