From: Rick Delaney <rick@consumercontact.com>
To: pgsql-sql@postgresql.org
Subject: Calculation dependencies in views
Date: Tue, 04 Jan 2000 19:14:26 -0500
Message-ID: <38728CE2.C1756D3E@consumercontact.com> (raw)
I have a table with, say, a dozen fields and I want to end up with a view with
around 50 fields, calculated from the original dozen.
So it would be something like this:
CREATE VIEW final AS
SELECT
x, y, z,
(x + y) as a,
(y + z) as b,
(x + y) * z as c,
(y + z) * x as d,
(x + y) * z + x as e,
(y + z) * x + x as f
FROM my_table;
except my expressions are longer and more complicated. However, my expressions
do have similar dependencies and redundancies.
My question is what is a good way of dealing with this? I was going to do
something like
CREATE VIEW one AS
SELECT
id,
(x + y) as a,
(y + z) as b,
FROM my_table;
CREATE VIEW two AS
SELECT
m.id,
o.a * m.z as c,
o.b * m.x as d,
FROM my_table m, one o
WHERE m.id = o.id;
etc. but I'll end up with a lot of levels and joins going this route which I
expect will be pretty slow.
Can someone recommend anything to me? Should I be approaching this from a
completely different angle i.e other than views?
I'm pretty new at this so any pointers will be appreciated.
--Rick
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: rick@consumercontact.com
Subject: Re: Calculation dependencies in views
In-Reply-To: <38728CE2.C1756D3E@consumercontact.com>
References: <38728CE2.C1756D3E@consumercontact.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox