From: André Næss <andre.nass@student.uib.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Conditional rule?
Date: Fri, 28 Jul 2000 13:46:51 +0200
Message-ID: <013001bff889$86051030$74b5f8c2@wkst6> (raw)
References: <87k8e78yg9.fsf@matt.w80.math-hat.com>
<003601bff7d9$18cdd610$74b5f8c2@wkst6>
<00072715035004.09700@comptechnews>
<00072715082905.09700@comptechnews>
Thanks for all the help so far. What I now have is the following structure:
create table b_news (
id serial primary key,
title varchar(60),
time timestamp
);
create table b_news_unpublished (
news_id int references news on delete cascade
);
CREATE FUNCTION b_news_trigproc() RETURNS OPAQUE AS '
BEGIN
IF TG_OP = ''INSERT'' THEN
IF NEW.time NOTNULL THEN
INSERT INTO b_news_unpublished VALUES (NEW.id);
END IF;
END IF;
IF TG_OP = ''UPDATE'' THEN
IF NEW.time NOTNULL AND OLD.time ISNULL THEN
INSERT INTO b_news_unpublished VALUES (NEW.id);
END IF;
IF NEW.time ISNULL AND OLD.time NOTNULL THEN
DELETE FROM b_news_unpublished WHERE news_id=NEW.id;
END IF;
END IF;
RETURN null;
END;
' LANGUAGE 'plpgsql';
create trigger b_news_trigger
after insert or update on b_news
for each row execute procedure b_news_trigproc();
And this works as intended. There are however a few things that worries me.
First of all, I can't seem to find any way to list the trigger and the
function, they seem invisible. This is problematic because my work will be
continued by others, and allthough I will document everything I think it
should be possible to see the triggers and functions somehow...
Secondly, I miss one final idea, when a delete is performed on the
b_news_unpublished table, I would like to set up a rule or procedure that
sets the time value to null in b_news for each row that is affected by the
delete. I understand that the OLD and NEW objects are accessible only during
UPDATE or INSERT operations, so I can't quite see how to do this...
I also find it rather inelegant to use the constraint to handle DELETE
operations on news, whereas UPDATEs and INSERTs are handled by the trigger
procedure. Somehow I would like to either do all the tasks using the trigger
procedure, or using rules.
As for Itai Zukerman's comment: AOL. Good resources around triggers and
rules are very much needed!
Regards
André Næss
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andre.nass@student.uib.no
Subject: Re: Conditional rule?
In-Reply-To: <013001bff889$86051030$74b5f8c2@wkst6>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox