Re: plpgsql lacks generic identifier for record in triggers...

> 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:

[snip]  Err... wait, this is a classic case of send first then 
finishing to pondering the gripe.

> 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();

A statement trigger should be used instead since the return value is 
ignored (and NULL can be used to satisfy the need for return to 
actually return something).  When updating dynamic keys, you always 
need to be explicit regarding NEW/OLD to get the data version, but for 
static keys, statement triggers are the way to go.  Ex:

> db=# CREATE FUNCTION schma.tbl_inval() RETURNS TRIGGER AS 'BEGIN
>         EXECUTE public.mc_init();
>         EXECUTE public.mc_delete(''mc_key'');
>         RETURN NULL;
> END;' LANGUAGE 'plpgsql';
> db=# CREATE TRIGGER tbl_inval_trg AFTER INSERT OR UPDATE OR DELETE ON 
> schma.tbl FOR EACH STATEMENT EXECUTE PROCEDURE schma.tbl_inval();

Very nice.  -sc

-- 
Sean Chittenden