Re: Referencing an expression

On Tue, Aug 19, 2008 at 09:36:49AM -0700, Dispensa, Brad wrote:
> Hello,
>
> First my apologies for a trivial question, I'm still on the new side
> of Postgres coming from many years of access database development.

Welcome to SFPUG. :)

What's UCSF using PostgreSQL for?

> My question is, in an access query you can define a query expression
> and then reference that expression again later in the query:

No. I don't believe that the SQL:2008 specification allows this
either.

> Example
>
> Select 1_quantity, 1_cost,
> [1_quantity]*[cost] as 1_subtotal,
> 2_quantity, 2_cost,
> [2_quantity]*[cost] as 2_subtotal,
> [1_subtotal]+[ 2_subtotal] as total
> From table
>
> In the example above I have a table with purchasing information. I can
> create a subtotal of items using an expression, and then reference those
> newly created expressions to create another expression which is the
> "total".
>
> Using Postgres if I tried the following:
>
> SELECT
> 1_quantity,
> 1_cost,
> 1_quantity*cost as 1_subtotal,
> 2_quantity,
> 2_cost,
> 2_quantity*cost as 2_subtotal,
> 1_subtotal+ 2_subtotal as total
> FROM table
>
> The query will fail stating that it does not know what 1_subtotal or
> 2_subtotal are.

Right.

> I can not quite figure out how this is done in Postgres with out
> using multiple sub quires which makes for long SQL statements. Can
> anyone shed some light on this?

In PostgreSQL 8.4, you will (almost certainly) be able to use common
table expressions like:

WITH t(a,b,c) AS (
SELECT
some_complicated_thing,
another_complicated_thing,
yet_another_even_more_complicated_thing
...
)
SELECT
some_function_of(a),
...

> Many thanks!
>
> Brad Dispensa
>
> -----------------------
> University of California San Francisco
> Center for Cerebrovascular Research
>
> Institute for Human Genetics
> Department of Anesthesia

I think I have a new slogan:

PostgreSQL: painless

;)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate