[GENERAL] Glitch Using a Rule to Copy Data to a Table

[gwlucas]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general