Thread: Expression on an Expression alias

I'm wondering if there is a way to write an expression on an
expression alias... or 'build on' an existing expression alias.
I am slowly converting old Filemaker work to PG and the below would be helpful.
My old Filemaker allows something similar to var 2 below.

This is an example ( a little feeble) where FV is a function
the first one works, but can get lengthy if I start to nest variations.

SELECT
fv_test.acct_val AS acct_val,
fv_test.time AS time,
fv_test.i AS interest_rate,
FV(fv_test.acct_val,fv_test.i,fv_test.time) AS FV1,
FV(fv_test.acct_val,fv_test.i,fv_test.time) *2 AS FV2,
FV(fv_test.acct_val,fv_test.i,fv_test.time) *3 AS FV3
FROM    "hrcommu"."fv_test"

The *effect* I'd like is to 'reuse' FV1, ~

SELECT
fv_test.acct_val AS acct_val,
fv_test.time AS time,
fv_test.i AS interest_rate,
FV(fv_test.acct_val,fv_test.i,fv_test.time) AS FV1,
FV1 *2 AS FV2,
FV1 *3 AS FV3
FROM    "hrcommu"."fv_test"

Any suggested reading, would be read.
Thanks

"Mike Ellsworth" <younicycle@gmail.com> writes:
> The *effect* I'd like is to 'reuse' FV1, ~

> SELECT
> fv_test.acct_val AS acct_val,
> fv_test.time AS time,
> fv_test.i AS interest_rate,
> FV(fv_test.acct_val,fv_test.i,fv_test.time) AS FV1,
> FV1 *2 AS FV2,
> FV1 *3 AS FV3
> FROM    "hrcommu"."fv_test"

This is flat out invalid according to the SQL spec.  The SELECT
expressions are notionally computed in parallel and so can't refer
to each other.

You can do something vaguely like what you want with nested
SELECTs:

SELECT
FV1,
FV1 *2 AS FV2,
FV1 *3 AS FV3
FROM
(SELECT
   FV(fv_test.acct_val,fv_test.i,fv_test.time) AS FV1
 FROM    "hrcommu"."fv_test"
) ss;

although how much advantage there is is debatable.
(In particular, this is very likely to still call fv()
three times per row.)

            regards, tom lane

On Fri, Apr 18, 2008 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Mike Ellsworth" <younicycle@gmail.com> writes:
>  > The *effect* I'd like is to 'reuse' FV1, ~
>
>  > SELECT
>  > fv_test.acct_val AS acct_val,
>  > fv_test.time AS time,
>  > fv_test.i AS interest_rate,
>  > FV(fv_test.acct_val,fv_test.i,fv_test.time) AS FV1,
>  > FV1 *2 AS FV2,
>  > FV1 *3 AS FV3
>  > FROM    "hrcommu"."fv_test

< The SELECT
>  expressions are notionally computed in parallel and so can't refer
>  to each other.

Thanks -now I get it.

Wish I didn't have 15,000 of these little quicksand pits to ungnarl.