[postgis-users] Update on column
Mike Toews
mwtoews at sfu.ca
Thu Jul 16 15:32:08 PDT 2009
Hi Bob,
You just need to write the rules to understand if the column has been
updated. For example, here is a bit of my trigger function to update
'geom_modifeid_by' and 'geom_modified_on' columns only if the 'geometry'
column has been modified:
CREATE OR REPLACE FUNCTION update_geom_tracking()
RETURNS trigger AS
$BODY$BEGIN
IF TG_OP='UPDATE' THEN
IF OLD.geometry ISNULL ~| NEW.geometry ISNULL) OR (OLD.geometry <>
NEW.geometry) THEN
NEW.geom_modified_by := CURRENT_USER;
NEW.geom_modified_on := CURRENT_TIMESTAMP;
END IF;
END IF;
RETURN NEW;
END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;
-- Oh, and here is my XOR function/operator:
CREATE OR REPLACE FUNCTION xor(boolean, boolean)
RETURNS boolean AS
'SELECT ($1 AND NOT $2) OR (NOT $1 AND $2)'
LANGUAGE 'sql' IMMUTABLE STRICT
COST 10;
CREATE OPERATOR ~|(
PROCEDURE = xor,
LEFTARG = bool,
RIGHTARG = bool);
COMMENT ON OPERATOR ~|(bool, bool) IS 'XOR';
I'm not sure where development is at for column-specific triggers. (I
know permissions were just implemented in 8.4).
-Mike
Bob Pawley wrote:
> I have read a while back that Postgresql doesn't support a trigger of
> an update on a specific column.
>
> If this is still the case, is there a work around??
>
> Bob
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list