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
