CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
         '/opt/iebusiness/app/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 "nulliszero" (int4)
RETURNS int4 AS
'SELECT 0 WHERE $1 IS NULL
UNION SELECT $1 WHERE $1 IS NOT NULL'
LANGUAGE 'SQL';

CREATE FUNCTION setentrystats ()
RETURNS OPAQUE AS '
	DECLARE
	BEGIN
		IF TG_OP=''INSERT'' THEN
			UPDATE entry_stats 
			SET entry_minprice=nulliszero(min(item_price))
			FROM item where entry_stats.entry_id=item_entry_id AND 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');

