Re: Passing of where clause to remote table in FDW

Thanks for the suggestion, Used || , actually I was reluctant to use this because my columns could have contained null values. But that I have handled using COALESCE.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, October 13, 2016 7:37 PM
To: Jaisingkar, Piyush
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Passing of where clause to remote table in FDW

"Jaisingkar, Piyush" <Piyush(dot)Jaisingkar(at)nttdata(dot)com> writes:
> While using fdw I am trying to get and filtered data from a foreign table. Filtering using a regex in where clause as follows:

> EXPLAIN ANALYZE VERBOSE SELECT CAF1.uprn
> FROM XYZ as CAF1
> WHERE
>
> (regexp_replace(upper(concat(CAF1.SUB_BUILDING_NAME,CAF1.BUILDING_NAME
> ,CAF1.BUILDING_NUMBER,CAF1.STREET_DESCRIPTION , CAF1.TOWN_NAME)),
> '[^a-zA-Z0-9]','') = '84WELLINGTONROADEXETER');

concat() is not immutable (it's only stable) so it can't be sent for remote execution. Use the || operator instead.

regards, tom lane

______________________________________________________________________
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.