Hi,
in the course of my investigation on how to agglomerate or concat several
tables using a view or functions the following little HOW-TO felt out. It is
kind of a full fledged example of how to coalesce two tables using a view.
However it is as it is and I am more less new to writing rules and thus
making faults. As the PG-doc says: There's a mega example but not some
simple so I had a try in making something simple.
So short story long: I want sou to have a look at my SQL-style example and
either learn from it and/or give comments and suggestions (what didn't I
see, since I am beginner and what hidden issues exist, what did I interpret
wrong)
textfile
----------------8<--------------------------------
HOW-TO concatinate two tables in Postgres using a view and rules
This is a full-example of how to agglomerate two tables, connected via a 1:1
relation into one view on which DELETE, UPDATE and INSERT can be used.
So our goal is to have two tables like id|a|b and id|x|y coalesced into
one
table or view id|a|b|x|y which by itself allows INSERT, UPDATE and DELETE.
-- We start with creation of the tables
-- In order to connect them afterwards, we need a primary key and some
columns:
CREATE TABLE tbla
(
id int4 NOT NULL,
a int4,
b varchar(12),
CONSTRAINT tbla_pk PRIMARY KEY (id)
)
WITHOUT OIDS;
-- One table is kind of master table, whilst the other(s) are/is
-- slave table. This just means that the master table defines what
-- (new) values are valid for the primary key and thus for the
-- foreign keys of the slave tables.
-- So a second table will at least have the same key as the master table.
-- On the one hand as foreign key to allow just values in tbla and to
-- retain referential integrity and on the other hand as primary key to
-- keep values unique and thus make a 1:n relation become a 1:1 relation:
CREATE TABLE tblb
(
id int4 NOT NULL,
x bool,
y timestamp,
CONSTRAINT tblb_pk PRIMARY KEY (id),
CONSTRAINT tblb_fk FOREIGN KEY (id) REFERENCES tbla (id) ON UPDATE CASCADE
ON DELETE CASCADE
)
WITHOUT OIDS;
-- Note that we specified ON DELETE CASCADE. This will get us handy, later
-- Now let's already test our new tables plus their constraints by inserting
some records:
INSERT INTO tbla VALUES ( 3, 9034, 'F dabiu' );
INSERT INTO tbla VALUES ( 6, -23, 'Moosi llap' );
-- we have to use same keys and qty 0..1
INSERT INTO tblb VALUES ( 3, false, now() );
INSERT INTO tblb VALUES ( 6, true, now() );
-- Now we can create such a agglomerating view. However it will just allow
-- data to be viewed - maybe thus the name.
CREATE OR REPLACE VIEW a_and_b AS
SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y
FROM tbla
NATURAL LEFT OUTER JOIN tblb;
-- This creates a new 'table' (internally Postgres views are tables with no
data
-- but a bunch of rules) with all the columns we expected earlier.
-- Note that since we used the name id in both tables, we could use a
NATURAL
-- JOIN. Depending on your column names and intentions, other joins are
required.
-- As you can see, this resembles just the concatenated table we had in
mind:
SELECT * FROM a_and_b;
-- In order to make INSERT, UPDATE and DELETE work o the view, the next step
-- is to define some rules. Note that there already exists one rule: The
-- 'standard-rule' named _RETURN. This makes a table to a view in Postgres.
-- Since the SELECT is covered by a rule, no actual data is required.
-- Let's start with a rule for inserting records:
CREATE OR REPLACE RULE a_b_insert AS
ON INSERT TO a_and_b DO INSTEAD (
INSERT INTO tbla (id, a, b) VALUES (new.id, new.a, new.b);
INSERT INTO tblb (id, x, y) VALUES (new.id, new.x, new.y);
);
-- What we do here is: Instead of issuing the users insert, we issue two of
-- our own inserts. Thereby reusing values we got from the users insert.
-- This is the pseudo relation NEW. NEW has the same structure as the view
-- the rule is written for. The two INSERTs (or whatever other
SQL-statements)
-- are coalesced by putting them into parentheses. Note that coming from our
-- definition, we have to fill tbla before tblb.
-- So it's now possible to insert records via tha view:
INSERT INTO a_and_b VALUES (99, 123, 'text', false, now() );
SELECT * FROM a_and_b WHERE id=99;
-- In order to be able to also delete records, a delete-rule is needed
-- This is also a INSTEAD-rule, since it is intended to be a rather
-- generic rule (any DELETE-WHERE-clause shall be possible), there are
-- no restricting expressions and it is a INSTEAD but not a ALSO-rule.
-- So in this case we want to delete records from just tbla. Since we
enabled
-- DELETE CASCADE Postgres will clean all dependent records in tblb as well.
-- One could think that the WHERE clause of the view's DELETE will be
applied
-- to this rule as well, but that's not true. a 'DELETE FROM tbla' ends up
-- in purging all records. So to restrict the rule's DELETE we can make use
-- of the pseudo relation OLD. This relation has the same form as the view
-- and delivers us the terms given in a WHERE-clause. That leads us to:
CREATE OR REPLACE RULE a_and_b_del AS
ON DELETE TO a_and_b DO INSTEAD
DELETE FROM tbla WHERE tbla.id = OLD.id;
-- which now allows us to safely delete selected records:
DELETE FROM a_and_b WHERE id=99;
SELECT * FROM a_and_b WHERE id=99;
-- Last but not least there's still the UPDATE to the view. A rule is
required,
-- too. It works almost the same pattern as before. Since a update is like a
-- combination of DELETE and INSERT, there exist two pseudo relations: OLD
and NEW.
-- One can refer to the unchanged record, to terms of the current statement
and
-- to the user's new values (he wants to set). In our case, we do only need
the
-- NEW-relation. As at the INSERT rule before, we coalesce several
SQL-statements
-- by using parentheses. This is again a 'default' and INSTEAD-rule. Instead
-- of the big update, we do two updates on each table. Thereby restricting
-- updates again of using just the primary key:
CREATE OR REPLACE RULE a_and_b_upd AS
ON UPDATE TO a_and_b DO INSTEAD
(
UPDATE tbla SET a = new.a, b = new.b WHERE tbla.id = new.id;
UPDATE tblb SET x = new.x, y = new.y WHERE tblb.id = new.id;
);
UPDATE a_and_b SET a=155, b='t1e5xt', x=false, y='2005-6-6' WHERE id=1;
SELECT * FROM a_and_b;
-- Note that this may fail if not all slave records exist. If for example
-- there exists just a master record for id=1
INSERT INTO tbla VALUES ( 1, -12399, 'solo master' );
-- a UPDATE on the view will update or try to update both tables, it will
-- have success however in jus one table and thus the second half columns
-- do not get set:
UPDATE a_and_b SET a=-333, b='neotext', x=false, y='2005-6-6' WHERE id=1;
SELECT * FROM a_and_b;
-- The values for x and y are lost.
SELECT * FROM a_and_b;
-- Maybe in this case it is more recommendable to define the view not as
-- with LEFT OUTER JOIN but with a normal equal join. This is up to you.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
