[SQL] Updating two table via a Rule?
How do I create a rule for a view that inserts into two tables? I have a view based on two tables. I would like to create insert, update, and delete rules for this view to: - update both tables when the view is updated - delete from both tables when a record is deleted from the view - insert into both table when a record is inserted into the view Here is the view: CREATE VIEW reg_PaymentLines AS SELECT P.MemberID, P.PaymentsID, P.PaymentDate, PL.PaymentLineID, PL.PaymentTypesID, PL.Amount FROM Payments P, PaymentLines PL WHERE P.PaymentsID = PL.PaymentsID; I have tried to create two insert rules on the view as follows: CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines DO INSTEAD INSERT INTO PaymentLines (PaymentsID, PaymentLineID, Amount) VALUES (new.PaymentsID, new.PaymentLineID, new.Amount); CREATE RULE reg_PaymentLines_r2 AS ON INSERT TO reg_PaymentLines DO INSTEAD INSERT INTO Payments (MemberID, PaymentsID, PaymentDate, Amount) VALUES (new.MemberID, new.PaymentsID, new.PaymentDate, new.Amount); PostgreSQL allows me to create the two rules. However, when I insert into the view, I get a foreign key constraint violation because the PaymentID does not exist in the Payments table. There is a foreign key constraint from PaymentLines.PaymentsID to Payments.PaymentsID. It appears that either: - the insert into the PaymentLines table before the insert occurs in the Payments tables - or that the insert into the PaymentLines table is not aware of the insert into the Payments table. - or that the insert to the Payments table is being ignored I get the same error regardless of how the two rules are created (i.e. same error if rule #2 is applied before rule #1). Any suggestions on how to get this to work? Any help is greatly appreciated. Thanks, Michael Davis
[SQL] RE: Updating two table via a Rule?
I just answered my question. For anyone how may be interested, here is the
answer. The following create rule allows multiple actions. This was not
very clear from the documentation. Would some be willing to suggest to the
documentation group to add an example of a rule with multiple actions? I
stumbled onto this syntax in an email on the hacker list after several
hours of research.
CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines
DO INSTEAD (
INSERT INTO Payments (MemberID, PaymentsID, PaymentDate,
Amount)
VALUES (new.MemberID, new.PaymentsID, new.PaymentDate,
new.Amount);
INSERT INTO PaymentLines (MemberID, PaymentsID, PaymentLineID,
Amount)
VALUES (new.MemberID, new.PaymentsID,
new.PaymentLineID, new.Amount);
);
Here is an insert that works:
insert into reg_PaymentLines (MemberID, PaymentsID, PaymentDate,
PaymentLineID, Amount)
VALUES(9, 77,
'1/1/2001', nextval('PaymentLines_s'), 10);
Here is the insert that is failing:
insert into reg_PaymentLines (MemberID, PaymentsID, PaymentDate,
PaymentLineID, Amount)
VALUES(9, nextval('Payments_s'), '1/1/2001',
nextval('PaymentLines_s'), 10);
The Payments_s sequences is bumped on both inserts. As a result, the
insert into the PaymentLines table has a different PaymentsID that the
insert into the Payments table.
Thanks, Michael Davis
-Original Message-
From: Michael Davis [SMTP:[EMAIL PROTECTED]]
Sent: Monday, January 01, 2001 3:20 AM
To: PostgreSQL-SQL
Subject:Updating two table via a Rule?
How do I create a rule for a view that inserts into two tables?
I have a view based on two tables. I would like to create insert, update,
and delete rules for this view to:
- update both tables when the view is updated
- delete from both tables when a record is deleted from the view
- insert into both table when a record is inserted into the view
Here is the view:
CREATE VIEW reg_PaymentLines AS
SELECT P.MemberID, P.PaymentsID, P.PaymentDate,
PL.PaymentLineID, PL.PaymentTypesID, PL.Amount
FROM Payments P, PaymentLines PL
WHERE P.PaymentsID = PL.PaymentsID;
I have tried to create two insert rules on the view as follows:
CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines
DO INSTEAD
INSERT INTO PaymentLines (PaymentsID, PaymentLineID, Amount)
VALUES (new.PaymentsID, new.PaymentLineID,
new.Amount);
CREATE RULE reg_PaymentLines_r2 AS ON INSERT TO reg_PaymentLines
DO INSTEAD
INSERT INTO Payments (MemberID, PaymentsID, PaymentDate,
Amount)
VALUES (new.MemberID, new.PaymentsID, new.PaymentDate,
new.Amount);
PostgreSQL allows me to create the two rules. However, when I insert into
the view, I get a foreign key constraint violation because the PaymentID
does not exist in the Payments table. There is a foreign key constraint
from PaymentLines.PaymentsID to Payments.PaymentsID. It appears that
either:
- the insert into the PaymentLines table before the insert occurs in the
Payments tables
- or that the insert into the PaymentLines table is not aware of the insert
into the Payments table.
- or that the insert to the Payments table is being ignored
I get the same error regardless of how the two rules are created (i.e. same
error if rule #2 is applied before rule #1).
Any suggestions on how to get this to work? Any help is greatly
appreciated.
Thanks, Michael Davis
[SQL] resetting serials and sequences
Hi,
#create temp table a (
id serial primary key,
name text not null);
#insert into a (name) values ('Tom');
#insert into a (name) values ('Fer');
#insert into a (name) values ('Mario');
#select * from a;
id | name
+---
1 | Tom
2 | Fer
3 | Mario
(3 rows)
OK. Now for some reason I need to reset everything without drop tables:
#delete from a;
#select setval ('a_id_seq', 1);
vacuum;
And now reinsert items:
#insert into a (name) values ('Tom');
#insert into a (name) values ('Fer');
#insert into a (name) values ('Mario');
#select * from a;
id | name
+---
2 | Tom
3 | Fer
4 | Mario
(3 rows)
We have missed the id "1"!!!
Otherway:
#select setval('a_id_seq', 0);
ERROR: a_id_seq.setval: value 0 is of of bounds (1,2147483647)
Is this a bug?
Best wishes for the brand new year \fer
Re: [SQL] Updating two table via a Rule?
Try doing the two inserts in one rule: CREATE RULE ... DO INSTEAD ( INSERT INTO ... ; INSERT INTO ... ; ); This is the only way of controlling the order in which the actions will be done; separate rules will be applied in an undefined order. regards, tom lane
Re: [SQL] resetting serials and sequences
Ferruccio Zamuner <[EMAIL PROTECTED]> writes: > Is this a bug? No, but it is a missing feature: there's no way to reset a sequence's is_called flag once it's been set. This is fixed for 7.1: there's now a three-parameter variant of setval() to let you do exactly that. regards, tom lane
[SQL] Removing a constraint?
Does anyone know how to completely and accurately remove or drop a constraint, specifically a foreign key constraint? I tried to remove a constraint by deleting it's trigger from pg_triggers. This caused some undesirable side effects with other tables involved with the constraint. I have several tables that I need to change the column constraints and foreign key constraints on. Recreating (drop and create) the table every time I need to change a column constraint is a pain because all the objects that reference the table would also need to be recreated (i.e. views and triggers). How do production DBAs successfully make changes to their tables? FYI, I was able to alter table add the same constraint many times. Is this a problem? This created a new trigger in pg_triggers every time.
[SQL] AUTOINCREMENT--help
Is there a function in SQL that does autoincrementing... eg. CREATE TABLE (name varchar(40),num_id int); wherein num_id starts with 0 and then when ever I INSERT another record num_id goes 1 etc Thanks in advance
[ADMIN] Re: [SQL] Removing a constraint?
It should work if you remove all three triggers for the constraint using drop trigger, don't delete rows from pg_trigger unless you go through and manually change the row in pg_class for the relation the trigger is for. On Mon, 1 Jan 2001, Michael Davis wrote: > Does anyone know how to completely and accurately remove or drop a > constraint, specifically a foreign key constraint? I tried to remove a > constraint by deleting it's trigger from pg_triggers. This caused some > undesirable side effects with other tables involved with the constraint. I > have several tables that I need to change the column constraints and > foreign key constraints on. Recreating (drop and create) the table every > time I need to change a column constraint is a pain because all the objects > that reference the table would also need to be recreated (i.e. views and > triggers). How do production DBAs successfully make changes to their > tables? > > FYI, I was able to alter table add the same constraint many times. Is this > a problem? This created a new trigger in pg_triggers every time.
Re: [SQL] AUTOINCREMENT--help
On Tue, 2 Jan 2001, Macky wrote: > Is there a function in SQL that does autoincrementing... http://www.postgresql.org/docs/faq-english.html#4.16.1 and http://www.postgresql.org/users-lounge/docs/7.0/postgres/sql-createsequence.htm will tell you how to do this. -- Brett http://www.chapelperilous.net/~bmccoy/ --- A thing is not necessarily true because a man dies for it. -- Oscar Wilde, "The Portrait of Mr. W.H."
[SQL] date infinity
Hi, I've not found, as reported into postgresql docs, 'infinity': create temp table subscriptions ( id int references people, expire date default 'infinity'); ERROR: Unrecognized date external representation 'infinity' Is there someone that knows the new costant name? >From PostgreSQL docs: http://localhost/pg7.0.3/postgres/datatype1134.htm Table 3-14. Postgres Special Date/Time Constants infinityLater than other valid times now and today costants are working. Bye,\fer
