Rule system goes weird with SELECT queries - Mailing list pgsql-hackers

I must admit I'm trying to (ab)use the rule system into
being a stored-procedure system, so maybe I'm just getting
what I deserve.  However, the results I'm getting are
just plain weird.

If I define two rules for the same action, each with 
a single select command, I wind up with two selects as
expected, but they are both cross-product selects on the
two tables. This is unexpected.

If I change the grammar rules so that I can have a
compound action with two selects, I get two selects,
each effectively the four-times cross-product of
the selects.  Talk about exponential growth!!

Now I can see why compound SELECTs were disallowed.
And I can guess why my two separate rules behaved this
way, sort of.  But if I'm right, the rules are being
processed by the planner once on creation and again when
being invoked, and something is not quite right about
it.

But: does anyone else see a need for a stored-procedure
facility, different from function definition?  I'm
probably going to do it anyway, but if there's support
for the idea, I will try to make it conform to the
standards of the community.  In return for a little
guidance on that subject.

Here are the details (all tables initially empty):

Form 1: two separate rules gives two cross-products.

create rule rule4a as on insert to dummy do instead select * from d2;
create rule rule4b as on insert to dummy do instead select * from d3;
explain insert into dummy values(1);

psql:rule4.sql:14: NOTICE:  QUERY PLAN:
Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8) ->  Seq Scan on d3  (cost=0.00..20.00 rows=1000 width=4) ->
SeqScan on d2  (cost=0.00..20.00 rows=1000 width=4)
 
psql:rule4.sql:14: NOTICE:  QUERY PLAN:
Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8) ->  Seq Scan on d2  (cost=0.00..20.00 rows=1000 width=4) ->
SeqScan on d3  (cost=0.00..20.00 rows=1000 width=4)
 

EXPLAIN                                                                                      
Form 2: single rule with two SELECT commands gives something
quite weird apparently a quadruple cross-product, performed
twice:

create rule rule3 as on insert to dummy do instead (select * from d2;
select * from d3;);
explain insert into dummy values(1);

psql:rule3.sql:13: NOTICE:  QUERY PLAN:
Nested Loop  (cost=0.00..30030030020.00 rows=1000000000000 width=16) ->  Nested Loop  (cost=0.00..30030020.00
rows=1000000000width=12)       ->  Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8)             ->  Seq Scan on
d2 (cost=0.00..20.00 rows=1000 width=4)             ->  Seq Scan on d3  (cost=0.00..20.00 rows=1000 width=4)       ->
SeqScan on d3  (cost=0.00..20.00 rows=1000 width=4) ->  Seq Scan on d2  (cost=0.00..20.00 rows=1000 width=4)
 
psql:rule3.sql:13: NOTICE:  QUERY PLAN:
Nested Loop  (cost=0.00..30030030020.00 rows=1000000000000 width=16) ->  Nested Loop  (cost=0.00..30030020.00
rows=1000000000width=12)       ->  Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8)             ->  Seq Scan on
d2 (cost=0.00..20.00 rows=1000 width=4)             ->  Seq Scan on d3  (cost=0.00..20.00 rows=1000 width=4)       ->
SeqScan on d3  (cost=0.00..20.00 rows=1000 width=4) ->  Seq Scan on d2  (cost=0.00..20.00 rows=1000
 
width=4)                             

EXPLAIN

-- 
Kevin O'Gorman  (805) 650-6274  mailto:kogorman@pacbell.net
Permanent e-mail forwarder:  mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"   -- Alfred North Whitehead