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;

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.