From: Stephen Frost <sfrost@snowman.net>
To: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: FW: Creating database links
Date: Fri, 3 Oct 2014 14:01:28 -0400
Message-ID: <20141003180128.GO28859@tamriel.snowman.net> (raw)
In-Reply-To: <2BC18916DE4BF141AC1EFC55CE2CAE7D5817E5@NAEANRFKXM01V.nadsusea.nads.navy.mil>
References: <2BC18916DE4BF141AC1EFC55CE2CAE7D2B02B9@NAEANRFKXM01V.nadsusea.nads.navy.mil>
<4063.1400771439@sss.pgh.pa.us>
<CAOwNKidR+xu_GJmeqj-aCALJ3pOEkBjLOcLAYUAkfTLS4RSOjQ@mail.gmail.com>
<2BC18916DE4BF141AC1EFC55CE2CAE7D2B13F2@NAEANRFKXM01V.nadsusea.nads.navy.mil>
<CAOwNKieFmhh4xh0e0k5XoX1pLDMhEJoRO_X16X1viT6khrixwg@mail.gmail.com>
<2BC18916DE4BF141AC1EFC55CE2CAE7D5817E5@NAEANRFKXM01V.nadsusea.nads.navy.mil>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Denise,
* Ferrell, Denise CTR NSWCDD, Z11 (denise.ferrell.ctr@navy.mil) wrote:
> What would be the syntax for extracting all columns from a database table using dblink? Could this be used if you are unsure of the structure of the table you're extracting from?
To grab all columns from a remote table, you could use:
select row_to_json(apples) from apples;
This would return a json blob, but unfortunately you can't trivially
convert that into a record at this time. You could turn it into a set
of key/value pairs though:
select
b.*
from (select row_to_json(data.*) from data) as a,
lateral json_each(a.row_to_json) as b;
You could write a PL/pgsql procedure to query the remote catalog and
determine what the columns are and create the view using dblink
accordingly too.
To get the columns for a database table, you can query pg_attribute,
with pg_class, like so:
-- Get the column names for the 'apples' table
-- attnum > 0 avoids internal PG columns
select
attname
from
pg_attribute
join pg_class on (attrelid = oid)
where relname = 'apples'
and attnum > 0;
You would need to include pg_namespace if you might have an 'apples'
table in multiple schemas.
What would probably be interesting to you also is the new IMPORT FOREIGN
SCHEMA capability which was recently added to PG and will be in
PostgreSQL 9.5:
http://www.postgresql.org/docs/devel/static/sql-importforeignschema.html
Or, perhaps, just using the postgres_fdw and foreign data tables instead
of using dblink at all might work.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, denise.ferrell.ctr@navy.mil
Subject: Re: FW: Creating database links
In-Reply-To: <20141003180128.GO28859@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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