Re: Plpsql connecting to more than one database?

On Wed, Mar 30, 2016 at 5:18 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wednesday, March 30, 2016, Francisco Reyes <lists(at)natserv(dot)net> wrote:
>
>> Is it possible to have a plpsql program connect to 2 databases?
>>
>> This seems to imply it,
>> http://stackoverflow.com/questions/18257003/pl-sql-querying-a-table-on-multiple-databases
>>
>> However looking at the postgresql plpsql reference,
>> http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING,
>> doesn't seem like there is a way to specify database. Is the stackoverflow
>> for plpsql in some other database (not postgresql)?
>>
>> Any pointers greatly appreciated.
>>
>> Have users that are familiar with plpsql and have a process which needs
>> to be done withing transactions in 2 databases.
>> begin in db1
>> begin in db2
>>
>> work on 1
>> work on 2
>>
>> if work on both dbs worked
>>
>> commit db1
>> commit db2
>>
>> Do they need to use some other language (Java/Python)? How about a
>> foreign data wrapper?
>>
>>
> It's plpgsql, potentially with a slash, pl/pgsql.
>
> You would have to use foreign data wrappers or dblink if you want a
> function to speak to a database other than the one it's calling session is
> attached to.
>
> Your SO link references Oracle's language. It is called pl/SQL there.
> Neither one has "plpsql".
>
> David J.
>
>
Have you looked at the dblink extension? That certainly give you the
possibility to connect to more than one db.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.