RECORD return types in general, dblink in particular - Mailing list pgsql-general

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