Chemistry Reference
In-Depth Information
($1 % 1000 / 100 * 5 ) +
($1 % 10000 / 1000 * 6 ) +
($1 % 100000 / 10000 * 7 ) +
($1 % 1000000 / 100000 * 8 )
) % 10;' Language SQL;
This function, in conjunction with a CAS number-parsing function can be
used to define a check constraint on a column of CAS numbers as follows.
Alter Table compound Add Constraint cas_check
Check (valid_cas(caspart(cas,1), caspart(cas,2), caspart(cas,3));
The definition of
caspart
is not shown here, but could be written in any
language supported by PostgreSQL. The
caspart
function would parse
the CAS number into each of its three integer parts.
The point of this exercise is not to emphasize any particular impor-
tance of CAS numbers. In some applications, they may be stored simply
as a string for reference, or even be of no interest at all. On the other hand,
a corporate
compound _ id
number may be very important. Often cor-
porate ids are compound strings, for example, encoding the occurrence
of various salts, or the acquisition of the compound from some external
source. Functions similar to those shown above would be required to
implement a check constraint.
It is a good idea to apply sensible constraints on data in order to ensure
data integrity. These constraints can prevent errors and simplify the pro-
cessing of data stored in the database. While there is some overhead using
a check constraint, it applies only when the data are inserted or updated.
6.6 Developing Complex SQL
Many SQL statements are simple. Some statements can grow in size and
become slightly more complex. For example, one might select dozens of col-
umns from a table using a sizable, but simple
select
statement. Some of
the columns might include a
where
clause, but this is not the kind of com-
plexity that requires careful thought when constructing the SQL statement.
On the other hand, a
select
statement can become quite complex, involv-
ing joining many tables with associated
where
clauses. It is very common
to forget a
join
condition resulting in many more rows than anticipated.
Rather than try to write complex SQL statements in one attempt, it is worth-
while to approach this systematically, as if writing a computer program.
Traditional computer languages combine sequential shorter state-
ments or program lines to produce a result. Of course, it is possible to write
such functions using SQL or using a variety of procedural languages such
as plpgsql or plperl. But even a single SQL statement can become com-
plex enough that it requires “writing” as if it were a function or program.
Search WWH ::
Custom Search