| Lists: | pgsql-general |
|---|
| From: | Matthew Peter <survivedsushi(at)yahoo(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | select result / functions from another database in plpgsql |
| Date: | 2006-11-06 08:25:49 |
| Message-ID: | 20061106082549.98686.qmail@web35213.mail.mud.yahoo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
I have DB1 and DB2 pg_databases... I then have a function in DB2 that wants some results from DB1, is it possible to query another db? Like how \! lets you hit the command line... but I need it in plpgsql if possible.
Also, I know functions are local to db (unless it inserted into template1 prior to createdb) but is there any other way to call functions from another db? Thanks!
---------------------------------
Sponsored Link
Free Uniden 5.8GHz Phone System with Packet8 Internet Phone Service
| From: | Richard Huxton <dev(at)archonet(dot)com> |
|---|---|
| To: | Matthew Peter <survivedsushi(at)yahoo(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database in |
| Date: | 2006-11-06 08:30:48 |
| Message-ID: | 454EF2B8.1070807@archonet.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Matthew Peter wrote:
> I have DB1 and DB2 pg_databases... I then have a function in DB2 that
> wants some results from DB1, is it possible to query another db? Like
> how \! lets you hit the command line... but I need it in plpgsql if
> possible.
Look into the db_link or dbi_link packages. These are exactly what you
are after.
--
Richard Huxton
Archonet Ltd
| From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
|---|---|
| To: | "Matthew Peter" <survivedsushi(at)yahoo(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database in plpgsql |
| Date: | 2006-11-06 14:31:02 |
| Message-ID: | b42b73150611060631w3fdbc5cer641034aba5aab89f@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On 11/6/06, Matthew Peter <survivedsushi(at)yahoo(dot)com> wrote:
> I have DB1 and DB2 pg_databases... I then have a function in DB2 that wants
> some results from DB1, is it possible to query another db? Like how \! lets
> you hit the command line... but I need it in plpgsql if possible.
>
>
> Also, I know functions are local to db (unless it inserted into template1
> prior to createdb) but is there any other way to call functions from another
> db? Thanks!
check out dblink contrib module for starters. Maybe take a look at
pl/sh, which looks pretty neat.
merlin
| From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
|---|---|
| To: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Matthew Peter <survivedsushi(at)yahoo(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database in plpgsql |
| Date: | 2006-11-06 14:48:22 |
| Message-ID: | 699863.79060.qm@web31804.mail.mud.yahoo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
> check out dblink contrib module for starters. Maybe take a look at
> pl/sh, which looks pretty neat.
Is there really such a thing as PL/SH? or are you refering to piping sql queries into psql?
Regards,
Richard Broersma Jr.
| From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
|---|---|
| To: | "Richard Broersma Jr" <rabroersma(at)yahoo(dot)com> |
| Cc: | "Matthew Peter" <survivedsushi(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database in plpgsql |
| Date: | 2006-11-06 15:27:50 |
| Message-ID: | b42b73150611060727l36f9b6f1n63dcf9bb98549ffb@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On 11/6/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> > check out dblink contrib module for starters. Maybe take a look at
> > pl/sh, which looks pretty neat.
>
> Is there really such a thing as PL/SH? or are you refering to piping sql queries into psql?
http://plsh.projects.postgresql.org/
It would be interesting to compare the pl/sh approach vs. the
traditional cron/bash/psql -tAc approach.
merlin
| From: | Matthew Peter <survivedsushi(at)yahoo(dot)com> |
|---|---|
| To: | Richard Huxton <dev(at)archonet(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database in plpgsql |
| Date: | 2006-11-06 20:16:53 |
| Message-ID: | 20061106201653.99644.qmail@web35203.mail.mud.yahoo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
--- Richard Huxton <dev(at)archonet(dot)com> wrote:
> Matthew Peter wrote:
> > I have DB1 and DB2 pg_databases... I then have a function in DB2 that
> > wants some results from DB1, is it possible to query another db? Like
> > how \! lets you hit the command line... but I need it in plpgsql if
> > possible.
>
> Look into the db_link or dbi_link packages. These are exactly what you
> are after.
>
> --
> Richard Huxton
> Archonet Ltd
>
Perfect. Leave it to me not to check the contrib package first :) Thanks again
____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Richard Huxton <dev(at)archonet(dot)com> |
| Cc: | Matthew Peter <survivedsushi(at)yahoo(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: select result / functions from another database |
| Date: | 2006-11-18 02:28:06 |
| Message-ID: | 200611180228.kAI2S6m18709@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Richard Huxton wrote:
> Matthew Peter wrote:
> > I have DB1 and DB2 pg_databases... I then have a function in DB2 that
> > wants some results from DB1, is it possible to query another db? Like
> > how \! lets you hit the command line... but I need it in plpgsql if
> > possible.
>
> Look into the db_link or dbi_link packages. These are exactly what you
> are after.
Uh, folks, we have an FAQ item on this:
<H3 id="item4.17">4.17) How do I perform queries using
multiple databases?</H3>
Please point folks to the FAQ, because if they don't know this answer,
they don't know other answers as well.
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +