Pierre-Frédéric Caillaud wrote:
> - Change the name of your table to "hidden_table"
>
> - Create a view which is a duplicate of your table :
> CREATE VIEW visible_table AS SELECT * FROM hidden_table;
>
> -> Your application now accesses its data without realizing it goes
> through a view.
>
> Now create a rule on this view, to make it update the real
> hidden_table. As the rule does not apply to hidden_table, it won't
> recurse.
>
> Other solution (this similar to what Tom Lane proposed I think) :
>
> Create a field common_id in your table, with
> - an insert trigger which puts a SERIAL default value if there is no
> parent, or copies the parent's value if there is one
> - an update trigger to copy the new parent's common_id whenever a child
> changes parent (if this ever occurs in your design)
>
> Now create another table linking common_id to the 'common' value.
>
> Create a view which joins the two, which emulates your current behaviour.
> Create an ON UPDATE rule to the view which just changes one row in the
> link table.
>
> If you do a lot of selects, solution #1 will be faster, if you do a lot
> of updates, #2 will win...
The "hidden table" method should work just fine. Ingenius idea, thank you!
> Just out of curiosity, what is this for ?
The actual application has companies instead of parents, employees
instead of children, then emails as children of employees and/or
companies, and folders as parents of companies and employees. The
"common" field (in all 4 layers) are a pair of permissions flags.
May I humbly suggest two possible todo's for postgreSQL: a simple flag
to suppress recursion (easier/more powerful way of doing the above),
and/or more direct access to query rewriting. Seems right now rules
require you to rewrite queries while partially blind to them. Being able
to rewrite queries in statement triggers similar to what can be done
with row triggers would be very nice too.