[postgis-users] Update on column

[Mike Toews]

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
>