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.
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.