ctidnulltest.sql

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
         '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
         HANDLER plpgsql_call_handler
         LANCOMPILER 'PL/pgSQL';


CREATE TABLE entry(
	entry_id INT4 PRIMARY KEY
);

CREATE TABLE entry_stats
(
	entry_id INT4 NOT NULL REFERENCES entry ON DELETE CASCADE,
	entry_minprice INT4 NOT NULL DEFAULT 0
);

CREATE FUNCTION init_entry_stats_func() RETURNS OPAQUE AS '
	DECLARE
	BEGIN
		INSERT INTO entry_stats VALUES (NEW.entry_id,0);
		RETURN NULL;
	END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER init_entry_stats AFTER INSERT ON entry
FOR EACH ROW EXECUTE PROCEDURE init_entry_stats_func(entry_id);

INSERT INTO entry VALUES (1);

CREATE TABLE item(
	item_id INT4 PRIMARY KEY,
	item_entry_id INT4 NOT NULL REFERENCES entry ON DELETE NO ACTION,
	item_price INT4 NOT NULL,
	item_live bool NOT NULL DEFAULT 'n'
);

CREATE FUNCTION setentrystats ()
RETURNS OPAQUE AS '
	DECLARE
	BEGIN
		IF TG_OP=''INSERT'' THEN
			UPDATE entry_stats 
			SET entry_minprice=min(item_price)
			FROM item where item_entry_id=NEW.item_entry_id AND item_live=''t'';
		END IF;
		RETURN NULL;
	END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER updateentryprices AFTER INSERT ON item
FOR EACH ROW EXECUTE PROCEDURE setentrystats();

INSERT INTO item VALUES (1,1,12,'f');
/* Change this last line to "INSERT INTO item VALUES (1,1,12,'t');" and no error is generated */