From: | Matthew Wilson <matt(at)tplus1(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to refer to computed columns from other computed columns? |
Date: | 2010-08-16 12:45:38 |
Message-ID: | i4bbti$q0q$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm converting some procedural code to SQL as an experiment. Here's the
pseudocode:
c = a - b
if c < 0 then d = 'no'
else d = 'yes'
In SQL, I've got this:
select a, b, a - b as c,
case when a - b < 0 then 'no'
else 'yes'
end as d
from foo;
This is a trivial example, but you can see how I calculate a - b two
separate times.
In reality, I have much nastier calculations and they happen more than
just twice.
I'm looking for an elegant solution for this puzzle. I don't want to
repeat that a - b part over and over because I likely will need to
change how c gets defined and I don't want to have to change more than
one place in the code.
All I can come up with so far is to use a view and then another view on
top of that one:
create view v1 as
select a, b, a - b as c
from foo;
create view v2 as
select a, b, c,
case when c < 0 then 'no'
else 'yes'
end as d
from v1;
This is better than the first solution because c is only defined in a
single place. Is this the best possible solution?
Thanks for the help.
Matt
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-08-16 14:26:36 | Re: How to refer to computed columns from other computed columns? |
Previous Message | Adrian von Bidder | 2010-08-16 11:26:53 | Re: good exception handling archiecutre |