Re: Using expression names in subsequent calculation - Mailing list pgsql-novice

On 12 September 2011 02:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tony Theodore <tony.theodore@gmail.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