PATCH proposed with new features for CREATE TABLE

Hi people!.I  developed a work for an university course, which I wish to share with you.

I extended the foreign key clause in the create table in order to permit insertions and updates on a referencing table
(atable with foreign key
 
attributes) with all kind of actions (the existing ones plus CASCADE, SET NULL and SET DEFAULT).

I think it is important to handle situations where the referencing data is available but it cannot be inserted due to
thelack of the referenced
 
tuple. It is ugly, for example, to request the user to create a dummy referenced entry previous to the insertion since
itcan be done
 
automatically with the proposed functionality.
Applying it in the context of a product with a well-defined execution model of triggers, like PostgreSQL, I do not
introduceany kind of
 
indetermination in the sequence of verification of the referential constraints, because we know beforehand, depending
onthe order of creation of
 
the tables and constraints, which will be the resulting order of the chain of verifications. So, when a referencing
tableis updated or tuples
 
are added to it, even when this table is the origin of various referential chains of verifications, the resulting
behavioronly depends on the
 
order of creation mentioned above. (I insist with the theme of determinism because I think this is the main problem for
whichno database product
 
includes this characteristic). I tested the code with examples of such cases (taking modified problematical examples
froma text of Markowitz)
 
and it works well.

The new syntax for the column_constraint_clause (and table_constraint_clause) of the CREATE TABLE statement that I
propose(and implement) is:
 
    ...    [ ON INSERT action ]    [ ON DELETE action ]    [ ON UPDATE_LEFT action ]    [ ON UPDATE_RIGHT action ]
...
where

"ON DELETE action"  stays the same as before (it refers to deletes in the referenced table),

"ON UPDATE_RIGHT action"  is the original ON UPDATE action (like before, it refers to modifications in the referenced
table),

"ON UPDATE_LEFT action"  specifies the action to do when a referencing column (a FK_column) in the referencing table is
beingupdated to a new value, and this new
 
value do not exist like pk_value in the pk_table. If the row is updated, but the referencing column is not changed, no
actionis done. There are
 
the following actions.
  NO ACTION         Disallows update of row.
  RESTRICT         Disallows update of row.
  CASCADE         Updates the value of the referenced column (the pk_column) to the new value of the
referencingcolumn (the fk_column).
 
  SET NULL         Sets the referencing column values to NULL.
  SET DEFAULT         Sets the referencing column values to their default value.

"ON INSERT action"  specifies the action to do when a referencing row (a FK_row) in the referencing table is being
inserted,and the new fk_values do not exist
 
like pk_values in the referenced table (pk_table). There are the following actions.
  NO ACTION         Disallows insert of row.
  RESTRICT         Disallows insert of row.
  CASCADE         Inserts a new row into the referenced table which pk_columns take the values of the new fk_columns,
andthe other attributes are set to
 
NULL values (if it is allowed).
  SET NULL         Sets the referencing column values to NULL.
  SET DEFAULT         Sets the referencing column values to their default value.

I have not added new files, just modified the existing ones (so the makefiles stay like before). I  send a diff (-c)
againstthe version 7.0.2
 
(the one I worked with).

In summary, the patch contains:

* modifications to the grammar to include the new syntax of the CREATE TABLE statement (to recognize the new tokens and
dothe appropriate
 
stuff).

* Addition of definitions of flags and masks for FOREIGN KEY constraints in CreateStmt.

* the new generic trigger procedures for referential integrity constraint checks.

* modifications to the parser stage to accept them (in procedures transformCreateStmt() and  transformAlterTableStmt()
).

* update to declarations for operations on built-in types.

* extension of the definition of the system "procedure" relation (pg_proc) along with the  relation's initial
contents.

* modifications to the TRIGGERs support code to accept the new characteristics.

Many thanks in advance to those who read and (maybe) consider all this, regards
                                       Jose Luis Ozzano  (jozzano@exa.unicen.edu.ar)