select result / functions from another database in plpgsql
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
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
On 11/6/06, Matthew Peter <survivedsushi@yahoo.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
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.
On 11/6/06, Richard Broersma Jr <rabroersma@yahoo.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
--- Richard Huxton <dev@archonet.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
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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +