Menu
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.
252
Introduction to PostgreSQL CREATE TRIGGER statement First, specify the name of the trigger after the TRIGGER keywords. Second, specify the timing that cause the trigger to fire. It can be BEFORE or AFTER an event occurs. Third, specify the event that invokes the trigger.
A PostgreSQL trigger is a function invoked automatically whenever an event such as insert, update, or delete occurs. In this section, you will learn about triggers and how to manage them effectively.
A “trigger” is defined as any event that sets a course of action in a motion. In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.
A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered.
If the trigger fires after the event, all changes, including the effects of other triggers, are “visible” to the trigger. A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies.
A constraint trigger can only be specified as AFTER. One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the event that will fire the trigger. Multiple events can be specified using OR, except when transition relations are requested. For UPDATE events, it is possible to specify a list of columns using this syntax:
CREATE TRIGGER creates a new trigger. CREATE OR REPLACE TRIGGER will either create a new trigger, or replace an existing trigger. The trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when certain operations are performed on that table.
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.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
