Execute a function through fdw

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE <Patrick.Fiche@aqsacom.com> a
écrit :

Hello,

Is it possible to execute a function located on a server accessed through
Postgres fdw.

This function returns a TABLE structure.

I have mapped rmt_schema and there is a function called Get_Tables in this
schema.

I would like to execute something like :

SELECT * FROM rmt_schema.Get_Tables();

Or is it required to create a local function that will access to remote
tables in order to achieve the same result ?

It's probably easier to create a view on the remote server, and access it
as a foreign table on the local server.

Guillaume Lelarge <guillaume@lelarge.info> writes:

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE <Patrick.Fiche@aqsacom.com> a
écrit :

Is it possible to execute a function located on a server accessed through
Postgres fdw.

It's probably easier to create a view on the remote server, and access it
as a foreign table on the local server.

Yeah. Or if you really want to call a remote function by name, see
dblink. postgres_fdw actively avoids doing that sort of thing.

regards, tom lane

Thanks a lot for your answer.
Using a view is really a good solution for my case.
As I already use fdw for some other cases, I prefer not to mix with dblink.

Regards,

Patrick Fiche

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, October 18, 2019 1:55 PM
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Patrick FICHE <Patrick.Fiche@aqsacom.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Execute a function through fdw

Guillaume Lelarge <guillaume@lelarge.info> writes:

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE
<Patrick.Fiche@aqsacom.com> a écrit :

Is it possible to execute a function located on a server accessed
through Postgres fdw.

It's probably easier to create a view on the remote server, and access
it as a foreign table on the local server.

Yeah. Or if you really want to call a remote function by name, see dblink. postgres_fdw actively avoids doing that sort of thing.

regards, tom lane

Hi,

I got one more issue after I created my view.

I created it on my Server 1 but I am unable to view it on the Server 2.
I can see all tables through fdw after IMPORT FOREIGN SCHEMA.

I was able to get access to my view only after recreating the SERVER / USER MAPPING on Server 2.

Is it the expected behavior to recreate the FOREIGN SERVER / SCHEMA after a new table or view has been created ?

Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96

-----Original Message-----
From: Patrick FICHE <Patrick.Fiche@aqsacom.com>
Sent: Friday, October 18, 2019 2:35 PM
To: Tom Lane <tgl@sss.pgh.pa.us>; Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: RE: Execute a function through fdw

Thanks a lot for your answer.
Using a view is really a good solution for my case.
As I already use fdw for some other cases, I prefer not to mix with dblink.

Regards,

Patrick Fiche

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, October 18, 2019 1:55 PM
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Patrick FICHE <Patrick.Fiche@aqsacom.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Execute a function through fdw

Guillaume Lelarge <guillaume@lelarge.info> writes:

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE
<Patrick.Fiche@aqsacom.com> a écrit :

Is it possible to execute a function located on a server accessed
through Postgres fdw.

It's probably easier to create a view on the remote server, and access
it as a foreign table on the local server.

Yeah. Or if you really want to call a remote function by name, see dblink. postgres_fdw actively avoids doing that sort of thing.

regards, tom lane

Le ven. 18 oct. 2019 à 17:53, Patrick FICHE <Patrick.Fiche@aqsacom.com> a
écrit :

Hi,

I got one more issue after I created my view.

I created it on my Server 1 but I am unable to view it on the Server 2.
I can see all tables through fdw after IMPORT FOREIGN SCHEMA.

I was able to get access to my view only after recreating the SERVER /
USER MAPPING on Server 2.

Is it the expected behavior to recreate the FOREIGN SERVER / SCHEMA after
a new table or view has been created ?

No, you don't need to re create the foreign server. How did it not work?

Le ven. 18 oct. 2019 à 17:53, Patrick FICHE <Patrick.Fiche@aqsacom.com<mailto:Patrick.Fiche@aqsacom.com>> a écrit :
Hi,

I got one more issue after I created my view.

I created it on my Server 1 but I am unable to view it on the Server 2.
I can see all tables through fdw after IMPORT FOREIGN SCHEMA.

I was able to get access to my view only after recreating the SERVER / USER MAPPING on Server 2.

Is it the expected behavior to recreate the FOREIGN SERVER / SCHEMA after a new table or view has been created ?

| No, you don't need to re create the foreign server. How did it not work?

After I created the view on the server 1, I tried to import it in the Foreign Schema on Server 2 using the LIMITED clause.
The command executed successfully but when I tried to query the view on server 2, it returned that table did not exist.

So, I tried to reimport the full schema (after dropping / creating the schema on server 2), I ran the IMPORT FOREIGN SCHEMA without any LIMITED clause. I could see all tables but still not the view.

The only solution that I found in order to get the view accessible on Server 2 was to recreate the SERVER / USER MAPPING and IMPORT FOREIGN SCHEMA.

My Postgres versions on both servers is 11.5.

Regards,

On Fri, Oct 18, 2019 at 7:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Guillaume Lelarge <guillaume@lelarge.info> writes:

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE <Patrick.Fiche@aqsacom.com>

a

écrit :

Is it possible to execute a function located on a server accessed

through

Postgres fdw.

It's probably easier to create a view on the remote server, and access it
as a foreign table on the local server.

Yes, that would probably work here, but if the function takes user-supplied
arguments, that won't work.

Yeah. Or if you really want to call a remote function by name, see
dblink. postgres_fdw actively avoids doing that sort of thing.

And importantly, you can specify the name of the existing postgres_fdw
server to the dblink functions in place of the connection string. This
removes quite a bit of the drudgery of using dblink, if you are already
using postgres_fdw.

Cheers,

Jeff