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
tofind entries in fedexinvoices where smp_pkg.get_invoice_charges(id) returns a record containing charge_name in
('ADDRESSCORRECTION CHARGE','ADDRESS CORRECTION'). Should return the fedexinvoices row and the row from
smp_pkg.get_invoice_chargesthat 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