rules ON SELECT

Hi,

I'm currently thinking about multiple action and non-INSTEAD
rules ON SELECT. I'm not sure what users might expect when
they get fired.

Well if a user types SELECT ... FROM tab and there are rules
ON SELECT TO tab, then of course. But what about if the user
issues an INSERT INTO x SELECT ... FROM tab or an UPDATE x
SET col = tab.attr? In fact tab is scanned and returns data.
Should the rule ON SELECT then be fired too?

And what the hell is all that good for? Do we need other
rules ON SELECT than those that build views (which we have
now)? Tracing which data one user uses? Cannot be what rules
are made for.

I would guess that SELECT ... FROM tab and INSERT INTO x SELECT ... FROM
tab would use the rules, but I can see where and UPDATE is using the
data from the table just like a SELECT, so I can see it would be
confusing for some if it DID do the rule in that case, and for others if
it DID NOT use the rule. That is a no-win situation, and usually means
that there is a bad interface design. However, in our case, it is just
a "much too powerful" interface design.

Please use your judgement. I can see that you are saying that there
really is no reason for SELECT rules except to create views. However,
if someone wants a computed column as part of a table, AND they want the
table name to be the same, isn't a SELECT rule the only way to do that.
They could of course rename the table, and create a view with the old
name. This may be the clearest way to do it.

I think if you make it consistent and/or print something to the user
when they try and do something strange, that usually keeps people happy.
If they understand what it is doing, the will usually accept it,
especially if it is a SUPERSET of SQL.

If nobody votes against, I would only add some code checking
that there is at max one INSTEAD SELECT rule that returns
exactly the relations tuple structure ON SELECT (currently
with CREATE TABLE, CREATE RULE someone can setup a situation
that crashes the backend on SELECT). So SELECT rules are
totally restricted to view building and nothing else.

After that I'll tidy up the rewrite code (the work I've done
screwed it up a little with nearly duplicate functions).
Anything except for bug fixing is then delayed for 6.5.

I still have in mind that we wanted to have views of UNIONS,
DISTINCT views and some more. But since they require totally
different semantics (the resulting plan must have something
like a subselect of union in the case of an UPDATE...) this
is far too much and has bad bad traps deep inside. We all
don't want to fall into one during BETA.

Sounds good to me.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026