diff options
author | Peter Eisentraut <peter_e@gmx.net> | 2018-08-15 23:08:34 +0200 |
---|---|---|
committer | Peter Eisentraut <peter_e@gmx.net> | 2018-08-22 14:44:49 +0200 |
commit | 0a63f996e018ac508c858e87fa39cc254a5db49f (patch) | |
tree | 38598534b4cadc3f3f56e9049b7ca0daa4a339d2 | |
parent | d12782898eb5979bef3c6dd0d80b93d1aaf53cac (diff) | |
download | postgresql-0a63f996e018ac508c858e87fa39cc254a5db49f.tar.gz |
Change PROCEDURE to FUNCTION in CREATE TRIGGER syntax
Since procedures are now a different thing from functions, change the
CREATE TRIGGER and CREATE EVENT TRIGGER syntax to use FUNCTION in the
clause that specifies the function. PROCEDURE is still accepted for
compatibility.
pg_dump and ruleutils.c output is not changed yet, because that would
require a change in information_schema.sql and thus a catversion change.
Reported-by: Peter Geoghegan <pg@bowt.ie>
Reviewed-by: Jonathan S. Katz <jonathan.katz@excoventures.com>
-rw-r--r-- | doc/src/sgml/ddl.sgml | 2 | ||||
-rw-r--r-- | doc/src/sgml/event-trigger.sgml | 4 | ||||
-rw-r--r-- | doc/src/sgml/func.sgml | 6 | ||||
-rw-r--r-- | doc/src/sgml/information_schema.sgml | 2 | ||||
-rw-r--r-- | doc/src/sgml/lo.sgml | 2 | ||||
-rw-r--r-- | doc/src/sgml/plperl.sgml | 4 | ||||
-rw-r--r-- | doc/src/sgml/plpgsql.sgml | 16 | ||||
-rw-r--r-- | doc/src/sgml/pltcl.sgml | 4 | ||||
-rw-r--r-- | doc/src/sgml/ref/create_event_trigger.sgml | 12 | ||||
-rw-r--r-- | doc/src/sgml/ref/create_trigger.sgml | 24 | ||||
-rw-r--r-- | doc/src/sgml/tcn.sgml | 2 | ||||
-rw-r--r-- | doc/src/sgml/textsearch.sgml | 4 | ||||
-rw-r--r-- | doc/src/sgml/trigger.sgml | 4 | ||||
-rw-r--r-- | src/backend/parser/gram.y | 9 | ||||
-rw-r--r-- | src/test/regress/expected/triggers.out | 4 | ||||
-rw-r--r-- | src/test/regress/sql/triggers.sql | 4 |
16 files changed, 62 insertions, 41 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 6aa035188f..5ae3cacbf0 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3580,7 +3580,7 @@ LANGUAGE plpgsql; <programlisting> CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement - FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); + FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger(); </programlisting> We must redefine the trigger function each month so that it always diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index be975d1399..d273dc5b58 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -1044,7 +1044,7 @@ CREATE FUNCTION noddl() RETURNS event_trigger AS 'noddl' LANGUAGE C; CREATE EVENT TRIGGER noddl ON ddl_command_start - EXECUTE PROCEDURE noddl(); + EXECUTE FUNCTION noddl(); </programlisting> </para> @@ -1129,7 +1129,7 @@ $$; CREATE EVENT TRIGGER no_rewrite_allowed ON table_rewrite - EXECUTE PROCEDURE no_rewrite(); + EXECUTE FUNCTION no_rewrite(); </programlisting> </para> </sect1> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c6f61ce2c0..eaf8fbdf62 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -20594,7 +20594,7 @@ SELECT (pg_stat_file('filename')).modification; <programlisting> CREATE TRIGGER z_min_update BEFORE UPDATE ON tablename -FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); +FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger(); </programlisting> In most cases, you would want to fire this trigger last for each row. Bearing in mind that triggers fire in name order, you would then @@ -20846,7 +20846,7 @@ END $$; CREATE EVENT TRIGGER test_event_trigger_for_drops ON sql_drop - EXECUTE PROCEDURE test_event_trigger_for_drops(); + EXECUTE FUNCTION test_event_trigger_for_drops(); </programlisting> </para> </sect2> @@ -20911,7 +20911,7 @@ $$; CREATE EVENT TRIGGER test_table_rewrite_oid ON table_rewrite - EXECUTE PROCEDURE test_event_trigger_table_rewrite_oid(); + EXECUTE FUNCTION test_event_trigger_table_rewrite_oid(); </programlisting> </para> </sect2> diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 09ef2827f2..9a61aa8541 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -5793,7 +5793,7 @@ ORDER BY c.ordinal_position; <entry><type>character_data</type></entry> <entry> Statement that is executed by the trigger (currently always - <literal>EXECUTE PROCEDURE + <literal>EXECUTE FUNCTION <replaceable>function</replaceable>(...)</literal>) </entry> </row> diff --git a/doc/src/sgml/lo.sgml b/doc/src/sgml/lo.sgml index ab8d192bc1..cce37932ec 100644 --- a/doc/src/sgml/lo.sgml +++ b/doc/src/sgml/lo.sgml @@ -70,7 +70,7 @@ CREATE TABLE image (title text, raster lo); CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image - FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster); + FOR EACH ROW EXECUTE FUNCTION lo_manage(raster); </programlisting> <para> diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 6296a226b6..967efba3b5 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1300,7 +1300,7 @@ $$ LANGUAGE plperl; CREATE TRIGGER test_valid_id_trig BEFORE INSERT OR UPDATE ON test - FOR EACH ROW EXECUTE PROCEDURE valid_id(); + FOR EACH ROW EXECUTE FUNCTION valid_id(); </programlisting> </para> </sect1> @@ -1350,7 +1350,7 @@ $$ LANGUAGE plperl; CREATE EVENT TRIGGER perl_a_snitch ON ddl_command_start - EXECUTE PROCEDURE perlsnitch(); + EXECUTE FUNCTION perlsnitch(); </programlisting> </para> </sect1> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 103988f4c5..d39a38e39d 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -4069,7 +4069,7 @@ CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp - FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); + FOR EACH ROW EXECUTE FUNCTION emp_stamp(); </programlisting> </example> @@ -4124,7 +4124,7 @@ $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp - FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); + FOR EACH ROW EXECUTE FUNCTION process_emp_audit(); </programlisting> </example> @@ -4203,7 +4203,7 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view - FOR EACH ROW EXECUTE PROCEDURE update_emp_view(); + FOR EACH ROW EXECUTE FUNCTION update_emp_view(); </programlisting> </example> @@ -4348,7 +4348,7 @@ $maint_sales_summary_bytime$ LANGUAGE plpgsql; CREATE TRIGGER maint_sales_summary_bytime AFTER INSERT OR UPDATE OR DELETE ON sales_fact - FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime(); + FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime(); INSERT INTO sales_fact VALUES(1,1,1,10,3,15); INSERT INTO sales_fact VALUES(1,2,1,20,5,35); @@ -4425,15 +4425,15 @@ $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit_ins AFTER INSERT ON emp REFERENCING NEW TABLE AS new_table - FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit(); + FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit(); CREATE TRIGGER emp_audit_upd AFTER UPDATE ON emp REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table - FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit(); + FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit(); CREATE TRIGGER emp_audit_del AFTER DELETE ON emp REFERENCING OLD TABLE AS old_table - FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit(); + FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit(); </programlisting> </example> @@ -4498,7 +4498,7 @@ BEGIN END; $$ LANGUAGE plpgsql; -CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch(); +CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch(); </programlisting> </example> </sect2> diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 0d4abfec7e..4dd6fe434f 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -789,7 +789,7 @@ $$ LANGUAGE pltcl; CREATE TABLE mytab (num integer, description text, modcnt integer); CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab - FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt'); + FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt'); </programlisting> Notice that the trigger function itself does not know the column @@ -852,7 +852,7 @@ CREATE OR REPLACE FUNCTION tclsnitch() RETURNS event_trigger AS $$ elog NOTICE "tclsnitch: $TG_event $TG_tag" $$ LANGUAGE pltcl; -CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE PROCEDURE tclsnitch(); +CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE FUNCTION tclsnitch(); </programlisting> </para> </sect1> diff --git a/doc/src/sgml/ref/create_event_trigger.sgml b/doc/src/sgml/ref/create_event_trigger.sgml index 396d82118e..52ba746166 100644 --- a/doc/src/sgml/ref/create_event_trigger.sgml +++ b/doc/src/sgml/ref/create_event_trigger.sgml @@ -24,7 +24,7 @@ PostgreSQL documentation CREATE EVENT TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">event</replaceable> [ WHEN <replaceable class="parameter">filter_variable</replaceable> IN (filter_value [, ... ]) [ AND ... ] ] - EXECUTE PROCEDURE <replaceable class="parameter">function_name</replaceable>() + EXECUTE { FUNCTION | PROCEDURE } <replaceable class="parameter">function_name</replaceable>() </synopsis> </refsynopsisdiv> @@ -98,6 +98,14 @@ CREATE EVENT TRIGGER <replaceable class="parameter">name</replaceable> A user-supplied function that is declared as taking no argument and returning type <literal>event_trigger</literal>. </para> + + <para> + In the syntax of <literal>CREATE EVENT TRIGGER</literal>, the keywords + <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are + equivalent, but the referenced function must in any case be a function, + not a procedure. The use of the keyword <literal>PROCEDURE</literal> + here is historical and deprecated. + </para> </listitem> </varlistentry> @@ -136,7 +144,7 @@ END; $$; CREATE EVENT TRIGGER abort_ddl ON ddl_command_start - EXECUTE PROCEDURE abort_any_command(); + EXECUTE FUNCTION abort_any_command(); </programlisting></para> </refsect1> diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index b2dddafb46..6514ffc6ae 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -33,7 +33,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> [ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ] - EXECUTE PROCEDURE <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> ) + EXECUTE { FUNCTION | PROCEDURE } <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> ) <phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase> @@ -401,6 +401,14 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ and returning type <literal>trigger</literal>, which is executed when the trigger fires. </para> + + <para> + In the syntax of <literal>CREATE TRIGGER</literal>, the keywords + <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are + equivalent, but the referenced function must in any case be a function, + not a procedure. The use of the keyword <literal>PROCEDURE</literal> + here is historical and deprecated. + </para> </listitem> </varlistentry> @@ -555,7 +563,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW - EXECUTE PROCEDURE check_account_update(); + EXECUTE FUNCTION check_account_update(); </programlisting> The same, but only execute the function if column <literal>balance</literal> @@ -565,7 +573,7 @@ CREATE TRIGGER check_update CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW - EXECUTE PROCEDURE check_account_update(); + EXECUTE FUNCTION check_account_update(); </programlisting> This form only executes the function if column <literal>balance</literal> @@ -576,7 +584,7 @@ CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) - EXECUTE PROCEDURE check_account_update(); + EXECUTE FUNCTION check_account_update(); </programlisting> Call a function to log updates of <literal>accounts</literal>, but only if @@ -587,7 +595,7 @@ CREATE TRIGGER log_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) - EXECUTE PROCEDURE log_account_update(); + EXECUTE FUNCTION log_account_update(); </programlisting> Execute the function <function>view_insert_row</function> for each row to insert @@ -597,7 +605,7 @@ CREATE TRIGGER log_update CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW - EXECUTE PROCEDURE view_insert_row(); + EXECUTE FUNCTION view_insert_row(); </programlisting> Execute the function <function>check_transfer_balances_to_zero</function> for each @@ -609,7 +617,7 @@ CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT - EXECUTE PROCEDURE check_transfer_balances_to_zero(); + EXECUTE FUNCTION check_transfer_balances_to_zero(); </programlisting> Execute the function <function>check_matching_pairs</function> for each row to @@ -621,7 +629,7 @@ CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW - EXECUTE PROCEDURE check_matching_pairs(); + EXECUTE FUNCTION check_matching_pairs(); </programlisting> </para> diff --git a/doc/src/sgml/tcn.sgml b/doc/src/sgml/tcn.sgml index 8cc55efd29..aa2fe4f00a 100644 --- a/doc/src/sgml/tcn.sgml +++ b/doc/src/sgml/tcn.sgml @@ -47,7 +47,7 @@ test(# ); CREATE TABLE test=# create trigger tcndata_tcn_trigger test-# after insert or update or delete on tcndata -test-# for each row execute procedure triggered_change_notification(); +test-# for each row execute function triggered_change_notification(); CREATE TRIGGER test=# listen tcn; LISTEN diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml index 6df424c63e..800a94bb54 100644 --- a/doc/src/sgml/textsearch.sgml +++ b/doc/src/sgml/textsearch.sgml @@ -1884,7 +1884,7 @@ CREATE TABLE messages ( ); CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE -ON messages FOR EACH ROW EXECUTE PROCEDURE +ON messages FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(tsv, 'pg_catalog.english', title, body); INSERT INTO messages VALUES('title here', 'the body text is here'); @@ -1940,7 +1940,7 @@ end $$ LANGUAGE plpgsql; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE - ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger(); + ON messages FOR EACH ROW EXECUTE FUNCTION messages_trigger(); </programlisting> </para> diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index c43dbc9786..be9c228448 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -871,10 +871,10 @@ CREATE FUNCTION trigf() RETURNS trigger LANGUAGE C; CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest - FOR EACH ROW EXECUTE PROCEDURE trigf(); + FOR EACH ROW EXECUTE FUNCTION trigf(); CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest - FOR EACH ROW EXECUTE PROCEDURE trigf(); + FOR EACH ROW EXECUTE FUNCTION trigf(); </programlisting> </para> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 87f5e95827..4bd2223f26 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -5344,7 +5344,7 @@ CreateAmStmt: CREATE ACCESS METHOD name TYPE_P INDEX HANDLER handler_name CreateTrigStmt: CREATE TRIGGER name TriggerActionTime TriggerEvents ON qualified_name TriggerReferencing TriggerForSpec TriggerWhen - EXECUTE PROCEDURE func_name '(' TriggerFuncArgs ')' + EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $3; @@ -5366,7 +5366,7 @@ CreateTrigStmt: | CREATE CONSTRAINT TRIGGER name AFTER TriggerEvents ON qualified_name OptConstrFromTable ConstraintAttributeSpec FOR EACH ROW TriggerWhen - EXECUTE PROCEDURE func_name '(' TriggerFuncArgs ')' + EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')' { CreateTrigStmt *n = makeNode(CreateTrigStmt); n->trigname = $4; @@ -5504,6 +5504,11 @@ TriggerWhen: | /*EMPTY*/ { $$ = NULL; } ; +FUNCTION_or_PROCEDURE: + FUNCTION + | PROCEDURE + ; + TriggerFuncArgs: TriggerFuncArg { $$ = list_make1($1); } | TriggerFuncArgs ',' TriggerFuncArg { $$ = lappend($1, $3); } diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index e57c6e1c42..7d59de98eb 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -22,12 +22,12 @@ create unique index pkeys_i on pkeys (pkey1, pkey2); create trigger check_fkeys_pkey_exist before insert or update on fkeys for each row - execute procedure + execute function check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); create trigger check_fkeys_pkey2_exist before insert or update on fkeys for each row - execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); + execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); -- -- For fkeys2: -- (fkey21, fkey22) --> pkeys (pkey1, pkey2) diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 1250cd63e0..d7dfd753be 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -26,13 +26,13 @@ create unique index pkeys_i on pkeys (pkey1, pkey2); create trigger check_fkeys_pkey_exist before insert or update on fkeys for each row - execute procedure + execute function check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); create trigger check_fkeys_pkey2_exist before insert or update on fkeys for each row - execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); + execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); -- -- For fkeys2: |