Search code examples
postgresqltriggers

Syntax error on creating trigger function in PostgreSQL


I use the next script to create trigger function in PostgreSQL:

CREATE OR REPLACE FUNCTION pdate_day_month_decade_trigger()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW."dayMonth" <> OLD."dayMonth" THEN
        DECLARE decadeMonthValue INTEGER := (
            CASE
              WHEN NEW."dayMonth" >= 1 AND NEW."dayMonth" <= 10 THEN 1
              WHEN NEW."dayMonth" >= 11 AND NEW."dayMonth" <= 20 THEN 2
              ELSE 3
            END
         );
         UPDATE main."YDate"
         SET "decadeMonth"=decadeMonthValue
         WHERE id = NEW.id;
    END IF;

    RETURN NEW;
END;
$$

But got an error:

ERROR:  syntax error (at or near: "SET")
LINE 16:    SET "decadeMonth"=decadeMonthValue
            ^
CONTEXT:  wrong type name "main."YDate"
         SET "decadeMonth"" 

ОШИБКА:  syntax error (at or near: "SET")
SQL state: 42601
Character: 394

I tried to change syntax of types (used full names and shortened), but error still persists.


Solution

  • The plpgsql block structure isn't respected. DECLARE should occur before BEGIN. While not needed here, you could nest several blocks into each others, but their structure must still be respected.