Thread: WG: Extension using the current connection handle
Hello 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? Regards Wolfgang
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 Oracle table with a BEFORE INSERT trigger that executes the procedure. See https://stackoverflow.com/a/46360383/6464308 Yours, Laurenz Albe
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
Wolfgang Brandl wrote: > 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 lost and all following didn't get a connection. > We did not analyse this. Normally, oracle_fdw re-establishes lost connections, so you get at most one error, and the next statement works again. Yours, Laurenz Albe