Question about function returning record

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