Message-ID: <013001bff889$86051030$74b5f8c2@wkst6>
From: "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre.nass@student.uib.no>
Cc: <pgsql-sql@postgresql.org>
References: <87k8e78yg9.fsf@matt.w80.math-hat.com>
	<003601bff7d9$18cdd610$74b5f8c2@wkst6>
	<00072715035004.09700@comptechnews>
	<00072715082905.09700@comptechnews>
Subject: Re: Conditional rule?
Date: Fri, 28 Jul 2000 13:46:51 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

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