From: | Christian Ullrich <ChrUllrich(at)gmx(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Update of two tables in a trigger |
Date: | 1999-05-24 18:59:56 |
Message-ID: | Pine.LNX.4.05.9905242030010.7829-100000@christian.ullrich.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 '
declare
printnr int4;
begin
if TG_OP = ''DELETE'' then
printnr = get_prnrfromcmd(OLD.cmd);
if printnr ISNULL then
return NULL;
end if;
delete from manpages where prnr = printnr;
delete from info where prnr = printnr;
return OLD;
end if;
if TG_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)
From | Date | Subject | |
---|---|---|---|
Next Message | Steven M. Wheeler | 1999-05-24 19:04:58 | Re: [SQL] Re: pgsql-sql-digest V1 #225 |
Previous Message | Jackson, DeJuan | 1999-05-24 18:53:17 | RE: [SQL] Problems with refint.so and 6.5 Beta |