Обсуждение: Execute a function through fdw
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 ?
Regards,
Patrick Fiche
Вложения
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 ?
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 sortof 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 sortof 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 ?
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?
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,
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.