Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...
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';
could this be used?
CREATE FUNCTION schma.tbl_ins_upd() RETURNS TRIGGER AS 'BEGIN
EXECUTE public.mc_init();
EXECUTE public.mc_delete(''mc_key'');
if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then
RETURN NEW;
else
RETURN OLD;
end if;
END;' LANGUAGE 'plpgsql';
regards
Laser
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] plpgsql lacks generic identifier for record in triggers...
Sean Chittenden <[EMAIL PROTECTED]> writes: > ... Better yet, could TRIGGER functions be allowed to > return nothing (ala VOID)? > 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). plperl is doing it that way, so I don't see why plpgsql couldn't allow it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] 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:
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
