Creating my first trigger in Postgresql with syntax error near "LANGUAGE"

Question

I need to learn more about postgres but I am trying to write a function that links to a trigger in my postgres db. Normally the functions I write work fine, however, this particular function keeps returning errors around the syntax at the end of my output.

I have a fixtures table with a column called 'Result'. I want to read the scores from the fixture e.g. Wolves 0:0 Ipswich, and trigger into Result 'Draw'.

The query I am writing is as follows:

CREATE OR REPLACE FUNCTION sppullscoretrigger()
RETURNS trigger

SECURITY DEFINER
AS $BODY$

DECLARE
  payload text;
BEGIN
    SELECT NEW.Home_Side_Score, NEW.Away_Side_Score FROM Fixtures;
        IF    NEW.Home_Side_Score is NULL or NEW.Away_Side_Score is NULL THEN NEW.Result = 'TBC';
        IF NEW.Home_Side_Score = NEW.Away_Side_Score THEN NEW.Result = 'Draw';
        IF NEW.Home_Side_Score > NEW.Away_Side_Score THEN NEW.Result = 'Home_Win';
        IF NEW.Home_Side_Score < NEW.Away_Side_Score THEN NEW.Result = 'Away_Win';
        ELSE NEW.Result = 'Error';
        END IF;


    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql

CREATE TRIGGER trgscore
BEFORE INSERT OR UPDATE
ON Fixtures
FOR EACH ROW
EXECUTE PROCEDURE sppullscoretrigger();

I have only written basic functions before but I am really struggling with getting this trigger to work.

You are missing a ; after the trigger function (and before the create trigger). Also: SELECT NEW.Home_Side_Score, NEW.Away_Side_Score FROM Fixtures; will select all rows from the table fixtures but with constant values for each row. You do not need that select at all. In a row level trigger you can just access the new and old records — user330315
– user330315, Commented Apr 3, 2016 at 8:55
Thank you. I have put the missing semi-colon in and that resolved the issue as well was the ELSIF issue identified by Joachim. — LemusThelroy
– LemusThelroy, Commented Apr 3, 2016 at 9:10
I have just tried using this trigger when I insert data and it tells me "Error: record 'New' has no field 'home_side_score' CONTEXT: SQL statement "SELECT New.Home_Side_Score is NULL or New.Away_Sido_Score is NULL"...." Any ideas? I thought the New attribute would hold the data I am importing so there would be a field home_side_score etc. — LemusThelroy
– LemusThelroy, Commented Apr 3, 2016 at 9:19
Yes it will. But to find the cause of the error you need to edit your question and add the complete create table statement for the table. But it's probably better to ask a new question for that problem. — user330315
– user330315, Commented Apr 3, 2016 at 9:21

Joachim Isaksson · Accepted Answer · 2016-04-03 08:49:25Z

1

You seem to be using IF when you mean ELSIF. This version compiles;

CREATE OR REPLACE FUNCTION sppullscoretrigger()
RETURNS trigger

SECURITY DEFINER
AS $BODY$

DECLARE
  payload text;
BEGIN
    SELECT NEW.Home_Side_Score, NEW.Away_Side_Score FROM Fixtures;
        IF    NEW.Home_Side_Score is NULL or NEW.Away_Side_Score is NULL THEN NEW.Result = 'TBC';
        ELSIF NEW.Home_Side_Score = NEW.Away_Side_Score THEN NEW.Result = 'Draw';
        ELSIF NEW.Home_Side_Score > NEW.Away_Side_Score THEN NEW.Result = 'Home_Win';
        ELSIF NEW.Home_Side_Score < NEW.Away_Side_Score THEN NEW.Result = 'Away_Win';
        ELSE NEW.Result = 'Error';
        END IF;

    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql

A quick SQLfiddle to test with.

answered Apr 3, 2016 at 8:49

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LemusThelroy Over a year ago

I have just tried using this trigger when I insert data and it tells me "Error: record 'New' has no field 'home_side_score' CONTEXT: SQL statement "SELECT New.Home_Side_Score is NULL or New.Away_Sido_Score is NULL"...." Any ideas? I thought the New attribute would hold the data I am importing so there would be a field home_side_score etc.

Collectives™ on Stack Overflow

Creating my first trigger in Postgresql with syntax error near "LANGUAGE"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related