From: | Tony Theodore <tony(dot)theodore(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using expression names in subsequent calculation |
Date: | 2011-09-13 07:49:06 |
Message-ID: | CAJFv53pNasHroOkkj4Yj0in60GqOV2gNc98HPUUz6bJwr6EvLA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 12 September 2011 02:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tony Theodore <tony(dot)theodore(at)gmail(dot)com> writes:
>> I'm migrating an application from Access, and having great fun so far,
>> but I'm running into problems with some queries. What I'm trying to do
>> boils down to this:
>
>> SELECT 10 AS qty, 0.5 AS price, 0.1 AS tax_rate, qty * price AS val,
>> val * tax_rate AS tax_amount, val + tax_amount as total
>
>> but get a "column qty does not exist" error.
>
> Yeah, this is entirely contrary to the SQL standard. In the standard,
> the columns of the result are notionally computed in parallel, so there
> is no way for one to refer to another. MS hasn't done the world any
> favors by inventing this incompatible extension.
>
>> What's the best way go about calculations like this that build upon
>> previous results?
>
> What you need to do is use a sub-select to create columns that can be
> referenced at the next level. The particular example you're showing
> here requires multiple levels of sub-select because you're chaining
> the operations. Something like
>
> SELECT *, val + tax_amount as total
> FROM
> (SELECT *, val * tax_rate AS tax_amount
> FROM
> (SELECT *, qty * price AS val
> FROM
> (SELECT 10 AS qty, 0.5 AS price, 0.1 AS tax_rate) as ss1
> ) as ss2
> ) as ss3;
Excellent, thanks for that, I didn't think that was possible and the
WITH statement was the only way to do such things.
> Keep in mind that the Postgres planner will typically flatten
> sub-selects used this way into a single level of plan, with the same
> result as if you hadn't chained the calculations but just expanded all
> the expressions into their primitive constitutents by hand. You can see
> that in this slightly less silly version of your example:
>
> regression=# create table sales (qty int, price numeric, tax_rate numeric);
> CREATE TABLE
> regression=# explain verbose SELECT *, val + tax_amount as total
> FROM
> (SELECT *, val * tax_rate AS tax_amount
> FROM
> (SELECT *, qty * price AS val
> FROM
> sales
> ) as ss2
> ) as ss3;
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on public.sales (cost=0.00..41.12 rows=830 width=68)
> Output: sales.qty, sales.price, sales.tax_rate, ((sales.qty)::numeric * sales.price), (((sales.qty)::numeric * sales.price) * sales.tax_rate), (((sales.qty)::numeric * sales.price) + (((sales.qty)::numeric * sales.price) * sales.tax_rate))
> (2 rows)
>
> In this particular example that's probably just fine, because the
> individual calculations are cheap enough that repeating them probably
> beats incurring the overhead of multiple run-time plan levels.
> But if you were trying to use a structure like this to avoid multiple
> evaluations of a very expensive function, you'd want to stick an
> "OFFSET 0" into the sub-select level that had the expensive function,
> so as to create an optimization fence.
>
> regards, tom lane
>
Okay, I'll do some reading up on that.
Thanks again,
Tony
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2011-09-13 15:12:29 | Re: PQisBusy() always busy |
Previous Message | SamuelStar | 2011-09-13 01:40:42 | Unable to start postgres service (8.4) |