public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adam Brusselback <[email protected]>
To: [email protected]
Subject: Re: Calling oracle function from PostgreSQL
Date: Mon, 2 Sep 2024 10:50:33 -0400
Message-ID: <CAMjNa7eGhfXMPJ8PLvjRhzeTAU4gF5OMcy=uASzea4oXhXdDuw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
	<[email protected]>

Re: That table hack
Oh man is scary as can be (to me). I think I would go with another option
(maybe outside of the database) entirely rather than introducing that into
my codebase.

Onto the general need:
I've definitely had the need for foreign function calls between my (both
Postgres) databases (e.g. dwh server calling a function to get some info
from oltp server), and I had to resort to dblink for that. Would have been
very nice if the FDW interface had support for functions / stored
procedures as first class citizens as long as the fdw implementation (and
other endpoint) support functions / stored procedures.

Once could dream.

-Adam

On Mon, Sep 2, 2024 at 9:34 AM Laurenz Albe <[email protected]>
wrote:

> On Fri, 2024-08-30 at 12:38 +0530, Shweta Rahate wrote:
> > In my application there is a requirement to call the oracle function
> from PostgreSQL db.
> >
> > The oracle function should take the input from Postgres db and returns
> the output.
> > Please suggest a way to achieve this.
>
> There is no direct way to do this via oracle_fdw.
>
> There are, however, a couple of hacks to do that; see the following
> example:
>
> The Oracle function:
>
>   CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS
>   BEGIN
>      RETURN n * 2;
>   END;
>   /
>
> Then I can define an Oracle table with a single row and a trigger on it:
>
>   CREATE TABLE call_double(inp NUMBER, outp NUMBER);
>
>   INSERT INTO call_double VALUES (1, 1);
>
>   COMMIT;
>
>   CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW
>   BEGIN
>      :NEW.outp := double(:NEW.inp);
>   END;
>   /
>
> Now I can define a foreign table as follows:
>
>   CREATE FOREIGN TABLE call_double(
>      inp numeric OPTIONS (key 'true'),
>      outp numeric)
>   SERVER oracle OPTIONS (table 'CALL_DOUBLE');
>
> And then the following UPDATE calls the function and returns the result:
>
>   UPDATE call_double SET inp = 12 RETURNING outp;
>
> That's ugly, but perhaps it is good enough as a workaround.
>
> Yours,
> Laurenz Albe
>
>
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Calling oracle function from PostgreSQL
  In-Reply-To: <CAMjNa7eGhfXMPJ8PLvjRhzeTAU4gF5OMcy=uASzea4oXhXdDuw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox