RECORD return types in general, dblink in particular

From: Jeremy Semeiks <jrs(at)farviolet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: RECORD return types in general, dblink in particular
Date: 2006-01-06 18:57:48
Message-ID: 20060106185748.GG17397@farviolet.farviolet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message Scott Ribe 2006-01-06 19:10:33 Re: 'Official' definition of ACID compliance?
Previous Message Bruno Almeida do Lago 2006-01-06 18:40:02 More atomic online backup