When adding new data to an existing table, I am trying to populate an existing field with a subset of data from another field. I'm using PGAdmin on postgres version 9 with postGIS extension
To achieve this I'm trying to create a function and trigger. This is what I have but I cant get it to run correctly - I'm getting syntax errors but not sure where. The function is
CREATE or REPLACE FUNCTION update_tp_tag_field()
RETURNS TRIGGER as $$
BEGIN
SET NEW."telco pole tag" = RIGHT((NEW."structure name"), STRPOS(REVERSE(NEW."structure name"),':')-1);
RETURN NEW;
END;
$$ language sql;
and the trigger is:
CREATE TRIGGER populate_tp_tag_field
BEFORE INSERT on data.PIA_Point_Structures
FOR EACH ROW
EXECUTE FUNCTION update_tp_tag_field();
The trigger saved ok, but the function gives a syntax error: ERROR: syntax error at or near "CREATE" LINE 6: AS $BODY$CREATE or REPLACE FUNCTION update_tp_tag_field()
or if added from scratch I get: ERROR: SQL functions cannot return type trigger SQL state: 42P13
I tried a less complex function to try and identify some of the syntax errors:
BEGIN
NEW."telco pole tag" = "structure name";
RETURN NEW;
and although that was accepted, it didnt produce any results in the field at all. Any help getting this to work would be appreciated
Managed to solve it, it turns out there were two issues the 1st was solved by Zegarek in the comments - I needed to change the language to PLPGSQL and that got rid of one error.
The second error is to do with the fact that I have more than one schema so needed to ensure the correct schema is referenced in both the function and trigger:
CREATE or REPLACE FUNCTION data.update_tp_tag_field()
RETURNS TRIGGER as $$
BEGIN
NEW."telco pole tag" = RIGHT(NEW."structure name",
STRPOS(REVERSE(NEW."structure name"),':')-1);
RETURN NEW;
END;
$$ language plpgsql;
and
CREATE TRIGGER populate_tp_tag_field
BEFORE INSERT
ON data.pia_point_structures
FOR EACH ROW
EXECUTE FUNCTION data.update_tp_tag_field();