pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating database links
6+ messages / 4 participants
[nested] [flat]
* Creating database links
@ 2014-05-22 15:06 Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
0 siblings, 1 reply; 6+ messages in thread
From: Ferrell, Denise CTR NSWCDD, Z11 @ 2014-05-22 15:06 UTC (permalink / raw)
To: pgsql-admin
Linux platform using PostgreSQL v9.3
I've looked thru the documentation and I can't find where PostgreSQL allows for database links (something similar to what's available in Oracle).
Does anyone know how to accomplish this or point me in the right direction?
Thank you!
Denise
Attachments:
smime.p7s (application/pkcs7-signature, 5.5K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating database links
@ 2014-05-22 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2014-05-22 15:10 UTC (permalink / raw)
To: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>; +Cc: pgsql-admin
"Ferrell, Denise CTR NSWCDD, Z11" <denise.ferrell.ctr@navy.mil> writes:
> I've looked thru the documentation and I can't find where PostgreSQL allows for database links (something similar to what's available in Oracle).
Closest thing we've got is foreign tables based on the postgres_fdw
foreign data wrapper (or you could use a different wrapper if you
want to link to a non-Postgres database).
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating database links
@ 2014-05-22 15:22 Jorge Fernandez <jorge.fernandez@openenglish.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Jorge Fernandez @ 2014-05-22 15:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>; pgsql-admin
This is what we're using , don't forget to install db_link module
select * from dblink('host=1192.168.1.1 dbname=mydb port=5432 user=myuser
password=my_password', 'select name from country') as t1 (proname name,
prosrc text);
Best Regards...
2014-05-22 12:10 GMT-03:00 Tom Lane <tgl@sss.pgh.pa.us>:
> "Ferrell, Denise CTR NSWCDD, Z11" <denise.ferrell.ctr@navy.mil> writes:
> > I've looked thru the documentation and I can't find where PostgreSQL
> allows for database links (something similar to what's available in Oracle).
>
> Closest thing we've got is foreign tables based on the postgres_fdw
> foreign data wrapper (or you could use a different wrapper if you
> want to link to a non-Postgres database).
>
> regards, tom lane
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
*Jorge Daniel Fernandez*
Leader - DBA Team
S: elgaita99 | t:@openenglish
w: www.openenglish.com
<http://www.openenglish.com/;
Opensoft Argentina S.R.L
552 Suipacha , Piso 3to, Capital Federal
Argentina, (C1022AAR)
(*) The content of this electronic message may be privileged and
confidential.
(*) If you are not an intended recipient of this email, please delete it
and do not use it.
(*) The Open English name and the Open English logo are registered
trademarks.
(*) Opensoft Argentina S.R.L is a vendor company of Open English LLC.
<http://www.openenglish.com/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating database links
@ 2014-05-22 15:39 Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
parent: Jorge Fernandez <jorge.fernandez@openenglish.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ferrell, Denise CTR NSWCDD, Z11 @ 2014-05-22 15:39 UTC (permalink / raw)
To: Jorge Fernandez <jorge.fernandez@openenglish.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thank you. This looks like it's what I'm looking for.
r,
Denise
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jorge Fernandez
Sent: Thursday, May 22, 2014 11:22 AM
To: Tom Lane
Cc: Ferrell, Denise CTR NSWCDD, Z11; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Creating database links
This is what we're using , don't forget to install db_link module
select * from dblink('host=1192.168.1.1 dbname=mydb port=5432 user=myuser password=my_password', 'select name from country') as t1 (proname name, prosrc text);
Best Regards...
2014-05-22 12:10 GMT-03:00 Tom Lane <tgl@sss.pgh.pa.us>:
"Ferrell, Denise CTR NSWCDD, Z11" <denise.ferrell.ctr@navy.mil> writes:
> I've looked thru the documentation and I can't find where PostgreSQL allows for database links (something similar to what's available in Oracle).
Closest thing we've got is foreign tables based on the postgres_fdw
foreign data wrapper (or you could use a different wrapper if you
want to link to a non-Postgres database).
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Jorge Daniel Fernandez
Leader - DBA Team
S: elgaita99 | t:@openenglish
w: www.openenglish.com <http://www.openenglish.com/;
<http://www.openenglish.com/;
Opensoft Argentina S.R.L
552 Suipacha , Piso 3to, Capital Federal Argentina, (C1022AAR)
(*) The content of this electronic message may be privileged and confidential.
(*) If you are not an intended recipient of this email, please delete it and do not use it.
(*) The Open English name and the Open English logo are registered trademarks.
(*) Opensoft Argentina S.R.L is a vendor company of Open English LLC.
<http://www.openenglish.com/;
Attachments:
smime.p7s (application/pkcs7-signature, 5.5K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* FW: Creating database links
@ 2014-10-02 17:43 Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
parent: Jorge Fernandez <jorge.fernandez@openenglish.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ferrell, Denise CTR NSWCDD, Z11 @ 2014-10-02 17:43 UTC (permalink / raw)
To: pgsql-admin
Hi Jorge,
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?
Thank you in advance,
d
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jorge Fernandez
Sent: Thursday, May 22, 2014 11:22 AM
To: Tom Lane
Cc: Ferrell, Denise CTR NSWCDD, Z11; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Creating database links
This is what we're using , don't forget to install db_link module
select * from dblink('host=1192.168.1.1 dbname=mydb port=5432 user=myuser password=my_password', 'select name from country') as t1 (proname name, prosrc text);
Best Regards...
2014-05-22 12:10 GMT-03:00 Tom Lane <tgl@sss.pgh.pa.us>:
"Ferrell, Denise CTR NSWCDD, Z11" <denise.ferrell.ctr@navy.mil> writes:
> I've looked thru the documentation and I can't find where PostgreSQL allows for database links (something similar to what's available in Oracle).
Closest thing we've got is foreign tables based on the postgres_fdw
foreign data wrapper (or you could use a different wrapper if you
want to link to a non-Postgres database).
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Jorge Daniel Fernandez
Leader - DBA Team
S: elgaita99 | t:@openenglish
w: www.openenglish.com <http://www.openenglish.com/;
<http://www.openenglish.com/;
Opensoft Argentina S.R.L
552 Suipacha , Piso 3to, Capital Federal Argentina, (C1022AAR)
(*) The content of this electronic message may be privileged and confidential.
(*) If you are not an intended recipient of this email, please delete it and do not use it.
(*) The Open English name and the Open English logo are registered trademarks.
(*) Opensoft Argentina S.R.L is a vendor company of Open English LLC.
<http://www.openenglish.com/;
--
Jorge Daniel Fernandez
Leader - DBA Team
S: elgaita99 | t:@openenglish
w: www.openenglish.com <http://www.openenglish.com/;
<http://www.openenglish.com/;
Opensoft Argentina S.R.L
552 Suipacha , Piso 3to, Capital Federal Argentina, (C1022AAR)
(*) The content of this electronic message may be privileged and confidential.
(*) If you are not an intended recipient of this email, please delete it and do not use it.
(*) The Open English name and the Open English logo are registered trademarks.
(*) Opensoft Argentina S.R.L is a vendor company of Open English LLC.
<http://www.openenglish.com/;
Attachments:
smime.p7s (application/pkcs7-signature, 5.5K) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: FW: Creating database links
@ 2014-10-03 18:01 Stephen Frost <sfrost@snowman.net>
parent: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2014-10-03 18:01 UTC (permalink / raw)
To: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-10-03 18:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-05-22 15:06 Creating database links Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
2014-05-22 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-05-22 15:22 ` Jorge Fernandez <jorge.fernandez@openenglish.com>
2014-05-22 15:39 ` Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
2014-10-02 17:43 ` Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
2014-10-03 18:01 ` Stephen Frost <sfrost@snowman.net>
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