Обсуждение: Triggers... Questions... Yes.
I just used Dezign for Databases and created a simple table in what it claimed was "ANSI Level 2" compliance with a one-up "autonum" column. It spewed forth the following code: -- file generated by "dezign for databases" -- create-date :6/13/2002 -- create-time :10:29:56 am -- project-name :not yet specified -- project-author :not yet specified create table transaction(autonum integer); create trigger transaction_autonum_inc before insert on transaction referencing new as n for each row set (n.autonum) = (select (max(autonum),0) + 1 from transaction); Postgres (as you might suspect) didn't like it. Looking at the Postgres "create trigger" syntax, there is nothing about "referencing X as Y" being a possible phrase. Is the problem in: 1. Dezign for Databases, this really isn't ANSI Level 2, 2. Postgres, it doesn't (yet) support this level of functionality, but should, or 3. Me, I'm mixing up ANSI Level 2 and ANSI SQL '92, Postgres doesn't now and has no plans to support ANSI Level 2 SQL ??? Thanks, Tim Ellis DBA, Gamet
On Thu, 13 Jun 2002, Tim Ellis wrote: > I just used Dezign for Databases and created a simple table in what it > claimed was "ANSI Level 2" compliance with a one-up "autonum" column. > > It spewed forth the following code: > > -- file generated by "dezign for databases" > -- create-date :6/13/2002 > -- create-time :10:29:56 am > -- project-name :not yet specified > -- project-author :not yet specified > > create table transaction(autonum integer); > > create trigger transaction_autonum_inc > before insert on transaction > referencing new as n > for each row > set (n.autonum) = (select (max(autonum),0) + 1 from transaction); > > Postgres (as you might suspect) didn't like it. Looking at the Postgres > "create trigger" syntax, there is nothing about "referencing X as Y" being > a possible phrase. > > Is the problem in: > > 1. Dezign for Databases, this really isn't ANSI Level 2, > 2. Postgres, it doesn't (yet) support this level of functionality, but > should, or > 3. Me, I'm mixing up ANSI Level 2 and ANSI SQL '92, Postgres doesn't now > and has no plans to support ANSI Level 2 SQL IIRC, SQL92 doesn't have triggers. I believe the above is SQL 99 syntax and it looks like triggers aren't Core SQL99 either. PostgreSQL may eventually take the full SQL99 syntax but it's likely to be a while unless someone wants it enough to work on it.
Tim Ellis <Tim.Ellis@gamet.com> writes:
> I just used Dezign for Databases and created a simple table in what it
> claimed was "ANSI Level 2" compliance with a one-up "autonum" column.
There is no such animal as "ANSI Level 2" SQL.
> create trigger transaction_autonum_inc
> before insert on transaction
> referencing new as n
> for each row
> set (n.autonum) = (select (max(autonum),0) + 1 from transaction);
Looking at SQL99 (that's ISO/IEC 9075-2:1999, Part 2: SQL/Foundation
if we have to get pedantic about it), there is something about a
"referencing" clause, which we do not have. However, the "set" command
shown here does not appear to be legal according to SQL99, so I'm not
planning to get excited about not having "referencing". There are a
number of missing features in our trigger stuff, and that one seems
right at the bottom of the list as far as adding useful functionality
goes.
BTW, what are they expecting "(max(autonum),0)" to do, other than draw a
syntax error? Perhaps there was supposed to be a COALESCE there?
Even if it worked or were standards-compliant, this approach to
implementing an autonumbering column would be brain dead in the extreme
--- you do *not* want to do a max() aggregate for every insert. Use
a sequence object instead...
regards, tom lane
> > I just used Dezign for Databases and created a simple table in what it > > claimed was "ANSI Level 2" compliance with a one-up "autonum" column. > > There is no such animal as "ANSI Level 2" SQL. That explains a lot of things. About this DDL problem and Dezign for Databases both. > > set (n.autonum) = (select (max(autonum),0) + 1 from transaction); > > BTW, what are they expecting "(max(autonum),0)" to do, other than draw a > syntax error? Perhaps there was supposed to be a COALESCE there? Yeh. Good question. I would assume their generator MEANT (max(autonum,0)+1) supposing that a "ANSI Level 2"-compliant server's max function might return the second value if the first is NULL. But no matter how you slice it, that looks syntactically wrong. > Even if it worked or were standards-compliant, this approach to > implementing an autonumbering column would be brain dead in the extreme > --- you do *not* want to do a max() aggregate for every insert. Use > a sequence object instead... Perhaps. Is the sequence object part of standard SQL? I know Postgres and Oracle have it (among others) but I also know of at least one major recent-version RDBMS that fails to have a sequence object. On the topic of max(x), there are certain RDBMSs that implement a max(x) in such a way that the optimiser will, supposing x has an index on it, use the index to determine max(x) rather than a table scan. I noticed during porting an from Sybase to Postgres this particular problem when my select max(x) from tableY queries were running very slowly. -- Tim Ellis DBA, Gamet
Tim Ellis <Tim.Ellis@gamet.com> writes:
> Yeh. Good question. I would assume their generator MEANT
> (max(autonum,0)+1) supposing that a "ANSI Level 2"-compliant server's
> max function might return the second value if the first is NULL. But no
> matter how you slice it, that looks syntactically wrong.
I suspected they meant COALESCE(max(autonum),0) + 1, which would be
correct since max() on no values is defined to produce NULL. I'm
not aware of any variant of max() that takes two arguments.
> Perhaps. Is the sequence object part of standard SQL? I know Postgres and
> Oracle have it (among others) but I also know of at least one major
> recent-version RDBMS that fails to have a sequence object.
It is not standard. On the other hand, expecting people to have
implemented every last frammish in SQL99 is a bit premature also...
SQL92 didn't have triggers at all.
regards, tom lane