I have a glitch using a rule to copy data to a table. I was wondering if
anyone could clarify why the following doesn?t work and suggest to me an
alternate way of accomplishing my objective?
I have a tables called (for sake of the example) ?bravo? and ?charlie?, and
I want to use a rule to automatically copy data from bravo to charlie when a
new record is inserted into bravo. I declare a rule and all seems to work
fine until I try to impose some restrictions on what goes into bravo?
In the example that follows, the table ?alpha? is a source of data... It
contains only one attribute which is called ?name?. I want to insert
records into bravo using a select from alpha? with the further restriction
that if I?ve already got a record in bravo with that name, it doesn?t get
added to the table a second time. This all sounds kind of arbitrary, but it
is based on a real-world application for which this is a reasonable
restriction. The crux of the problem isn?t the selection, but the copy
operation.
So, in the example below, I perform two queries, the first inserts data into
bravo with a successful copy to charlie. The second inserts data into
bravo, but does not copy it.
CREATE TABLE alpha (name VARCHAR(32));
CREATE TABLE bravo (name VARCHAR(32), flavor VARCHAR(32));
CREATE TABLE charlie (name VARCHAR(32), flavor VARCHAR(32));
INSERT INTO alpha(name) VALUES('Liz' );
INSERT INTO alpha(name) VALUES('Jay');
INSERT INTO alpha(name) VALUES('Bill');
CREATE rule charlie_copy_rule AS
ON INSERT TO bravo DO
INSERT INTO charlie VALUES(NEW.name, NEW.flavor);
--- a simple insert into bravo using the rule to make
--- a copy into charlie. This works fine
INSERT INTO bravo(name, flavor)
(SELECT name, 'Chocolate' FROM alpha WHERE NAME='Liz');
--- now insert into bravo only those entries that do not
--- already exist. This isn's so fine
INSERT INTO bravo (SELECT name , 'Vanilla' FROM alpha
WHERE NOT EXISTS(SELECT 1 FROM bravo where bravo.name=alpha.name));
SELECT * FROM bravo;
SELECT * FROM charlie;
The results from bravo are just what I'd expect
name | flavor
------+-----------
Liz | Chocolate
Jay | Vanilla
Bill | Vanilla
(3 rows)
But the results from charlie don't include the results from the second
insert.
name | flavor
------+-----------
Liz | Chocolate
(1 row)
So I am left wondering why the records from the second query didn?t make it
into the table named charlie. Any thoughts?
Thanks in advance for your help.
Gary
----------------------------------------------------------------------------
Computer Programming is the Art of the Possible
Gary Lucas, Software Engineer
Sonalysts, Inc
215 Parkway North
Waterford, CT 06385
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
