| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Jaisingkar, Piyush" <Piyush(dot)Jaisingkar(at)nttdata(dot)com> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Passing of where clause to remote table in FDW |
| Date: | 2016-10-13 14:07:20 |
| Message-ID: | 1042.1476367640@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
"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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruce Momjian | 2016-10-13 14:09:34 | Re: pg_upgrade not able to cope with pg_largeobject being in a different tablespace |
| Previous Message | Adrian Klaver | 2016-10-13 13:45:50 | Re: confusion about user paring with pg_hba and pg_ident |