Now that pgmemcache is getting more use, I've heard a couple of groans
regarding the need to have two functions with exactly the same code
body. This is necessary because there is no generic way of handling
NEW/OLD. For example:
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