Обсуждение: 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.