Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL

Question

I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'?

It is an update trigger on a table which contains point geometries.

It has fields called 'easting' and 'northing'. These fields need to converted from integer to double precision due to this error code on saving of the table.

Provider errors:

PostGIS error while adding features: ERROR: invalid input syntax for integer: "387733.568253859"
CONTEXT: PL/pgSQL function "tf_xy_27700" line 5 at assignment'*

So I have converted them to double precision. I now need to get these fields to update with the x/y on update.

See below for my code so far:

CREATE OR REPLACE FUNCTION tf_xy_27700()
 RETURNS trigger AS
$BODY$
 BEGIN
 IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
 new.easting := ST_X(new.wkb_geometry);
 RETURN NEW;
 END IF;
 END;
$BODY$
LANGUAGE plpgsql;

• user1822: Yes, that's possible - but you need to provide more details. At least the create table statement for your table.
• daniel franklin: it is an update trigger on a table which contains point geometries. it has fields called 'easting' and 'northing'.
• Erwin Brandstetter: The explanation is rather confusing. Not going to touch this question without table definition and Postgres version. Also, the whole idea seems flawed. Storing functionally dependent values (redundantly) is a bad idea in most cases.

Answer 1

its okay, and sorry for the bad explanation.

i got it to work as shown below:

CREATE OR REPLACE FUNCTION tf_xy_point()
 RETURNS trigger AS
$BODY$BEGIN
IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
 new.easting := ST_X(new.wkb_geometry);
 new.northing := ST_Y(new.wkb_geometry);
RETURN NEW;
END IF;
END;$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION tf_xy_point() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO public;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO postgres;

This now allows for the trigger to used as generic for any other table wishing to find the eastings and northings on UPDATE.

• Erwin Brandstetter: GRANT does not make much sense without REVOKE first, because per the manual: execute privilege is granted to PUBLIC for newly created functions. Just remove the GRANT statements. Related: dba.stackexchange.com/a/46834/3684