Thread: 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
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. +