Tip: a function for creating a remote view using dblink

Tip: a function for creating a remote view using dblink

Post by Mark Gibs » Wed, 18 Feb 2004 01:05:17



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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

      message can get through to the mailing list cleanly

 
 
 

Tip: a function for creating a remote view using dblink

Post by Karsten Hilbe » Wed, 18 Feb 2004 18:08:41


Quote:> 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:
...
> Is there any existing site (a wiki for example) for posting PostgreSQL
> specific tips?

The PG cookbook ?

Karsten
--

E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

Tip: a function for creating a remote view using dblink

Post by Josh Berk » Thu, 19 Feb 2004 03:05:44


Mark,

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

Thanks!  Way cool!

Quote:> 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)

We're working on something, but nothing's up yet.   In the meantime, use the
Techdocs Wiki to post it so that we don't lose track:
http://techdocs.postgresql.org/guides

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

 
 
 

1. Using Views to Create Views with outer joins

Does anyone know if it's possible to create a view from two
other
views using an outer join? So far, it doesn't seem to work. The

result set is the same as an inner join.
I was going crazy trying to figure out what was happening in
my datawindows.
To make things simpler to understand, I created 2 simple tables
with 3 columns each. I created a view from these tables, with
an
outer join and the result set was as expected.
I then created 2 views, one from each of my original 2 tables
that contained all columns from the original tables.
In other words, each view was identical to the table
it was derived from.
When I then created a view from these views with an OJ, the
result set was not correct.
Is this technically not possible, due to the way views work, or
have I discovered yet another bug? Using Watcom 4.0

TIA

--bob

2. Installing solaris on a pc?

3. how to create a view using SQL

4. CMOS settings for RD54

5. Create DB2 view using SQL passthrough?

6. grab user to last modify file

7. Perl crash doing CREATE VIEW using DBI and DBD::DB2

8. Capaciflectors as contact sensors?

9. creating a script from a view which ceates the original view

10. Historic Query using a view/function ?

11. using catalog to create a searchable index - and enabling that function ...

12. create function using language SQL