Re: Is relational theory irrelevant?

"Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message news:bpgef8$gp6$1_at_hanover.torolab.ibm.com...
> Mikito Harakiri wrote:
>
> > "Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message
> > news:bpfunr$dic$1_at_hanover.torolab.ibm.com...
> >
> >>The syntax is as follows:
> >>SELECT .. FROM OLD TABLE(DELETE FROM ...)
> >>
> >>OLD TABLE being the set of deleted rows
> >
> >
> > May I ask you how is your single statement it different from the
following
> > sequence:
> >
> > SELECT * FROM T;
> > DELETE FROM T;
> Depending on your isolation level it will give you different results or
> deadlocks. Not to speak of the fact that you have to read the data twice
> which is a performance problem.

Correction:

SELECT * FROM T FOR UPDATE;
DELETE FROM T;
COMMIT;
> >>Example:
> >>WITH del AS (SELECT * FROM OLD TABLE(DELETE FROM S))
> >>SELECT count(*) FROM NEW TABLE(INSERT INTO T SELECT * FROM del);
> >
> >
> > And how is it different from:
> >
> > INSERT INTO T as
> > SELECT * FROM S;
> > SELECT count(*) FROM S;
> > DELETE FROM S;
> So what happens if after my INSERT another connection deletes/inserts
> some rows in S?
> I will get wrong COUNT(*).

INSERT INTO T as
SELECT * FROM S FOR UPDATE;
SELECT count(*) FROM S;
DELETE FROM S;
COMMIT;
> But let me give you another example
> (it requires the acceptance of surrogate keys as a fact of life):
> CREATE TABLE T(PK, C1 INT, C2 INT);
> CREATE TRIGGER BEFORE INSERT ON T REFERENCING NEW ROW AS n FOR EACH ROW
> SET n.pk = (SELECT COALESCE(MAX(pk), 0) + 1 FROM T);
>
> INSERT INTO T(c1, c2) VALUES (5, 6);
> What is your new primary key?
> You will never know....
> When you get a customer reference number where does it come from?

No trigger is necessary:

SELECT COALESCE(MAX(c1), 0) + 1 into :x FROM T; insert into T(c1, c2) values (:x, 6);

> > I guess "more" orthogonality argument from the flame wars with your
> > competitor is long forgotten.
> To me if I enable existing fucntionality (UPDATE, DELETE, INSERT and NEW
> TABLE, OLD TABLE) in another existing feature (SELECT FROM) using
> existing methods (SETS), that is indeed an increase in orthogonality.
> What is your point?

May I ask you what

SELECT * FROM OLD TABLE(DELETE FROM T)
union
SELECT * FROM T returns? Then, how about

SELECT * FROM OLD TABLE(DELETE FROM (
   SELECT * FROM NEW TABLE(DELETE FROM T) )

Ah, you don't allow features in such combinations? Since combining orthogonal features is never a problem, then what exactly do you mean "we increased orthodonality with new syntax"? Received on Wed Nov 19 2003 - 21:23:17 CET