New sql user here, and I’m trying to make a change in an existing view implemented in postgresql. The view computes a column, computed_column, that is a function 2 variables (I will refer to it as #1 and #2 in the first snippet) which can be derived from the existing columns, and I’m not sure what the best way to do this is without doing a bunch of casework in the computed_column selection.

The view is something as follows:

create or replace view my_view as
select
  -- some other columns not included here
  (case when t.flag = 'on' THEN (#1) ELSE #2 END) / (#1 - #2) as computed_column
from table1 t1
left join table2 t2 on ....
left join table2 t3 on ....

I tried something like this (note that derived_column1 is #1 above and likewise for #2)

create or replace view my_view as
select
  -- some other columns not included here
  (case when t1.proto = 'p' then t2.c2 else t2.c3) as derived_column1,
  (case when t1.proto = 's' then t2.c4 else t2.c5) as derived_column2,
  (case when t1.flag = 'on' then (derived_column1) ELSE derived_column2 END) / (derived_column1 - derived_column2) as computed_column
from table1 t1
left join table2 t2 on ....
left join table2 t3 on ....

But this doesn’t work because i apparently can’t refer to a column alias defined in the same select clause, so I settled to do a subquery, but it seems to be rather verbose since I have a long list of columns that are being selected, and I have to put that in both the outer select and the inner subquery select. Is there a better way to do this?