Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
CREATE RULE(7)		PostgreSQL 9.6.3 Documentation		CREATE RULE(7)

       CREATE_RULE - define a new rewrite rule

       CREATE [	OR REPLACE ] RULE name AS ON event
	   TO table_name [ WHERE condition ]
	   DO [	ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

       where event can be one of:


       CREATE RULE defines a new rule applying to a specified table or view.
       CREATE OR REPLACE RULE will either create a new rule, or	replace	an
       existing	rule of	the same name for the same table.

       The PostgreSQL rule system allows one to	define an alternative action
       to be performed on insertions, updates, or deletions in database
       tables. Roughly speaking, a rule	causes additional commands to be
       executed	when a given command on	a given	table is executed.
       Alternatively, an INSTEAD rule can replace a given command by another,
       or cause	a command not to be executed at	all. Rules are used to
       implement SQL views as well. It is important to realize that a rule is
       really a	command	transformation mechanism, or command macro. The
       transformation happens before the execution of the command starts. If
       you actually want an operation that fires independently for each
       physical	row, you probably want to use a	trigger, not a rule. More
       information about the rules system is in	Chapter	39, The	Rule System,
       in the documentation.

       Presently, ON SELECT rules must be unconditional	INSTEAD	rules and must
       have actions that consist of a single SELECT command. Thus, an ON
       SELECT rule effectively turns the table into a view, whose visible
       contents	are the	rows returned by the rule's SELECT command rather than
       whatever	had been stored	in the table (if anything). It is considered
       better style to write a CREATE VIEW command than	to create a real table
       and define an ON	SELECT rule for	it.

       You can create the illusion of an updatable view	by defining ON INSERT,
       ON UPDATE, and ON DELETE	rules (or any subset of	those that's
       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.

       There is	a catch	if you try to use conditional rules for	complex	view
       updates:	there must be an unconditional INSTEAD rule for	each action
       you wish	to allow on the	view. If the rule is conditional, or is	not
       INSTEAD,	then the system	will still reject attempts to perform the
       update action, because it thinks	it might end up	trying to perform the
       action on the dummy table of the	view in	some cases. If you want	to
       handle all the useful cases in conditional rules, add an	unconditional
       DO INSTEAD NOTHING rule to ensure that the system understands it	will
       never be	called on to update the	dummy table. Then make the conditional
       rules non-INSTEAD; in the cases where they are applied, they add	to the
       default INSTEAD NOTHING action. (This method does not currently work to
       support RETURNING queries, however.)

	   A view that is simple enough	to be automatically updatable (see
	   CREATE VIEW (CREATE_VIEW(7))) does not require a user-created rule
	   in order to be updatable. While you can create an explicit rule
	   anyway, the automatic update	transformation will generally
	   outperform an explicit rule.

	   Another alternative worth considering is to use INSTEAD OF triggers
	   (see	CREATE TRIGGER (CREATE_TRIGGER(7))) in place of	rules.

	   The name of a rule to create. This must be distinct from the	name
	   of any other	rule for the same table. Multiple rules	on the same
	   table and same event	type are applied in alphabetical name order.

	   The event is	one of SELECT, INSERT, UPDATE, or DELETE. Note that an
	   INSERT containing an	ON CONFLICT clause cannot be used on tables
	   that	have either INSERT or UPDATE rules. Consider using an
	   updatable view instead.

	   The name (optionally	schema-qualified) of the table or view the
	   rule	applies	to.

	   Any SQL conditional expression (returning boolean). The condition
	   expression cannot refer to any tables except	NEW and	OLD, and
	   cannot contain aggregate functions.

	   INSTEAD indicates that the commands should be executed instead of
	   the original	command.

	   ALSO	indicates that the commands should be executed in addition to
	   the original	command.

	   If neither ALSO nor INSTEAD is specified, ALSO is the default.

	   The command or commands that	make up	the rule action. Valid

       Within condition	and command, the special table names NEW and OLD can
       be used to refer	to values in the referenced table.  NEW	is valid in ON
       INSERT and ON UPDATE rules to refer to the new row being	inserted or
       updated.	 OLD is	valid in ON UPDATE and ON DELETE rules to refer	to the
       existing	row being updated or deleted.

       You must	be the owner of	a table	to create or change rules for it.

       In a rule for INSERT, UPDATE, or	DELETE on a view, you can add a
       RETURNING clause	that emits the view's columns. This clause will	be
       used to compute the outputs if the rule is triggered by an INSERT
       When the	rule is	triggered by a command without RETURNING, the rule's
       RETURNING clause	will be	ignored. The current implementation allows
       only unconditional INSTEAD rules	to contain RETURNING; furthermore
       there can be at most one	RETURNING clause among all the rules for the
       same event. (This ensures that there is only one	candidate RETURNING
       clause to be used to compute the	results.)  RETURNING queries on	the
       view will be rejected if	there is no RETURNING clause in	any available

       It is very important to take care to avoid circular rules. For example,
       though each of the following two	rule definitions are accepted by
       PostgreSQL, the SELECT command would cause PostgreSQL to	report an
       error because of	recursive expansion of a rule:

	       ON SELECT TO t1
	       DO INSTEAD
		   SELECT * FROM t2;

	       ON SELECT TO t2
	       DO INSTEAD
		   SELECT * FROM t1;

	   SELECT * FROM t1;

       Presently, if a rule action contains a NOTIFY command, the NOTIFY
       command will be executed	unconditionally, that is, the NOTIFY will be
       issued even if there are	not any	rows that the rule should apply	to.
       For example, in:

	   CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY	mytable;

	   UPDATE mytable SET name = 'foo' WHERE id = 42;

       one NOTIFY event	will be	sent during the	UPDATE,	whether	or not there
       are any rows that match the condition id	= 42. This is an
       implementation restriction that might be	fixed in future	releases.

       CREATE RULE is a	PostgreSQL language extension, as is the entire	query
       rewrite system.


PostgreSQL 9.6.3		     2017			CREATE RULE(7)


Want to link to this manual page? Use this URL:

home | help