Tip: a function for creating a remote view using dblink

From: Mark Gibson <gibsonm(at)cromwell(dot)co(dot)uk>
To: PGSQL <pgsql-sql(at)postgresql(dot)org>
Subject: Tip: a function for creating a remote view using dblink
Date: 2004-02-16 16:05:17
Message-ID: 4030EA3D.60900@cromwell.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,
I'm posting a function here in the hope others may find it useful
and/or correct my mistakes/make improvements :)

This creates a view of a remote table, using dblink:

CREATE OR REPLACE FUNCTION dblink_create_view(text, text, text)
RETURNS VOID
LANGUAGE plpgsql
STRICT
AS '
DECLARE
connstr ALIAS FOR $1;
remote_name ALIAS FOR $2;
local_name ALIAS FOR $3;
schema_name text;
table_name text;
rec RECORD;
col_names text := '''';
col_defs text := '''';
sql_str text;
BEGIN

schema_name := split_part(remote_name, ''.'', 1);
table_name := split_part(remote_name, ''.'', 2);

FOR rec IN
SELECT * FROM dblink(connstr,
''SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '' || quote_literal(schema_name) || '' AND
c.relname = '' || quote_literal(table_name) || '' AND
a.attisdropped = false AND
a.attnum > 0'')
AS rel (n name, t text)
LOOP
col_names := col_names || quote_ident(rec.n) || '','';
col_defs := col_defs || quote_ident(rec.n) || '' '' || rec.t || '','';
END LOOP;

sql_str := ''CREATE VIEW '' || local_name ||
'' AS SELECT * FROM dblink('' || quote_literal(connstr) || '','' ||
quote_literal(''SELECT '' || trim(trailing '','' from col_names) ||
'' FROM '' || quote_ident(schema_name) || ''.'' ||
quote_ident(table_name)) ||
'') AS rel ('' || trim(trailing '','' from col_defs) || '')'';

EXECUTE sql_str;
RETURN;
END
';

Usage example:
SELECT dblink_create_view('host=... dbname=... user=...',
'schema.remote_table', 'local_view');
SELECT * FROM local_view;

The schema MUST be specified for the remote table name.

Suggestions for improvement welcome. Any ideas?

Is there any existing site (a wiki for example) for posting PostgreSQL
specific tips?
(Wasn't sure if pgsql-sql is the right place for this kind of thing)

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message lowdog 2004-02-16 16:07:30 SQL query seach + rearranging results
Previous Message Mark Roberts 2004-02-16 15:56:49 Re: Date format problems