plpgsql lacks generic identifier for record in triggers... - Mailing list pgsql-hackers

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