db=# CREATE FUNCTION schma.tbl_ins_upd() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
RETURN NEW;
END;' LANGUAGE 'plpgsql';
db=# CREATE FUNCTION schma.tbl_del() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
RETURN OLD;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_ins_upd_trg AFTER INSERT OR UPDATE ON schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_ins_upd();
db=# CREATE TRIGGER tbl_del_trg AFTER DELETE ON schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_del();
It's be nice if there was a generic return type so that one could collapse those two functions and trigger creation statements into one function and one trigger. Something like:
db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
RETURN ROW;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_inval();
pgmemcache has pushed this to the surface as a problem that otherwise wouldn't exist. That said, plpgsql's semantics are clearly the issue here as it's a syntax problem. ROW being an alias for NEW in the INSERT and UPDATE case, and OLD in the DELETE case. Thoughts? Would a patch be accepted that modified plpgsql's behavior to include a new predefined alias? Better yet, could TRIGGER functions be allowed to return nothing (ala VOID)? For example:
db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
RETURN;
END;' LANGUAGE 'plpgsql';
db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON schma.tbl FOR EACH ROW EXECUTE PROCEDURE schma.tbl_inval();
Which would tell the backend to assume that the row wasn't changed and proceed with its handling. This is the preferred approach, IMHO... but I think is the hardest to achieve (I haven't looked to see what'd be involved yet).
Enjoy your T-Day commute if you haven't yet. -sc
-- Sean Chittenden
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
