Using expression names in subsequent calculation

From: Tony Theodore <tony(dot)theodore(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Using expression names in subsequent calculation
Date: 2011-09-11 06:13:50
Message-ID: CAJFv53qhOROQ8ED=QZEGq6cn_Xm86bfZGrUe+SkHssp+QF9=Yg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

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.

In a real query - qty, price and tax_rate would come from actual
columns, but then the error is on the "val" column. I'd like to use
the name of the expression rather than repeat the calculation.

Near the bottom of the SELECT docs, there's a note about namespaces
that explains why this won't work, but I'm wondering how to go about
this type of query.

So far I've come up with:

WITH order_details AS (SELECT 10 AS qty, 0.5 AS price, 0.1 AS tax_rate),
order_values AS (SELECT qty * price AS val from order_details),
order_taxes AS (SELECT val * tax_rate AS tax_amount FROM
order_details, order_values)
SELECT qty, price, tax_rate, val, tax_amount, val + tax_amount as
total FROM order_details, order_values, order_taxes

However, that looks at lot more complicated than simply repeating the
calculations ;)

What's the best way go about calculations like this that build upon
previous results?

Thanks,

Tony

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2011-09-11 16:00:34 Re: Using expression names in subsequent calculation
Previous Message Tim Dunphy 2011-09-09 21:00:01 cant' start psql ipv6 error