From 2ec993a7cbdd8e251817ac6bbc9a704ce8346f73 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 10 Oct 2010 13:43:33 -0400 Subject: Support triggers on views. This patch adds the SQL-standard concept of an INSTEAD OF trigger, which is fired instead of performing a physical insert/update/delete. The trigger function is passed the entire old and/or new rows of the view, and must figure out what to do to the underlying tables to implement the update. So this feature can be used to implement updatable views using trigger programming style rather than rule hacking. In passing, this patch corrects the names of some columns in the information_schema.triggers view. It seems the SQL committee renamed them somewhere between SQL:99 and SQL:2003. Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me. --- doc/src/sgml/catalogs.sgml | 9 +- doc/src/sgml/information_schema.sgml | 61 +++++++++---- doc/src/sgml/plperl.sgml | 4 +- doc/src/sgml/plpgsql.sgml | 106 +++++++++++++++++++++-- doc/src/sgml/plpython.sgml | 20 ++--- doc/src/sgml/pltcl.sgml | 16 ++-- doc/src/sgml/ref/create_rule.sgml | 10 ++- doc/src/sgml/ref/create_trigger.sgml | 106 ++++++++++++++++++++--- doc/src/sgml/rules.sgml | 162 +++++++++++++++++++++++------------ doc/src/sgml/trigger.sgml | 140 +++++++++++++++++++++++------- 10 files changed, 487 insertions(+), 147 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8e4081cb33c..c37b9950b70 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4243,7 +4243,7 @@ The catalog pg_seclabel stores security - labels on database objects. See the + labels on database objects. See the statement. @@ -4795,7 +4795,8 @@ - The catalog pg_trigger stores triggers on tables. + The catalog pg_trigger stores triggers on tables + and views. See for more information. @@ -4839,7 +4840,7 @@ tgtype int2 - Bit mask identifying trigger conditions + Bit mask identifying trigger firing conditions @@ -4956,7 +4957,7 @@ pg_class.relhastriggers - must be true if a table has any triggers in this catalog. + must be true if a relation has any triggers in this catalog. diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 509efea8e19..9d30949aa77 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4885,8 +4885,8 @@ ORDER BY c.ordinal_position; The view triggers contains all triggers defined - in the current database on tables that the current user owns or has - some non-SELECT privilege on. + in the current database on tables and views that the current user owns + or has some non-SELECT privilege on. @@ -4987,34 +4987,34 @@ ORDER BY c.ordinal_position; - condition_timing + action_timing character_data - Time at which the trigger fires (BEFORE or - AFTER) + Time at which the trigger fires (BEFORE, + AFTER, or INSTEAD OF) - condition_reference_old_table + action_reference_old_table sql_identifier Applies to a feature not available in PostgreSQL - condition_reference_new_table + action_reference_new_table sql_identifier Applies to a feature not available in PostgreSQL - condition_reference_old_row + action_reference_old_row sql_identifier Applies to a feature not available in PostgreSQL - condition_reference_new_row + action_reference_new_row sql_identifier Applies to a feature not available in PostgreSQL @@ -5032,9 +5032,9 @@ ORDER BY c.ordinal_position; Triggers in PostgreSQL have two incompatibilities with the SQL standard that affect the representation in the information schema. First, trigger names are - local to the table in PostgreSQL, rather + local to each table in PostgreSQL, rather than being independent schema objects. Therefore there can be duplicate - trigger names defined in one schema, as long as they belong to + trigger names defined in one schema, so long as they belong to different tables. (trigger_catalog and trigger_schema are really the values pertaining to the table that the trigger is defined on.) Second, triggers can @@ -5045,14 +5045,34 @@ ORDER BY c.ordinal_position; multiple rows in the information schema, one for each type of event. As a consequence of these two issues, the primary key of the view triggers is really - (trigger_catalog, trigger_schema, trigger_name, - event_object_table, event_manipulation) instead of + (trigger_catalog, trigger_schema, event_object_table, + trigger_name, event_manipulation) instead of (trigger_catalog, trigger_schema, trigger_name), which is what the SQL standard specifies. Nonetheless, if you define your triggers in a manner that conforms with the SQL standard (trigger names unique in the schema and only one event type per trigger), this will not affect you. + + + + Prior to PostgreSQL 9.1, this view's columns + action_timing, + action_reference_old_table, + action_reference_new_table, + action_reference_old_row, and + action_reference_new_row + were named + condition_timing, + condition_reference_old_table, + condition_reference_new_table, + condition_reference_old_row, and + condition_reference_new_row + respectively. + That was how they were named in the SQL:1999 standard. + The new naming conforms to SQL:2003 and later. + + @@ -5562,19 +5582,28 @@ ORDER BY c.ordinal_position; is_trigger_updatable yes_or_no - Applies to a feature not available in PostgreSQL + + YES if the view has an INSTEAD OF + UPDATE trigger defined on it, NO if not + is_trigger_deletable yes_or_no - Applies to a feature not available in PostgreSQL + + YES if the view has an INSTEAD OF + DELETE trigger defined on it, NO if not + is_trigger_insertable_into yes_or_no - Applies to a feature not available in PostgreSQL + + YES if the view has an INSTEAD OF + INSERT trigger defined on it, NO if not + diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index d2584623b5a..e07fba5b5f3 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -999,7 +999,9 @@ $$ LANGUAGE plperl; $_TD->{when} - When the trigger was called: BEFORE, AFTER, or UNKNOWN + When the trigger was called: BEFORE, + AFTER, INSTEAD OF, or + UNKNOWN diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index d3bf847c892..934bea6c376 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3112,9 +3112,9 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id; TG_WHEN - Data type text; a string of either - BEFORE or AFTER - depending on the trigger's definition. + Data type text; a string of + BEFORE, AFTER, or + INSTEAD OF, depending on the trigger's definition. @@ -3234,8 +3234,25 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id; effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is - usually not sensible. A useful idiom in DELETE - triggers might be to return OLD. + usually not sensible. The usual idiom in DELETE + triggers is to return OLD. + + + + INSTEAD OF triggers (which are always row-level triggers, + and may only be used on views) can return null to signal that they did + not perform any updates, and that the rest of the operation for this + row should be skipped (i.e., subsequent triggers are not fired, and the + row is not counted in the rows-affected status for the surrounding + INSERT/UPDATE/DELETE). + Otherwise a nonnull value should be returned, to signal + that the trigger performed the requested operation. For + INSERT and UPDATE operations, the return value + should be NEW, which the trigger function may modify to + support INSERT RETURNING and UPDATE RETURNING + (this will also affect the row value passed to any subsequent triggers). + For DELETE operations, the return value should be + OLD. @@ -3354,6 +3371,85 @@ AFTER INSERT OR UPDATE OR DELETE ON emp + + A variation of the previous example uses a view joining the main table + to the audit table, to show when each entry was last modified. This + approach still records the full audit trail of changes to the table, + but also presents a simplified view of the audit trail, showing just + the last modified timestamp derived from the audit trail for each entry. + shows an example + of an audit trigger on a view in PL/pgSQL. + + + + A <application>PL/pgSQL</application> View Trigger Procedure For Auditing + + + This example uses a trigger on the view to make it updatable, and + ensure that any insert, update or delete of a row in the view is + recorded (i.e., audited) in the emp_audit table. The current time + and user name are recorded, together with the type of operation + performed, and the view displays the last modified time of each row. + + + +CREATE TABLE emp ( + empname text PRIMARY KEY, + salary integer +); + +CREATE TABLE emp_audit( + operation char(1) NOT NULL, + userid text NOT NULL, + empname text NOT NULL, + salary integer, + stamp timestamp NOT NULL +); + +CREATE VIEW emp_view AS + SELECT e.empname, + e.salary, + max(ea.stamp) AS last_updated + FROM emp e + LEFT JOIN emp_audit ea ON ea.empname = e.empname + GROUP BY 1, 2; + +CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$ + BEGIN + -- + -- Perform the required operation on emp, and create a row in emp_audit + -- to reflect the change made to emp. + -- + IF (TG_OP = 'DELETE') THEN + DELETE FROM emp WHERE empname = OLD.empname; + IF NOT FOUND THEN RETURN NULL; END IF; + + OLD.last_updated = now(); + INSERT INTO emp_audit VALUES('D', user, OLD.*); + RETURN OLD; + ELSIF (TG_OP = 'UPDATE') THEN + UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname; + IF NOT FOUND THEN RETURN NULL; END IF; + + NEW.last_updated = now(); + INSERT INTO emp_audit VALUES('U', user, NEW.*); + RETURN NEW; + ELSIF (TG_OP = 'INSERT') THEN + INSERT INTO emp VALUES(NEW.empname, NEW.salary); + + NEW.last_updated = now(); + INSERT INTO emp_audit VALUES('I', user, NEW.*); + RETURN NEW; + END IF; + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER emp_audit +INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view + FOR EACH ROW EXECUTE PROCEDURE update_emp_view(); + + + One use of triggers is to maintain a summary table of another table. The resulting summary can be used in place of the diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index c5445637f16..6eb884e0dc5 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -609,7 +609,7 @@ CREATE TYPE greeting AS ( who text ); - + A set result can be returned from a: @@ -751,8 +751,7 @@ $$ LANGUAGE plpythonu; contains the event as a string: INSERT, UPDATE, - DELETE, TRUNCATE, - or UNKNOWN. + DELETE, or TRUNCATE. @@ -761,8 +760,8 @@ $$ LANGUAGE plpythonu; TD["when"] - contains one of BEFORE, AFTER, - or UNKNOWN. + contains one of BEFORE, AFTER, or + INSTEAD OF. @@ -771,8 +770,7 @@ $$ LANGUAGE plpythonu; TD["level"] - contains one of ROW, - STATEMENT, or UNKNOWN. + contains ROW or STATEMENT. @@ -838,12 +836,14 @@ $$ LANGUAGE plpythonu; - If TD["when"] is BEFORE and + If TD["when"] is BEFORE or + INSTEAD OF and TD["level"] is ROW, you can return None or "OK" from the Python function to indicate the row is unmodified, - "SKIP" to abort the event, or "MODIFY" to - indicate you've modified the row. + "SKIP" to abort the event, or if TD["event"] + is INSERT or UPDATE you can return + "MODIFY" to indicate you've modified the new row. Otherwise the return value is ignored. diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 326c757e432..398209d3966 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -591,8 +591,8 @@ SELECT 'doesn''t' AS ret $TG_when - The string BEFORE or AFTER depending on the - type of trigger event. + The string BEFORE, AFTER, or + INSTEAD OF, depending on the type of trigger event. @@ -665,10 +665,14 @@ SELECT 'doesn''t' AS ret the operation (INSERT/UPDATE/DELETE) that fired the trigger will proceed normally. SKIP tells the trigger manager to silently suppress the operation for this row. If a list is returned, it tells PL/Tcl to - return a modified row to the trigger manager that will be inserted - instead of the one given in $NEW. (This works for INSERT and UPDATE - only.) Needless to say that all this is only meaningful when the trigger - is BEFORE and FOR EACH ROW; otherwise the return value is ignored. + return a modified row to the trigger manager. This is only meaningful + for row-level BEFORE INSERT or UPDATE + triggers for which the modified row will be inserted instead of the one + given in $NEW; or for row-level INSTEAD OF + INSERT or UPDATE triggers where the returned row + is used to support INSERT RETURNING and + UPDATE RETURNING commands. The return value is ignored for + other types of triggers. diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 5d2182c2cab..e7c88497d60 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -53,7 +53,7 @@ CREATE [ OR REPLACE ] RULE name AS physical row, you probably want to use a trigger, not a rule. More information about the rules system is in . - + Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist @@ -73,7 +73,9 @@ CREATE [ OR REPLACE ] RULE name AS sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables. If you want to support INSERT RETURNING and so on, then be sure to put a suitable - RETURNING clause into each of these rules. + RETURNING clause into each of these rules. Alternatively, + an updatable view can be implemented using INSTEAD OF + triggers (see ). @@ -232,12 +234,12 @@ CREATE [ OR REPLACE ] RULE name AS CREATE RULE "_RETURN" AS ON SELECT TO t1 - DO INSTEAD + DO INSTEAD SELECT * FROM t2; CREATE RULE "_RETURN" AS ON SELECT TO t2 - DO INSTEAD + DO INSTEAD SELECT * FROM t1; SELECT * FROM t1; diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 1934113181e..95d67aad6f5 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } +CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE PROCEDURE function_name ( arguments ) @@ -33,21 +33,22 @@ CREATE TRIGGER name { BEFORE | AFTE CREATE TRIGGER creates a new trigger. The - trigger will be associated with the specified table and will + trigger will be associated with the specified table or view and will execute the specified function function_name when certain events occur. - The trigger can be specified to fire either before the + The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or - DELETE is attempted) or after the operation has + DELETE is attempted); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or - DELETE has completed). If the trigger fires - before the event, the trigger can skip the operation for the - current row, or change the row being inserted (for + DELETE has completed); or instead of the operation + (in the case of inserts, updates or deletes on a view). + If the trigger fires before or instead of the event, the trigger can skip + the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the effects of other triggers, are visible @@ -68,11 +69,71 @@ CREATE TRIGGER name { BEFORE | AFTE - In addition, triggers may be defined to fire for a + Triggers that are specified to fire INSTEAD OF the trigger + event must be marked FOR EACH ROW, and can only be defined + on views. BEFORE and AFTER triggers on a view + must be marked as FOR EACH STATEMENT. + + + + In addition, triggers may be defined to fire for TRUNCATE, though only FOR EACH STATEMENT. + + The following table summarizes which types of triggers may be used on + tables and views: + + + + + + + When + Event + Row-level + Statement-level + + + + + BEFORE + INSERT/UPDATE/DELETE + Tables + Tables and views + + + TRUNCATE + — + Tables + + + AFTER + INSERT/UPDATE/DELETE + Tables + Tables and views + + + TRUNCATE + — + Tables + + + INSTEAD OF + INSERT/UPDATE/DELETE + Views + — + + + TRUNCATE + — + — + + + + + Also, a trigger definition can specify a Boolean WHEN condition, which will be tested to see whether the trigger should @@ -116,10 +177,11 @@ CREATE TRIGGER name { BEFORE | AFTE BEFORE AFTER + INSTEAD OF - Determines whether the function is called before or after the - event. + Determines whether the function is called before, after, or instead of + the event. @@ -143,6 +205,10 @@ UPDATE OF column_name1 [, column_name2 + + + UPDATE INSTEAD OF triggers do not support lists of columns. + @@ -150,7 +216,7 @@ UPDATE OF column_name1 [, column_name2table - The name (optionally schema-qualified) of the table the trigger + The name (optionally schema-qualified) of the table or view the trigger is for. @@ -188,6 +254,11 @@ UPDATE OF column_name1 [, column_name2DELETE triggers cannot refer to NEW. + + INSTEAD OF triggers do not support WHEN + conditions. + + Currently, WHEN expressions cannot contain subqueries. @@ -326,6 +397,16 @@ CREATE TRIGGER log_update WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE log_account_update(); + + Execute the function view_insert_row for each row to insert + rows into the tables underlying a view: + + +CREATE TRIGGER view_insert + INSTEAD OF INSERT ON my_view + FOR EACH ROW + EXECUTE PROCEDURE view_insert_row(); + @@ -396,7 +477,8 @@ CREATE TRIGGER log_update The ability to fire triggers for TRUNCATE is a - PostgreSQL extension of the SQL standard. + PostgreSQL extension of the SQL standard, as is the + ability to define statement-level triggers on views. diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 17c92bdf130..8d5ffd673fe 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -76,7 +76,7 @@ - When reading the SQL representations of the + When reading the SQL representations of the query trees in this chapter it is necessary to be able to identify the parts the statement is broken into when it is in the query tree structure. The parts of a query tree are @@ -132,11 +132,11 @@ - SELECT queries normally don't have a result - relation. The special case of a SELECT INTO is - mostly identical to a CREATE TABLE followed by a - INSERT ... SELECT and is not discussed - separately here. + SELECT queries don't have a result + relation. (The special case of SELECT INTO is + mostly identical to CREATE TABLE followed by + INSERT ... SELECT, and is not discussed + separately here.) @@ -166,11 +166,13 @@ - DELETE commands don't need a target list - because they don't produce any result. In fact, the planner will - add a special CTID entry to the empty target list, but - this is after the rule system and will be discussed later; for the - rule system, the target list is empty. + DELETE commands don't need a normal target list + because they don't produce any result. Instead, the rule system + adds a special CTID entry to the empty target list, + to allow the executor to find the row to be deleted. + (CTID is added when the result relation is an ordinary + table. If it is a view, a whole-row variable is added instead, + as described in .) @@ -189,10 +191,11 @@ For UPDATE commands, the target list describes the new rows that should replace the old ones. In the rule system, it contains just the expressions from the SET - column = expression part of the command. The planner will handle - missing columns by inserting expressions that copy the values from - the old row into the new one. And it will add the special - CTID entry just as for DELETE, too. + column = expression part of the command. The planner will + handle missing columns by inserting expressions that copy the values + from the old row into the new one. Just as for DELETE, + the rule system adds a CTID or whole-row variable so that + the executor can identify the old row to be updated. @@ -283,7 +286,7 @@ CREATE VIEW myview AS SELECT * FROM mytab; - + compared against the two commands: @@ -291,7 +294,7 @@ CREATE TABLE myview (same column list as mytab); CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; - + because this is exactly what the CREATE VIEW command does internally. This has some side effects. One of them is that the information about a view in the @@ -431,7 +434,7 @@ CREATE VIEW shoe_ready AS The action of the rule is one query tree that is a copy of the SELECT statement in the view creation command. - + The two extra range @@ -512,7 +515,7 @@ SELECT s.sl_name, s.sl_avail, To expand the view, the rewriter simply creates a subquery range-table entry containing the rule's action query tree, and substitutes this - range table entry for the original one that referenced the view. The + range table entry for the original one that referenced the view. The resulting rewritten query tree is almost the same as if you had typed: @@ -578,7 +581,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, WHERE shoe_ready.total_avail >= 2; - The first rule applied will be the one for the + The first rule applied will be the one for the shoe_ready view and it results in the query tree: @@ -656,7 +659,9 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, Two details of the query tree aren't touched in the description of view rules above. These are the command type and the result relation. - In fact, view rules don't need this information. + In fact, the command type is not needed by view rules, but the result + relation may affect the way in which the query rewriter works, because + special care needs to be taken if the result relation is a view. @@ -718,22 +723,21 @@ UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a; and thus the executor run over the join will produce exactly the - same result set as a: + same result set as: SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; - - will do. But there is a little problem in - UPDATE: The executor does not care what the - results from the join it is doing are meant for. It just produces - a result set of rows. The difference that one is a - SELECT command and the other is an - UPDATE is handled in the caller of the - executor. The caller still knows (looking at the query tree) that - this is an UPDATE, and it knows that this - result should go into table t1. But which of the rows that are - there has to be replaced by the new row? + + But there is a little problem in + UPDATE: the part of the executor plan that does + the join does not care what the results from the join are + meant for. It just produces a result set of rows. The fact that + one is a SELECT command and the other is an + UPDATE is handled higher up in the executor, where + it knows that this is an UPDATE, and it knows that + this result should go into table t1. But which of the rows + that are there has to be replaced by the new row? @@ -750,7 +754,7 @@ SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; - + Now another detail of PostgreSQL enters the stage. Old table rows aren't overwritten, and this is why ROLLBACK is fast. In an UPDATE, @@ -759,7 +763,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; CTID pointed to, the cmax and xmax entries are set to the current command counter and current transaction ID. Thus the old row is hidden, and after - the transaction commits the vacuum cleaner can really remove it. + the transaction commits the vacuum cleaner can eventually remove + the dead row. @@ -803,16 +808,57 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; What happens if a view is named as the target relation for an INSERT, UPDATE, or - DELETE? After doing the substitutions - described above, we will have a query tree in which the result - relation points at a subquery range-table entry. This will not - work, so the rewriter throws an error if it sees it has produced - such a thing. + DELETE? Simply doing the substitutions + described above would give a query tree in which the result + relation points at a subquery range-table entry, which will not + work. Instead, the rewriter assumes that the operation will be + handled by an INSTEAD OF trigger on the view. + (If there is no such trigger, the executor will throw an error + when execution starts.) Rewriting works slightly differently + in this case. For INSERT, the rewriter does + nothing at all with the view, leaving it as the result relation + for the query. For UPDATE and + DELETE, it's still necessary to expand the + view query to produce the old rows that the command will + attempt to update or delete. So the view is expanded as normal, + but another unexpanded range-table entry is added to the query + to represent the view in its capacity as the result relation. - To change this, we can define rules that modify the behavior of - these kinds of commands. This is the topic of the next section. + The problem that now arises is how to identify the rows to be + updated in the view. Recall that when the result relation + is a table, a special CTID entry is added to the target + list to identify the physical locations of the rows to be updated. + This does not work if the result relation is a view, because a view + does not have any CTID, since its rows do not have + actual physical locations. Instead, for an UPDATE + or DELETE operation, a special wholerow + entry is added to the target list, which expands to include all + columns from the view. The executor uses this value to supply the + old row to the INSTEAD OF trigger. It is + up to the trigger to work out what to update based on the old and + new row values. + + + + If there are no INSTEAD OF triggers to update the view, + the executor will throw an error, because it cannot automatically + update a view by itself. To change this, we can define rules that + modify the behavior of INSERT, + UPDATE, and DELETE commands on + a view. These rules will rewrite the command, typically into a command + that updates one or more tables, rather than views. That is the topic + of the next section. + + + + Note that rules are evaluated first, rewriting the original query + before it is planned and executed. Therefore, if a view has + INSTEAD OF triggers as well as rules on INSERT, + UPDATE, or DELETE, then the rules will be + evaluated first, and depending on the result, the triggers may not be + used at all. @@ -1383,7 +1429,7 @@ SELECT * FROM shoelace_arrive; Take a quick look at the current data: - + SELECT * FROM shoelace; @@ -1522,8 +1568,8 @@ SELECT s.sl_name, AND shoelace_data.sl_name = s.sl_name AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail; - - After that the rule system runs out of rules and returns the + + After that the rule system runs out of rules and returns the generated query trees. @@ -1542,7 +1588,7 @@ SELECT s.sl_name, WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail; - + UPDATE shoelace_data SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, @@ -1675,7 +1721,7 @@ SELECT * FROM shoelace; in total uses 4 nesting/joined views, where one of them itself has a subquery qualification containing a view and where calculated view columns are used, - gets rewritten into + gets rewritten into one single query tree that deletes the requested data from a real table. @@ -1783,12 +1829,13 @@ CREATE VIEW phone_number AS SELECT person, phone FROM phone_data WHERE phone NOT LIKE '412%'; This view might seem secure, since the rule system will rewrite any - SELECT from phone_number into a + SELECT from phone_number into a SELECT from phone_data and add the qualification that only entries where phone does not begin with 412 are wanted. But if the user can create his or her own functions, it is not difficult to convince the planner to execute the user-defined function prior to the NOT LIKE expression. + For example: CREATE FUNCTION tricky(text, text) RETURNS bool AS $$ BEGIN @@ -1796,6 +1843,7 @@ BEGIN RETURN true; END $$ LANGUAGE plpgsql COST 0.0000000000000000000001; + SELECT * FROM phone_number WHERE tricky(person, phone); Every person and phone number in the phone_data table will be @@ -1803,8 +1851,8 @@ SELECT * FROM phone_number WHERE tricky(person, phone); execute the inexpensive tricky function before the more expensive NOT LIKE. Even if the user is prevented from defining new functions, built-in functions can be used in - similar attacks. (For example, casting functions include their inputs in - the error messages they produce.) + similar attacks. (For example, most casting functions include their + input values in the error messages they produce.) @@ -1906,19 +1954,21 @@ SELECT * FROM phone_number WHERE tricky(person, phone); - On the other hand, a trigger cannot be created on views because - there is no real data in a view relation; however INSERT, UPDATE, - and DELETE rules can be created on views. + In this chapter, we focused on using rules to update views. All of + the update rule examples in this chapter can also be implemented + using INSTEAD OF triggers on the views. Writing such + triggers is often easier than writing rules, particularly if complex + logic is required to perform the update. For the things that can be implemented by both, which is best depends on the usage of the database. - A trigger is fired for any affected row once. A rule manipulates + A trigger is fired once for each affected row. A rule modifies the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is - called for every single row and must execute its operations + called for every single row and must re-determine what to do many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right. @@ -1961,7 +2011,7 @@ CREATE RULE computer_del AS ON DELETE TO computer Now we look at different types of deletes. In the case of a: - + DELETE FROM computer WHERE hostname = 'mypc.local.net'; diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 3565bffe533..38979cdaedb 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -33,7 +33,11 @@ A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is - performed. Triggers can be defined to execute either before or after any + performed. Triggers can be attached to both tables and views. + + + + On tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. @@ -45,6 +49,20 @@ appropriate time to handle the event. + + On views, triggers can be defined to execute instead of + INSERT, UPDATE, or + DELETE operations. INSTEAD OF triggers + are fired once for each row that needs to be modified in the view. + It is the responsibility of the + trigger's function to perform the necessary modifications to the + underlying base tables and, where appropriate, return the modified + row as it will appear in the view. Triggers on views can also be defined + to execute once per SQL statement, before or after + INSERT, UPDATE, or + DELETE operations. + + The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a @@ -74,18 +92,29 @@ two types of triggers are sometimes called row-level triggers and statement-level triggers, respectively. Triggers on TRUNCATE may only be - defined at statement-level. + defined at statement level. On views, triggers that fire before or + after may only be defined at statement level, while triggers that fire + instead of an INSERT, UPDATE, + or DELETE may only be defined at row level. - Triggers are also classified as before triggers and - after triggers. - Statement-level before triggers naturally fire before the - statement starts to do anything, while statement-level after - triggers fire at the very end of the statement. Row-level before + Triggers are also classified according to whether they fire + before, after, or + instead of the operation. These are referred to + as BEFORE triggers, AFTER triggers, and + INSTEAD OF triggers respectively. + Statement-level BEFORE triggers naturally fire before the + statement starts to do anything, while statement-level AFTER + triggers fire at the very end of the statement. These types of + triggers may be defined on tables or views. Row-level BEFORE triggers fire immediately before a particular row is operated on, - while row-level after triggers fire at the end of the statement - (but before any statement-level after triggers). + while row-level AFTER triggers fire at the end of the + statement (but before any statement-level AFTER triggers). + These types of triggers may only be defined on tables. Row-level + INSTEAD OF triggers may only be defined on views, and fire + immediately as each row in the view is identified as needing to be + operated on. @@ -101,8 +130,8 @@ It can return NULL to skip the operation for the current row. This instructs the executor to not perform the - row-level operation that invoked the trigger (the insertion or - modification of a particular table row). + row-level operation that invoked the trigger (the insertion, + modification, or deletion of a particular table row). @@ -117,14 +146,33 @@ - A row-level before trigger that does not intend to cause either of - these behaviors must be careful to return as its result the same + A row-level BEFORE trigger that does not intend to cause + either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers). + + A row-level INSTEAD OF trigger should either return + NULL to indicate that it did not modify any data from + the view's underlying base tables, or it should return the view + row that was passed in (the NEW row + for INSERT and UPDATE + operations, or the OLD row for + DELETE operations). A nonnull return value is + used to signal that the trigger performed the necessary data + modifications in the view. This will cause the count of the number + of rows affected by the command to be incremented. For + INSERT and UPDATE operations, the trigger + may modify the NEW row before returning it. This will + change the data returned by + INSERT RETURNING or UPDATE RETURNING, + and is useful when the view will not show exactly the same data + that was provided. + + The return value is ignored for row-level triggers fired after an operation, and so they can return NULL. @@ -133,11 +181,12 @@ If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by - trigger name. In the case of before triggers, the - possibly-modified row returned by each trigger becomes the input - to the next trigger. If any before trigger returns + trigger name. In the case of BEFORE and + INSTEAD OF triggers, the possibly-modified row returned by + each trigger becomes the input to the next trigger. If any + BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent - triggers are not fired. + triggers are not fired (for that row). @@ -146,31 +195,37 @@ be fired. In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row. (Statement-level triggers can also have WHEN conditions, although the feature - is not so useful for them.) In a before trigger, the WHEN + is not so useful for them.) In a BEFORE trigger, the + WHEN condition is evaluated just before the function is or would be executed, so using WHEN is not materially different from testing the same condition at the beginning of the trigger function. However, in - an after trigger, the WHEN condition is evaluated just after - the row update occurs, and it determines whether an event is queued to - fire the trigger at the end of statement. So when an after trigger's + an AFTER trigger, the WHEN condition is evaluated + just after the row update occurs, and it determines whether an event is + queued to fire the trigger at the end of statement. So when an + AFTER trigger's WHEN condition does not return true, it is not necessary to queue an event nor to re-fetch the row at end of statement. This can result in significant speedups in statements that modify many rows, if the trigger only needs to be fired for a few of the rows. + INSTEAD OF triggers do not support + WHEN conditions. - Typically, row before triggers are used for checking or + Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, - a before trigger might be used to insert the current time into a + a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are - consistent. Row after triggers are most sensibly + consistent. Row-level AFTER triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is - that an after trigger can be certain it is seeing the final value of the - row, while a before trigger cannot; there might be other before triggers - firing after it. If you have no specific reason to make a trigger before - or after, the before case is more efficient, since the information about + that an AFTER trigger can be certain it is seeing the final + value of the row, while a BEFORE trigger cannot; there might + be other BEFORE triggers firing after it. If you have no + specific reason to make a trigger BEFORE or + AFTER, the BEFORE case is more efficient, since + the information about the operation doesn't have to be saved until end of statement. @@ -237,7 +292,8 @@ Statement-level triggers follow simple visibility rules: none of the changes made by a statement are visible to statement-level triggers that are invoked before the statement, whereas all - modifications are visible to statement-level after triggers. + modifications are visible to statement-level AFTER + triggers. @@ -245,14 +301,14 @@ The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible - to SQL commands executed in a row-level before trigger, because - it hasn't happened yet. + to SQL commands executed in a row-level BEFORE trigger, + because it hasn't happened yet. - However, SQL commands executed in a row-level before + However, SQL commands executed in a row-level BEFORE trigger will see the effects of data changes for rows previously processed in the same outer command. This requires caution, since the ordering of these @@ -263,7 +319,16 @@ - When a row-level after trigger is fired, all data changes made + Similarly, a row-level INSTEAD OF trigger will see the + effects of data changes made by previous firings of INSTEAD + OF triggers in the same outer command. + + + + + + When a row-level AFTER trigger is fired, all data + changes made by the outer command are already complete, and are visible to the invoked trigger function. @@ -386,6 +451,15 @@ typedef struct TriggerData + + TRIGGER_FIRED_INSTEAD(tg_event) + + + Returns true if the trigger fired instead of the operation. + + + + TRIGGER_FIRED_FOR_ROW(tg_event) -- cgit v1.2.3