X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id BA0F7D1DAB0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 16:04:24 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 75254-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Feb 2004 12:04:18 -0400 (AST)
Received: from crommail.cromwell-tools.co.uk
	(host217-40-164-180.in-addr.btopenworld.com [217.40.164.180])
	by svr1.postgresql.org (Postfix) with ESMTP id 6DE43D1C513
	for <pgsql-sql@postgresql.org>; Mon, 16 Feb 2004 12:04:16 -0400 (AST)
Message-ID: <4030EA3D.60900@cromwell.co.uk>
Date: Mon, 16 Feb 2004 16:05:17 +0000
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: PGSQL <pgsql-sql@postgresql.org>
Subject: Tip: a function for creating a remote view using dblink
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/200
X-Sequence-Number: 16890

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.