pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTip: a function for creating a remote view using dblink
3+ messages / 3 participants
[nested] [flat]
* Tip: a function for creating a remote view using dblink
@ 2004-02-16 16:05 Mark Gibson <gibsonm@cromwell.co.uk>
0 siblings, 2 replies; 3+ messages in thread
From: Mark Gibson @ 2004-02-16 16:05 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tip: a function for creating a remote view using dblink
@ 2004-02-17 09:08 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Mark Gibson <gibsonm@cromwell.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Karsten Hilbert @ 2004-02-17 09:08 UTC (permalink / raw)
To: 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:
...
> Is there any existing site (a wiki for example) for posting PostgreSQL
> specific tips?
The PG cookbook ?
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tip: a function for creating a remote view using dblink
@ 2004-02-17 18:05 Josh Berkus <josh@agliodbs.com>
parent: Mark Gibson <gibsonm@cromwell.co.uk>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2004-02-17 18:05 UTC (permalink / raw)
To: Mark Gibson <gibsonm@cromwell.co.uk>; pgsql-sql
Mark,
> I'm posting a function here in the hope others may find it useful
> and/or correct my mistakes/make improvements :)
Thanks! Way cool!
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-17 18:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-16 16:05 Tip: a function for creating a remote view using dblink Mark Gibson <gibsonm@cromwell.co.uk>
2004-02-17 09:08 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2004-02-17 18:05 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox