Database Reference
In-Depth Information
We will now insert two values in the
tab_python
table as follows:
warehouse_db=# INSERT INTO tab_python VALUES (1, 'Adam',
'Chicago');
warehouse_db=# INSERT INTO tab_python VALUES (2, 'John', 'Miami');
Here goes the trigger function in Python. You'll be observing how the same logic is
being used different syntactically. Returning
SKIP
will abort the transaction, which
in this case is the
INSERT
operation:
warehouse_db=# CREATE EXTENSION plpython3u;
CREATE FUNCTION python_trigger_func()
RETURNS trigger AS $$
IF TD["event"] == 'INSERT':
IF TD["new"]["emp_id"] >= 10:
RETURN "SKIP";
RETURN "MODIFY"
$$ LANGUAGE plpython3u;
Finally, associate the trigger with the
tab_python
table:
warehouse_db=# CREATE TRIGGER python_trigger
BEFORE INSERT ON tab_python
FOR EACH ROW
EXECUTE PROCEDURE python_trigger_func();
Here, the indentation is signiicant in the Python language and if
not handled well, can put you in trouble.
We'll try to insert rows with
emp_id
greater and smaller than
10
to see whether
trigger is working:
warehouse_db=# INSERT INTO tab_python VALUES
(13, 'Roger', 'Boston');
To check whether the row was inserted into the
tab_python
table, we will write a
SELECT
command as follows:
warehouse_db=# SELECT * FROM tab_python;
emp_id | emp_name | emp_city
--------+----------+----------
1 | Adam | Chicago
2 | John | Miami
(2 rows)
