Syntax error in Postgres SQL when creating a trigger for a function I've defined?

Question

Using this SQL:

DROP FUNCTION IF EXISTS notify_new_proposed_game();
CREATE FUNCTION notify_new_proposed_game()
RETURNS void
AS 'NOTIFY proposed_game, ''new'';'
LANGUAGE SQL;

DROP TRIGGER IF EXISTS new_proposed_game ON game;
CREATE TRIGGER new_proposed_game
AFTER INSERT
ON game
EXECUTE FUNCTION notify_new_proposed_game();

I get this error:

DROP FUNCTION
CREATE FUNCTION
psql:schema.sql:27: NOTICE:  trigger "new_proposed_game" for relation "game" does not exist, skipping
DROP TRIGGER
psql:schema.sql:31: ERROR:  syntax error at or near "FUNCTION"
LINE 4: EXECUTE FUNCTION notify_new_proposed_game();

and I'm not sure why.

I'm between novice and intermediate with SQL, so it could well be something really simple.

The function works when called manually in PSQL.

Desired result:

no error
trigger is set up

Laurenz Albe · Accepted Answer · 2020-09-02 06:37:13Z

1

There are several problems:

Syntax: you must be using an old version of PostgreSQL where you cannot use EXECUTE FUNCTION, but only EXECUTE PROCEDURE in the CREATE TRIGGER statement.
Trigger functions cannot be written in SQL. You must use PL/pgSQL or some other procedural language.
Your trigger function must RETURNS trigger, not void. Make sure to RETURN NEW; in the function.

answered Sep 2, 2020 at 6:37

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

James Wilson Over a year ago

Thank you for the prompt answer! I'm using version 12.4 and the docs suggest EXECUTE PROCEDURE is deprecated? 2 and 3 make a lot of sense and I'm going to try them:)

Laurenz Albe Over a year ago

You also forgot FOR EACH ROW.

Collectives™ on Stack Overflow

Syntax error in Postgres SQL when creating a trigger for a function I've defined?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related