Updating PostgreSQL geometry column using SQL (Trgger)

Question

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;

Answer 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.