pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (10+ messages)  latest in thread

Message-ID: <38728CE2.C1756D3E@consumercontact.com>
Permalink:  ../38728CE2.C1756D3E@consumercontact.com/
Also on:    postgresql.org/message-id/38728CE2.C1756D3E@consumercontact.com

 · 

reply

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