#Postgresql insert into view update#

The resulting rewritten query tree is almost the same as if you had typed: 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. When processing the range table entry for shoelace (the only one up to now) it finds the _RETURN rule with the query tree: The rule system walks through the range table and checks if there are rules for any relation. SELECT shoelace.sl_name, shoelace.sl_avail,Īnd this is given to the rule system. The SELECT * FROM shoelace was interpreted by the parser and produced the query tree:

INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40, 'inch') INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60, 'cm') INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9, 'm') INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0, 'm') INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0, 'inch') INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0, 'inch') INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm') INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm') Now we populate unit, shoe_data and shoelace_data and run a simple query on a view: The two extra range table entries for NEW and OLD that you can see in the pg_rewrite entry aren't of interest for SELECT rules. The action of the rule is one query tree that is a copy of the SELECT statement in the view creation command. The action of our rule has a query qualification.

Note that rule qualifications are not the same as query qualifications. The rule has no rule qualification (discussed later, with the non- SELECT rules, since SELECT rules currently cannot have them) and it is INSTEAD. The CREATE VIEW command for the shoelace view (which is the simplest one we have) will create a relation shoelace and an entry in pg_rewrite that tells that there is a rewrite rule that must be applied whenever the relation shoelace is referenced in a query's range table. Least(rsh.sh_avail, rsl.sl_avail) AS total_avail Un_fact real - factor to transform to cmĪs you can see, they represent shoe-store data. Sl_avail integer, - available number of pairs Sh_avail integer, - available number of pairs The real tables we need in the first two rule system descriptions are these: But it's better to have one example that covers all the points discussed step by step rather than having many different ones that might mix up in mind. This is not such a simple example to start from and this makes things harder to get into. One of the two first views is customized later by adding rules for INSERT, UPDATE, and DELETE operations so that the final result will be a view that behaves like a real table with some magic functionality. The examples for this chapter are two join views that do some calculations and some more views using them in turn. This restriction was required to make rules safe enough to open them for ordinary users, and it restricts ON SELECT rules to act like views. So SELECT rules are described first.Ĭurrently, there can be only one action in an ON SELECT rule, and it must be an unconditional SELECT action that is INSTEAD. And they have different semantics from rules on the other command types in that they modify the query tree in place instead of creating a new one.

#Postgresql insert into view update#

Rules ON SELECT are applied to all queries as the last step, even if the command given is an INSERT, UPDATE or DELETE.