function.sql

CREATE FUNCTION setentryprices ()
RETURNS OPAQUE AS '
	DECLARE
		temp INT4;
	BEGIN
		IF TG_OP=''INSERT'' THEN
			temp:=count(*) FROM item where item_entry_id=NEW.item_entry_id AND item_live=''t'';
			IF (temp>0) THEN
				UPDATE entry_stats 
				SET entry_maxprice=MAX(item_price),
				    entry_minprice=min(item_price),
				    entry_itemcount=count(item_price)
				FROM item 
				WHERE entry_stats.entry_id=item_entry_id AND item_entry_id=NEW.item_entry_id AND item_live=''t'';
			ELSE
				UPDATE entry_stats
				SET entry_maxprice=0,
				    entry_minprice=0,
				    entry_itemcount=0
				WHERE entry_stats.entry_id=NEW.item_entry_id;
			END IF;
		END IF;

		IF TG_OP=''DELETE'' THEN	
			temp:=count(*) FROM item where item_entry_id=OLD.item_entry_id AND item_live=''t'';
			IF (temp>0) THEN
				UPDATE entry_stats 
				SET entry_maxprice=MAX(item_price),
				    entry_minprice=min(item_price),
				    entry_itemcount=count(item_price)
				FROM item 
				WHERE entry_stats.entry_id=item_entry_id AND item_entry_id=OLD.item_entry_id AND item_live=''t'';
			ELSE
				SET entry_maxprice=0,
				    entry_minprice=0,
				    entry_itemcount=0
				WHERE entry_stats.entry_id=OLD.item_entry_id;
			END IF;
		END IF;

		IF TG_OP=''UPDATE'' THEN
			temp:=count(*) FROM item where item_entry_id=NEW.item_entry_id AND item_live=''t'';
			IF (temp>0) THEN
				UPDATE entry_stats 
				SET entry_maxprice=MAX(item_price),
				    entry_minprice=min(item_price),
				    entry_itemcount=count(item_price)
				FROM item where entry_stats.entry_id=item_entry_id AND item_entry_id=NEW.item_entry_id AND item_live=''t'';
			ELSE
				UPDATE entry_stats
				SET entry_maxprice=0,
				    entry_minprice=0,
				    entry_itemcount=0
				WHERE entry_stats.entry_id=NEW.item_entry_id;
			END IF;

			IF NEW.item_entry_id != OLD.item_entry_id THEN
				temp:=count(*) FROM item where item_entry_id=OLD.item_entry_id AND item_live=''t'';
				IF (temp>0) THEN
					UPDATE entry_stats 
					SET entry_maxprice=MAX(item_price),
					    entry_minprice=min(item_price),
					    entry_itemcount=count(item_price)
					FROM item 
					WHERE entry_stats.entry_id=item_entry_id AND item_entry_id=OLD.item_entry_id AND item_live=''t'';
				ELSE
					SET entry_maxprice=0,
					    entry_minprice=0,
					    entry_itemcount=0
					WHERE entry_stats.entry_id=OLD.item_entry_id;
				END IF;
			END IF;
		END IF; 
		RETURN NULL;
	END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER updateentryprices AFTER INSERT OR DELETE OR UPDATE ON item
FOR EACH ROW EXECUTE PROCEDURE setentryprices();