On Thu, May 7, 2009 at 7:37 AM, Markus Wollny <Markus.Wollny@xxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
