-----Original Message----- From: Andrew Dunstan [mailto:andrew@xxxxxxxxxxxx] Sent: Wednesday, November 14, 2012 11:08 AM To: David Greco Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: Poor performance using CTE On 11/14/2012 10:56 AM, David Greco wrote: > You're right. I was translating an oracle query , but looks like PG will allow some syntax that is different. Trying to find entries in fedexinvoices where smp_pkg.get_invoice_charges(id) returns a record containing charge_name in ('ADDRESS CORRECTION CHARGE','ADDRESS CORRECTION'). Should return the fedexinvoices row and the row from smp_pkg.get_invoice_charges that contains the address correction. > > > Something like this, though this is syntactically incorrect as smp_pkg.get_invoice_charges returns a set: > > > select fedexinvoices.*, (smp_pkg.get_invoice_charges(id)).* > from fedexinvoices > WHERE > trim(fedexinvoices.trackno)='799159791643' > and > (smp_pkg.get_invoice_charges(id)).charge_name IN ('ADDRESS CORRECTION > CHARGE','ADDRESS CORRECTION') First, please don't top-post when someone has replied underneath your post. It makes the thread totally unreadable. See <http://idallen.com/topposting.html> You could do something like this: WITH invoices as ( select * from fedexinvoices where trim(fedexinvoices.trackno)='799159791643' ), charges as ( SELECT fi2.id, smp_pkg.get_invoice_charges(fi2.id) charge_info from fedexinvoices fi2 join invoices i on i.id = f12.id ) select invoices.* from invoices inner join charges on charges.id = invoices.id AND (charges.charge_info).charge_name IN ('ADDRESS CORRECTION CHARGE','ADDRESS CORRECTION') ; Or probably something way simpler but I just did this fairly quickly and mechanically cheers andrew Thanks, that did the trick. Though I'm still not clear as to why. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
