Date: Fri, 3 Oct 2014 14:01:28 -0400
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
Message-ID: <20141003180128.GO28859@tamriel.snowman.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="suYNV1AwziNMnBoj"
Content-Disposition: inline
In-Reply-To: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D5817E5@NAEANRFKXM01V.nadsusea.nads.navy.mil>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--suYNV1AwziNMnBoj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 th=
e 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.*
=66rom (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
=66rom
  pg_attribute
  join pg_class on (attrelid =3D oid)
where relname =3D '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

--suYNV1AwziNMnBoj
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBAgAGBQJULuR3AAoJEO1sijiDR2RVMnAP/At40h2ETjcvfvb89c/LAKVu
bP3WlRAYIRvM5tUAjgI5+UdNw9vV+P9Wnh8Pa839sRgGlneoRudoksRdrGQQYeIa
Pw1RR5BoWOCrXJuiRmgdMYGmW8LJZxRsM+W0mGibWlOwi78W6LkoVYFsLRNszRjg
c4IA7PFjd74GKafa+ezxX9i3Fi7hD2ir0PcjdMCZRfZ8drmGTQ15APy8wDPxFE26
1d2TzdJRdc7Bypbr/LImn5Je6eSyxJH3kdAYfhcvTPefR19W656v2S73gQjC8EMb
FG73IvZHWwsDKbjaNVnvgjzzEAeNPSrPIsM2Gp500NsnQiUvwj3GEFC9/bd4iXYE
4HlrtZkkr6/mP//ptYZOU60NMi+MuDnoARI9iUGkySQlZAj+IoVIYNnnyBHngJxx
LyZWt7nKw20GvCYnYTA2lF0c9kzyYxdrRI1qcLRHXS1kRL13ROn6duXojkTvPTcH
RtbTjA47oFf4TH9gqkYio07JMrYvEw3xTu6AXovw3qAYVrAjxDmCh5h7Rly07WLo
sTMmKQW9AHzgd1DnknBkiKi2hkr7xiQL87l7UTeFFJdeNqG2EgIyRrswDgmP5lVS
JFWwvIqCA8SfbD3xGv04Ny+ly+uj058RgtMvEgENPgqYr8R2MK+cwjZ41QhlX1+l
4+mlmGvHHLWEEcDe18my
=2JJu
-----END PGP SIGNATURE-----

--suYNV1AwziNMnBoj--