While using fdw I am trying to get and filtered data from a foreign table. 
Filtering using a regex in where clause as follows:

      FROM XYZ as CAF1
 , CAF1.TOWN_NAME)), '[^a-zA-Z0-9]','') = '84WELLINGTONROADEXETER');

Following is the query plan for the query, please observe the remote SQL 

"Foreign Scan on abc.xyz caf1  (cost=100.00..212.40 rows=13 width=8) (actual 
time=0.607..590.992 rows=2 loops=1)"
"  Output: uprn"
"  Filter: (regexp_replace(upper(concat(caf1.sub_building_name, 
caf1.building_name, caf1.building_number, caf1.street_description, 
caf1.town_name)), '[^a-zA-Z0-9]'::text, ''::text) = 
"  Rows Removed by Filter: 115952"
"  Remote SQL: SELECT uprn, building_name, sub_building_name, building_number, 
street_description, town_name FROM lmn.xyz"
"Total runtime: 591.365 ms"

What I interpret through this is that the regex is not going to the remote 
side, instead whole remote table is being copied onto the foreign table and 
then the regex is applied.

A remote SQL that I wish to have is as follows:

Remote SQL: SELECT uprn, building_name, sub_building_name, building_number, 
street_description, town_name FROM lmn.xyz  where 
 || CAF1.TOWN_NAME), '[^a-zA-Z0-9]','')) = '84WELLINGTONROADEXETER'"

As the functions regexp_replace and upper are inbuild ones there should not be 
any problem passing them onto remote side as mentioned as follows:

"his is done by sending query WHERE clauses to the remote server for execution, 
and by not retrieving table columns that are not needed for the current query. 
To reduce the risk of misexecution of queries, WHERE clauses are not sent to 
the remote server unless they use only built-in data types, operators, and 
functions. Operators and functions in the clauses must be IMMUTABLE as well."

Ref: https://www.postgresql.org/docs/9.3/static/postgres-fdw.html
PostgreSQL: Documentation: 9.3: 
F.31. postgres_fdw. The postgres_fdw module provides the foreign-data wrapper 
postgres_fdw, which can be used to access data stored in external PostgreSQL 

Do we have any option to carry the execution as I want?

Thanks and Regards,

Piyush Jaisingkar

Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.

Reply via email to