Update of two tables in a trigger - Mailing list pgsql-sql
From | Christian Ullrich |
---|---|
Subject | Update of two tables in a trigger |
Date | |
Msg-id | Pine.LNX.4.05.9905242030010.7829-100000@christian.ullrich.net Whole thread Raw |
List | pgsql-sql |
Hello world, I am currently playing with postgres to learn a bit about it. I created a database to contain information about manpages i printed. (I am going to print a lot of them, and I don't want to do it twice). For some reason, I split the output in two tables, one to contain the command and the section number, one for the number of pages and sheets of paper, and the date I printed it. I connected these two tables by creating a sequence, using the nextval() as default value for a field in the first table and the currval() as default in the second. I also created a view (named v_manpages) that joins these two tables together, based on the mentioned fields. After failing to get it to work with rules, I am now trying to use triggers and PL/pgSQL-functions for updating the view, but I am experiencing rather strange things. The inserting of rows works fine, but deleting... Here's my problem: manpage=> select * from v_manpages; cmd |section|pages|sheets| when ----------+-------+-----+------+---------- procmailsc|5 | 5| 3|1999-05-24 sox |1 | 11| 6|1999-05-24 chgrp |1 | 1| 1|1999-05-24 procmailrc|5 | 14| 7|1999-05-24 manpage=> insert into v_manpages values ('update','sql',1,1); INSERT 20036 1 manpage=> insert into v_manpages values ('test'1,2,1); INSERT 20039 1 manpage=> select * from v_Manpages; cmd |section|pages|sheets| when ----------+-------+-----+------+---------- procmailsc|5 | 5| 3|1999-05-24 sox |1 | 11| 6|1999-05-24 chgrp |1 | 1| 1|1999-05-24 procmailrc|5 | 14| 7|1999-05-24 update |sql | 1| 1|1999-05-24 test |1 | 2| 1|1999-05-24 manpage=> delete from v_manpages where cmd = 'sox'; DELETE 1 manpage=> select * from v_Manpages; cmd |section|pages|sheets| when ----------+-------+-----+------+---------- procmailsc|5 | 5| 3|1999-05-24 sox |1 | 11| 6|1999-05-24 chgrp |1 | 1| 1|1999-05-24 procmailrc|5 | 14| 7|1999-05-24 Two are gone, sox is still present. manpage=> delete from v_manpages where cmd = 'sox'; DELETE 0 Now I can't delete anything more. These are my tables, triggers and functions: -------------- create table manpages (cmd text, section varchar(5), prnr int4 unique default nextval('seq_printnr')); create table info (prnr int4 unique default currval('seq_printnr'), pages int2, sheets int2, when date default current_date); create view v_manpages as select cmd,section,pages,sheets,when from manpages,info where manpages.prnr = info.prnr); create function get_prnrfromcmd(text) returns int4 as ' select prnr from manpages where cmd = $1; ' language 'sql'; create function tp_v_manpages () returns opaque as ' declareprintnr int4; beginif TG_OP = ''DELETE'' then printnr = get_prnrfromcmd(OLD.cmd); if printnr ISNULL then return NULL; endif; delete from manpages where prnr = printnr; delete from info where prnr = printnr; return OLD;end if;ifTG_OP = ''INSERT'' then insert into manpages values (NEW.cmd, NEW.section); printnr := get_prnrfromcmd(NEW.cmd); if printnr ISNULL then return NULL; end if; insert into info values(printnr, NEW.pages, NEW.sheets); return NEW;end if;return NULL; end; ' language 'plpgsql'; create trigger t_v_manpages before insert or delete on v_manpages for each row execute procedure tp_v_manpages(); ------------ PostgreSQL version is 6.4.2. What mistake did I make? (If this mail is too long, or if there is information missing, or if I am a complete fool, I'm very sorry. But please help me.) -- Christian Ullrich (I am a student, I live in Germany)