Re: Referencing an expression

[David Fetter <david(at)fetter(dot)org>]

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