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)