PostgreSQL Syntax error at or near FUNCTION on create trigger

Question

I installed postgresql and postgresql-plpython-10 (using apt) on a new machine running Ubuntu server 18.04 and I have reinstated a Postgresql database (extracted using pg_dumpall) from another machine onto a new machine (Linux).

I checked that the trackspreadsheetnztplpy function exists in the database and the table nztgsheet exists. I reran the function on the old machine and it worked perfectly. I checked plpythonu is installed on the new machine using psql command: \dL.

SQL error:

ERROR: syntax error at or near "FUNCTION" LINE 1: ...H ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION t... ^

In statement: CREATE TRIGGER trackspreadsheetnzt AFTER UPDATE ON nztgsheet FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION trackspreadsheetnztplpy();

I expected the trigger function to work, but it throws a syntax error instead.

Nick Barnes · Accepted Answer · 2019-08-08 23:25:28Z

26

The EXECUTE FUNCTION syntax for CREATE TRIGGER statements was introduced in Postgres 11.

In Postgres 10, you need to say EXECUTE PROCEDURE instead.

This syntax was deprecated in Postgres 11 with the introduction of procedures, which are distinct from functions and cannot be used to implement a trigger.

answered Aug 8, 2019 at 23:25

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

1 Comment

Mark Warburton Over a year ago

Thanks. Yes, the old machine was running version 11. Upgraded the new machine to version 11 and now it works!. Thanks.

Nikhil s g · Accepted Answer · 2021-09-15 18:13:32Z

0

If you are trying to add trigger function from pgadmin UI, In the code tab you need to specify function from BEGIN to END, as postgres implicitly specifies in the code " CREATE OR REPLACE FUNCTION ****() RETURNS TRIGGER AS $$"

answered Sep 15, 2021 at 18:13

Nikhil s g

111 bronze badge

Collectives™ on Stack Overflow

PostgreSQL Syntax error at or near FUNCTION on create trigger

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related