MIME-Version: 1.0
References: 
 <CAPuJPo5GgMXnwtdOERwHgrEgthqyX+iO0GL6Mv7qCw7uOz6T8g@mail.gmail.com>
 <667ba77c9d834363c2d23dab61ab2f003f68be8c.camel@cybertec.at>
In-Reply-To: <667ba77c9d834363c2d23dab61ab2f003f68be8c.camel@cybertec.at>
From: Adam Brusselback <adambrusselback@gmail.com>
Date: Mon, 2 Sep 2024 10:50:33 -0400
Message-ID: 
 <CAMjNa7eGhfXMPJ8PLvjRhzeTAU4gF5OMcy=uASzea4oXhXdDuw@mail.gmail.com>
Subject: Re: Calling oracle function from PostgreSQL
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000019ab19062124115d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMjNa7eGhfXMPJ8PLvjRhzeTAU4gF5OMcy%3DuASzea4oXhXdDuw%40mail.gmail.com>
Precedence: bulk

--00000000000019ab19062124115d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.=
at>
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 :=3D 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 =3D 12 RETURNING outp;
>
> That's ugly, but perhaps it is good enough as a workaround.
>
> Yours,
> Laurenz Albe
>
>
>

--00000000000019ab19062124115d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div></div>Re: That table hack<br> Oh man i=
s scary as can be (to
 me). I think I would go with another option (maybe outside of the=20
database) entirely rather than introducing that into my codebase.</div><div=
><br></div><div>Onto the general need:<br></div><div>I&#39;ve definitely ha=
d 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 i=
nterface had support for functions / stored procedures as first class citiz=
ens as long as the fdw implementation (and other endpoint) support function=
s / stored procedures.<br><br></div>Once could dream.</div><br></div>-Adam<=
br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Mon, Sep 2, 2024 at 9:34=E2=80=AFAM Laurenz Albe &lt;<a href=3D"mailt=
o:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></di=
v><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">On Fri, 2024-08-30 at 1=
2:38 +0530, Shweta Rahate wrote:<br>
&gt; In my application there is a requirement to call the oracle function f=
rom PostgreSQL db.<br>
&gt; <br>
&gt; The oracle function should take the input from Postgres db and returns=
 the output.<br>
&gt; Please suggest a way to achieve=C2=A0this.=C2=A0<br>
<br>
There is no direct way to do this via oracle_fdw.<br>
<br>
There are, however, a couple of hacks to do that; see the following example=
:<br>
<br>
The Oracle function:<br>
<br>
=C2=A0 CREATE OR REPLACE FUNCTION double(n NUMBER) RETURN NUMBER AS<br>
=C2=A0 BEGIN<br>
=C2=A0 =C2=A0 =C2=A0RETURN n * 2;<br>
=C2=A0 END;<br>
=C2=A0 /<br>
<br>
Then I can define an Oracle table with a single row and a trigger on it:<br=
>
<br>
=C2=A0 CREATE TABLE call_double(inp NUMBER, outp NUMBER);<br>
<br>
=C2=A0 INSERT INTO call_double VALUES (1, 1);<br>
<br>
=C2=A0 COMMIT;<br>
<br>
=C2=A0 CREATE TRIGGER double_trig BEFORE UPDATE ON call_double FOR EACH ROW=
<br>
=C2=A0 BEGIN<br>
=C2=A0 =C2=A0 =C2=A0:NEW.outp :=3D double(:NEW.inp);<br>
=C2=A0 END;<br>
=C2=A0 /<br>
<br>
Now I can define a foreign table as follows:<br>
<br>
=C2=A0 CREATE FOREIGN TABLE call_double(<br>
=C2=A0 =C2=A0 =C2=A0inp numeric OPTIONS (key &#39;true&#39;),<br>
=C2=A0 =C2=A0 =C2=A0outp numeric)<br>
=C2=A0 SERVER oracle OPTIONS (table &#39;CALL_DOUBLE&#39;);<br>
<br>
And then the following UPDATE calls the function and returns the result:<br=
>
<br>
=C2=A0 UPDATE call_double SET inp =3D 12 RETURNING outp;<br>
<br>
That&#39;s ugly, but perhaps it is good enough as a workaround.<br>
<br>
Yours,<br>
Laurenz Albe<br>
<br>
<br>
</blockquote></div>

--00000000000019ab19062124115d--