Re: RULE vs. SEQUENCE - Mailing list pgsql-hackers

Karel Zak wrote:
>
> On Mon, 4 Sep 2000, Jan Wieck wrote:
>
> >     The problem is, that NEW.attname in a rule  means,  "whatever
> >     is  in  the targetlist of the INSERT when applying the rule".
> >     In your example, it'll be  a  call  to  nextval().  The  rule
> >     system  doesn't  know  that  this targetlist expression has a
> >     side-effect (incrementing the sequence).
>
>  But, why 'NEW' tuple is in the rewriter created again, why is not used
> original tuple from original statement ... like in triggers?
>
>  Ooops yes, rewriter is before executor...hmm...
   More  Ooops:  the  rewriter  doesn't  create  any  tuples. He   creates another query tree, which is then optimized,
planned   and finally executed (to produce tuples).
 

>
> >     Thus, the rule creates a second query  which  does  it's  own
> >     calls to nextval() when executed.
>
>  But executor can knows that somethig was already executed, we can mark
> some already executed expressions in rewriter and not execute it again in
> final executor... like:
>
> typedef  some_expr {
>    bool executed;
>    Datum     *result;
>    ....
> } some_expr;
>
>
>  IMHO this is a good point for 7.2 ...
   Impossible - period.
   Think about this (a little longer - sorry):
       CREATE TABLE category (           cat_id        serial,           cat_name      text       );
       CREATE TABLE prod_attrs (           pa_prodid     integer,           pa_attkey     integer,           pa_attval
  text       );
 
       CREATE TABLE prod_attdefaults (           pdef_catid    integer,           pdef_attkey   integer,
pdef_attval  text,       );
 
       CREATE TABLE product (           prod_id       serial,           prod_category integer,           prod_name
text      );
 
       CREATE TABLE new_products (           new_category  integer,           new_name      text       );
   So  far,  so  good. For each product we store in "product", a   variable number of attributes can be stored in
"prod_attrs".  At   the  time  of  "INSERT  INTO  product",  the  rows  from   "prod_attdefaults"  where  "pdef_catid
= NEW.prod_category"   should be copied into "prod_attrs".
 
   The "NOT WORKING" rule for doing so would look like
       CREATE RULE attdefaults AS ON INSERT TO product DO           INSERT INTO prod_attrs           SELECT
NEW.prod_id,D.pdef_attkey, D.pdef_attval             FROM prod_attdefaults D             WHERE D.pdef_catid =
NEW.prod_category;
   Now let's have in "prod_attdefaults" 7 rows for category 1, 5   rows for category 2, 6 rows for category 3 and  no
rows for   category 4. And we do
 
       INSERT INTO new_products VALUES (1, 'chair');       INSERT INTO new_products VALUES (1, 'table');       INSERT
INTOnew_products VALUES (1, 'sofa');       INSERT INTO new_products VALUES (1, 'cupboard');       INSERT INTO
new_productsVALUES (2, 'shirt');       INSERT INTO new_products VALUES (2, 'shoe');       INSERT INTO new_products
VALUES(3, 'butter');       INSERT INTO new_products VALUES (4, 'shampoo');
 
   The query
       INSERT INTO product (prod_category, prod_name)           SELECT new_category, new_name FROM new_product;
   must  then  create  8  new  rows  in "product" and 44 rows in   "prod_attrs".  The first 7 with the nextval()
allocated for   the  chair, the next 7 with the nextval() for the table, etc.
 
   I can't see how this should be doable with  the  rewriter  on   the querylevel.
   This is something for a trigger.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #