From 5ebeb579b9b281dba5f8415b2fbda86fdae7b366 Mon Sep 17 00:00:00 2001 From: Kevin Grittner Date: Tue, 4 Apr 2017 18:36:39 -0500 Subject: Follow-on cleanup for the transition table patch. Commit 59702716 added transition table support to PL/pgsql so that SQL queries in trigger functions could access those transient tables. In order to provide the same level of support for PL/perl, PL/python and PL/tcl, refactor the relevant code into a new function SPI_register_trigger_data. Call the new function in the trigger handler of all four PLs, and document it as a public SPI function so that authors of out-of-tree PLs can do the same. Also get rid of a second QueryEnvironment object that was maintained by PL/pgsql. That was previously used to deal with cursors, but the same approach wasn't appropriate for PLs that are less tangled up with core code. Instead, have SPI_cursor_open install the connection's current QueryEnvironment, as already happens for SPI_execute_plan. While in the docs, remove the note that transition tables were only supported in C and PL/pgSQL triggers, and correct some ommissions. Thomas Munro with some work by Kevin Grittner (mostly docs) --- src/pl/plpython/sql/plpython_trigger.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'src/pl/plpython/sql') diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql index a054fe729b..79c24b714b 100644 --- a/src/pl/plpython/sql/plpython_trigger.sql +++ b/src/pl/plpython/sql/plpython_trigger.sql @@ -406,3 +406,27 @@ SELECT * FROM a; DROP TABLE a; INSERT INTO b DEFAULT VALUES; SELECT * FROM b; + +-- check that SQL run in trigger code can see transition tables + +CREATE TABLE transition_table_test (id int, name text); +INSERT INTO transition_table_test VALUES (1, 'a'); + +CREATE FUNCTION transition_table_test_f() RETURNS trigger LANGUAGE plpythonu AS +$$ + rv = plpy.execute("SELECT * FROM old_table") + assert(rv.nrows() == 1) + plpy.info("old: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + rv = plpy.execute("SELECT * FROM new_table") + assert(rv.nrows() == 1) + plpy.info("new: " + str(rv[0]["id"]) + " -> " + rv[0]["name"]) + return None +$$; + +CREATE TRIGGER a_t AFTER UPDATE ON transition_table_test + REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table + FOR EACH STATEMENT EXECUTE PROCEDURE transition_table_test_f(); +UPDATE transition_table_test SET name = 'b'; + +DROP TABLE transition_table_test; +DROP FUNCTION transition_table_test_f(); -- cgit v1.2.1