Message-ID: <1513176406.5128.36.camel@cybertec.at>
Subject: Re: WG: Extension using the current connection handle
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Brandl, Wolfgang" <wolfgang.brandl@brz.gv.at>,
	"pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Wed, 13 Dec 2017 15:46:46 +0100
In-Reply-To: 
 <F2EB158D72DC6A4F97877DD0BB6F020E61388C03@CMD0MBX-AB01.cmd0.cna.at>
References: 
	<F2EB158D72DC6A4F97877DD0BB6F020E61388C03@CMD0MBX-AB01.cmd0.cna.at>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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 the extension 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