Re: Expression on an Expression alias - Mailing list pgsql-novice

"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