Hello,
I'm trying to use dblink to build an interface to a number of foreign
databases. In particular, my goal is to hide the relevant data in
those databases behind a number of local updateable views, which I can
then treat interchangeably with my own local tables.
To abstract the act of connecting to a particular database from the
act of querying that database, I created a function dblink_cache on
top of dblink. dblink_cache supports transparent caching of
connections behind keys:
create or replace function dblink_cache(text, text, text)
returns setof record volatile security definer as $$
declare
dbname alias for $1; -- stable key for connection
st alias for $2; -- SELECT statement to execute
rectype alias for $3; -- result column definition list (see below)
conn_name text; -- protected dblink connection string
rec record;
begin
[ ... find our connection to dbname ... ]
for rec in execute
'select * from dblink(\''||conn_name||'\', \''||st||'\') as '||rectype
loop
return next rec;
end loop;
return;
end;
$$ language plpgsql;
This function works, but I can't figure out a simple way to avoid
specifying the returned column definition list multiple times in my
calls. For example:
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
'redundant_col_spec (datname text, encoding int)')
as redundant_col_spec (datname text, encoding int);
One (still sub-optimal) solution that came to mind was to define
a composite type, then use that as the column def list:
create type my_foreign_type as (datname text, encoding int);
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
'my_foreign_type')
as my_foreign_type;
However, this call gives the error
ERROR: a column definition list is required for functions returning "record"
so, unless my syntax is wrong somewhere above, I'm assuming that I
cannot use a composite type as shorthand for the full column def list.
The best solution I've found so far is the following awful psql
variable interpolation hack:
\set t 'redundant_col_spec (datname text, encoding int)'
\set qt '\'':t'\''
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
:qt)
as :t;
The normal suggested solution I see to this type of problem in the
archives is to define a function that returns an explicit composite
type rather than SETOF RECORD, but I don't see any way to do this for
the case of my dblink_cache function, and I have quite a few objects
to define that will utilize the functionality of dblink_cache. Is
there a better way to do what I'm trying to do?
More abstractly and idealistically, this type of problem is clearly
not limited to dblink. I would think that the optimal solution would
be some simple automated introspection. After all, the query "select
datname, encoding from pg_database" will always return the same column
types, and in theory the database could figure out what those types
are without me having to specify them even once. Does such a feature
exist, or would it be straightforward to implement?
Thanks,
Jeremy
