1

I'm looking to inderstand the role of date_part('epoch'::text, now()) WHERE id=NEW.id; in the trigger function below.

CREATE OR REPLACE FUNCTION fn_test_table_geo_update_event() RETURNS trigger 
AS $fn_test_table_geo_update_event$
  BEGIN  
    UPDATE test_table SET 
    geom = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326),
    updated_ts = date_part('epoch'::text, now()) WHERE id=NEW.id;

    RAISE NOTICE 'UPDATING geo data for %, [%,%]' , NEW.id, NEW.latitude, NEW.longitude;    
    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$fn_test_table_geo_update_event$ LANGUAGE plpgsql;
1

It would be clearer to write it on distinct lines:

 UPDATE test_table 
 SET 
    geom = ST_SetSRID(ST_MakePoint(NEW.longitude,NEW.latitude), 4326),
    updated_ts = date_part('epoch'::text, now()) 
 WHERE id=NEW.id;

So, the query updates the table test_table, the columns geom and updated_ts, for the record specified by the id.

The updated_ts is updated with the epoch of now (current date/time), which is "the number of seconds since 1970-01-01 00:00:00".

Since it is a trigger, the function has access to the NEW record, which is the values that have been updated (i.e. the new values), so the updated record is the one having the new ID. Let's note that the updated table may or may not be the same table as the one that called the trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.