Re: WG: Extension using the current connection handle

Hello
Thanks for the Tipp with the SPI.
We are not using oracle_fdw, becaue we got somehow problems with the connection. Therefore some of the transactions get
lostand all following didn't get a connection.
 
We did not analyse this.

Regards Wolfgang

-----Ursprüngliche Nachricht-----
Von: Laurenz Albe [mailto:laurenz.albe@cybertec.at] 
Gesendet: Mittwoch, 13. Dezember 2017 15:47
An: Brandl, Wolfgang; pgsql-admin@postgresql.org
Betreff: Re: WG: Extension using the current connection handle

Wolfgang Brandl wrote:
> I have written a extension in postgres to call a Oracle Stored procedure.
> We have a Postgres table holding the usernames and password related to the Oracle SID.
> 
> In my extension I wanted to select the username and password out of this table.
> Do I have to make a new Postgres connection or can I somehow use the current connection handel from the session Where
theextension will be called?
 

You would use the SPI interface to query database tables from a C function:
https://www.postgresql.org/docs/current/static/spi.html

If you don't feel like reinventing the wheel, there are tricks how to do that with oracle_fdw.  You could create an
Oracletable with a BEFORE INSERT trigger that executes the procedure.
 

See https://stackoverflow.com/a/46360383/6464308

Yours,
Laurenz Albe