postgresqlfunctionpgadmin

Postgresql creating a function to update a field with a subset of another


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


Solution

  • 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();