From: Lane Van Ingen <lvaningen@esncc.com>
To: Michael Burke <michael@engtech.ca>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Database with "override" tables
Date: Mon, 5 Dec 2005 17:37:00 -0500
Message-ID: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.com> (raw)
In-Reply-To: <43948FD2.3030904@engtech.ca>
I think I have a similar situation involving the naming of assets, where
the usual asset description is used, but users can enter a description in
a separate table which 'overrides' the original name with a name that is
more familiar to the individual.
IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two
select statements, like this:
select <override values> from foo1
union
select <normal values> from foo2
where <record not in foo1>;
Hope this helps.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael Burke
Sent: Monday, December 05, 2005 2:07 PM
To: PGSQL-SQL
Subject: [SQL] Database with "override" tables
Hello,
I am in a situation where I have various tables (including data such as
a product list) that are read-only to me. I wish to provide the
functionality of changing this table: Removing items, modifying items,
creating new ones. My original idea is to use a second table that is
formatted similarly to the first, read-only table.
However, I can't just duplicate all the data and work in a separate
table, as the original table may be modified by an outside source at any
time. Generally, I prefer to use the updated data from the read-only
table, unless there has been reason to modify it in the past -- in which
case the update may be safely ignored, and I would continue to use the
second table.
The most effective set up I have come up with thus far is as follows:
CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT);
CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id
INT, desc TEXT);
An entry in override_table with an initial_id matching an initial_id in
initial_table would take precedence over the entry in initial_table. A
fairly simple SELECT statement can return the data I require:
SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN
override_table ot ON it.initial_id = ot.initial_id;
So far so good, but then when I need to reference data in those two
tables from somewhere else, I need to reference both initial_id and
override_id on the result set from that table. I also end up using
COALESCE an uncomfortable amount (though, if this is required I will do
so). I would prefer to treat the initial + override tables as a single
SELECT set, but cannot come up with a way to do this cleanly, especially
with regards to the IDs.
I am willing to abandon this format of "overriding" the initial table if
it is way off-course, provided that the initial data be considered
read-only and update-able in the absence of "override" data.
All suggestions are greatly appreciated!
Thanks in advance,
Mike.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lvaningen@esncc.com, michael@engtech.ca
Subject: Re: Database with "override" tables
In-Reply-To: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.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