Re: Referncing a calculated column in a select?

On 2019-09-13 11:49:28 +0900, Kyotaro Horiguchi wrote:
> At Thu, 12 Sep 2019 23:16:01 +0200, "Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> wrote in <20190912211601(dot)GA3842(at)hjp(dot)at>
> > On 2019-09-12 15:35:56 -0500, Ron wrote:
> > > On 9/12/19 2:23 PM, stan wrote:
> > > > I am creating some views, that have columns with fairly complex calculations
> > > > in them. The I want to do further calculations using the result of this
> > > > calculation. Right now, I am just duplicating the first calculation in the
> > > > select fro the 2nd calculated column. There must be a batter way to do
> > > > this, right?
[...]
> > I think he wants to refer to other columns in the view.
> ..
> > What you can do is nest views:
>
> Doesn't subquery work?

Yes, a subquery would work, too.

> Or CTE (WITH clause) might look better.

CTEs (which I quite like in general) are optimisation barriers, so using
them in a view may lead to inefficient plans (depending on what you do
with the view). So, yes, it would work, but you have to check the plans
of your queries (and be prepared to rewrite your view if they aren't
good enough), which is why I didn't suggest it.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>