-----Original Message-----
From: Andrew Dunstan [mailto:and...@dunslane.net]
Sent: Wednesday, November 14, 2012 11:08 AM
To: David Greco
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
