From: Andrew Dunstan <andrew@dunslane.net>
To: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor performance using CTE
Date: Wed, 14 Nov 2012 11:07:31 -0500
Message-ID: <50A3C1C3.7000809@dunslane.net> (raw)
In-Reply-To: <187F6C10D2931A4386EE8E58E13857F61291D872@BY2PRD0811MB415.namprd08.prod.outlook.com>
References: <187F6C10D2931A4386EE8E58E13857F61291C600@BY2PRD0811MB415.namprd08.prod.outlook.com>
<50A3BDE2.1050201@dunslane.net>
<187F6C10D2931A4386EE8E58E13857F61291D872@BY2PRD0811MB415.namprd08.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, David_Greco@harte-hanks.com
Subject: Re: Poor performance using CTE
In-Reply-To: <50A3C1C3.7000809@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox