Re: RULE vs. SEQUENCE

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 INTO new_products VALUES (1, 'sofa');
INSERT INTO new_products VALUES (1, 'cupboard');
INSERT INTO new_products VALUES (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(at)Yahoo(dot)com #