Hi,
The attached patch adds the optional REFERENCES syntax in CREATE TRIGGER
statement to make an automatic alias for OLD/NEW record during trigger
setup. The implementation of this new feature makes CREATE TRIGGER
command more compatible to SQL standard, and allows the future
implementation of executing SQL commands in trigger action.
After the implementation, the extended syntax of statement is as follows.
CREATE TRIGGER name BEFORE|AFTER
INSERT|DELETE|UPDATE [OR...] ON tablename
[REFERENCING OLD|NEW [AS] identifier]
[FOR [EACH] ROW|STATEMENT]
EXECUTE PROCEDURE funcname (arguments)
The patch will also update two columns, condition_reference_old_table and
condition_reference_new_table with alias names of the OLD/NEW record in
the Triggers table of the information schema.
$ diff -c src/include/nodes/parsenodes.h.orig src/include/nodes/parsenodes.h
*** src/include/nodes/parsenodes.h.orig 2004-07-15 10:15:08.000000000 -0400
--- src/include/nodes/parsenodes.h 2004-09-01 18:20:20.000000000 -0400
***************
*** 994,999 ****
--- 994,1000 ----
RangeVar *relation; /* relation trigger is on */
List *funcname; /* qual. name of function to call */
List *args; /* list of (T_String) Values or
NIL */
+ List *tupleref; /* referenced tuple */
bool before; /* BEFORE/AFTER */
bool row; /* ROW/STATEMENT */
char actions[4]; /* 1 to 3 of 'i', 'u', 'd', +
trailing \0 */
$ diff -c src/include/catalog/pg_trigger.h.orig src/include/catalog/pg_trigger.h
*** src/include/catalog/pg_trigger.h.orig 2004-07-15 10:11:46.000000000
-0400
--- src/include/catalog/pg_trigger.h 2004-09-15 16:00:06.000000000 -0400
***************
*** 29,52 ****
*/
CATALOG(pg_trigger)
{
! Oid tgrelid; /* triggered relation */
NameData tgname; /* trigger' name */
! Oid tgfoid; /* OID of function to
be called */
int2 tgtype; /* BEFORE/AFTER
UPDATE/DELETE/INSERT
*
ROW/STATEMENT */
bool tgenabled; /* trigger is enabled/disabled
*/
! bool tgisconstraint; /* trigger is a RI constraint */
! NameData tgconstrname; /* RI constraint name */
! Oid tgconstrrelid; /* RI table of foreign key
definition */
/* in the case of ON DELETE or ON UPDATE */
! bool tgdeferrable; /* RI trigger is deferrable */
! bool tginitdeferred; /* RI trigger is deferred initially */
int2 tgnargs; /* # of extra arguments in
tgargs */
! int2vector tgattr; /* UPDATE of attr1, attr2 ... (NI) */
bytea tgargs; /*
first\000second\000tgnargs\000 */
int2 tgncols; /* # of columns in tgcols */
bytea tgcols; /* column names */
} FormData_pg_trigger;
/* ----------------
--- 29,53 ----
*/
CATALOG(pg_trigger)
{
! Oid tgrelid; /* triggered relation */
NameData tgname; /* trigger' name */
! Oid tgfoid; /* OID of function to be called
*/
int2 tgtype; /* BEFORE/AFTER
UPDATE/DELETE/INSERT
*
ROW/STATEMENT */
bool tgenabled; /* trigger is enabled/disabled
*/
! bool tgisconstraint; /* trigger is a RI constraint */
! NameData tgconstrname; /* RI constraint name */
! Oid tgconstrrelid; /* RI table of foreign key
definition */
/* in the case of ON DELETE or ON UPDATE */
! bool tgdeferrable; /* RI trigger is deferrable */
! bool tginitdeferred; /* RI trigger is deferred
initially */
int2 tgnargs; /* # of extra arguments in
tgargs */
! int2vector tgattr; /* UPDATE of attr1, attr2 ...
(NI) */
bytea tgargs; /*
first\000second\000tgnargs\000 */
int2 tgncols; /* # of columns in tgcols */
bytea tgcols; /* column names */
+ NameData tgidentifier; /* referenced tuple */
} FormData_pg_trigger;
/* ----------------
***************
*** 60,87 ****
* compiler constants for pg_trigger
* ----------------
*/
! #define Natts_pg_trigger 15
#define Anum_pg_trigger_tgrelid 1
#define Anum_pg_trigger_tgname 2
#define Anum_pg_trigger_tgfoid 3
#define Anum_pg_trigger_tgtype 4
#define Anum_pg_trigger_tgenabled 5
! #define Anum_pg_trigger_tgisconstraint 6
! #define Anum_pg_trigger_tgconstrname 7
! #define Anum_pg_trigger_tgconstrrelid 8
! #define Anum_pg_trigger_tgdeferrable 9
! #define Anum_pg_trigger_tginitdeferred 10
#define Anum_pg_trigger_tgnargs 11
#define Anum_pg_trigger_tgattr 12
#define Anum_pg_trigger_tgargs 13
#define Anum_pg_trigger_tgncols 14
#define Anum_pg_trigger_tgcols 15
! #define TRIGGER_TYPE_ROW (1 << 0)
! #define TRIGGER_TYPE_BEFORE (1 << 1)
! #define TRIGGER_TYPE_INSERT (1 << 2)
! #define TRIGGER_TYPE_DELETE (1 << 3)
! #define TRIGGER_TYPE_UPDATE (1 << 4)
#define TRIGGER_CLEAR_TYPE(type) (type = 0)
--- 61,91 ----
* compiler constants for pg_trigger
* ----------------
*/
! #define Natts_pg_trigger 16
#define Anum_pg_trigger_tgrelid 1
#define Anum_pg_trigger_tgname 2
#define Anum_pg_trigger_tgfoid 3
#define Anum_pg_trigger_tgtype 4
#define Anum_pg_trigger_tgenabled 5
! #define Anum_pg_trigger_tgisconstraint 6
! #define Anum_pg_trigger_tgconstrname 7
! #define Anum_pg_trigger_tgconstrrelid 8
! #define Anum_pg_trigger_tgdeferrable 9
! #define Anum_pg_trigger_tginitdeferred 10
#define Anum_pg_trigger_tgnargs 11
#define Anum_pg_trigger_tgattr 12
#define Anum_pg_trigger_tgargs 13
#define Anum_pg_trigger_tgncols 14
#define Anum_pg_trigger_tgcols 15
+ #define Anum_pg_trigger_tgidentifier 16
! #define TRIGGER_TYPE_ROW (1 << 0)
! #define TRIGGER_TYPE_BEFORE (1 << 1)
! #define TRIGGER_TYPE_INSERT (1 << 2)
! #define TRIGGER_TYPE_DELETE (1 << 3)
! #define TRIGGER_TYPE_UPDATE (1 << 4)
! #define TRIGGER_TYPE_OLD (1 << 5)
! #define TRIGGER_TYPE_NEW (1 << 6)
#define TRIGGER_CLEAR_TYPE(type) (type = 0)
***************
*** 90,100 ****
--- 94,108 ----
#define TRIGGER_SETT_INSERT(type) (type |= TRIGGER_TYPE_INSERT)
#define TRIGGER_SETT_DELETE(type) (type |= TRIGGER_TYPE_DELETE)
#define TRIGGER_SETT_UPDATE(type) (type |= TRIGGER_TYPE_UPDATE)
+ #define TRIGGER_SETT_OLD(type) (type |= TRIGGER_TYPE_OLD)
+ #define TRIGGER_SETT_NEW(type) (type |= TRIGGER_TYPE_NEW)
#define TRIGGER_FOR_ROW(type) (type & TRIGGER_TYPE_ROW)
#define TRIGGER_FOR_BEFORE(type) (type & TRIGGER_TYPE_BEFORE)
#define TRIGGER_FOR_INSERT(type) (type & TRIGGER_TYPE_INSERT)
#define TRIGGER_FOR_DELETE(type) (type & TRIGGER_TYPE_DELETE)
#define TRIGGER_FOR_UPDATE(type) (type & TRIGGER_TYPE_UPDATE)
+ #define TRIGGER_FOR_OLD(type) (type & TRIGGER_TYPE_OLD)
+ #define TRIGGER_FOR_NEW(type) (type & TRIGGER_TYPE_NEW)
#endif /* PG_TRIGGER_H */
$ diff -c src/include/utils/rel.h.orig src/include/utils/rel.h
*** src/include/utils/rel.h.orig 2004-07-15 10:15:24.000000000 -0400
--- src/include/utils/rel.h 2004-10-18 17:59:54.000000000 -0400
***************
*** 49,69 ****
*/
typedef struct Trigger
{
! Oid tgoid; /* OID of trigger (pg_trigger
row) */
/* Remaining fields are copied from pg_trigger, see pg_trigger.h */
! char *tgname;
! Oid tgfoid;
int16 tgtype;
bool tgenabled;
bool tgisconstraint;
! Oid tgconstrrelid;
bool tgdeferrable;
bool tginitdeferred;
int16 tgnargs;
int16 tgattr[FUNC_MAX_ARGS];
! char **tgargs;
int16 tgncols;
! char **tgcols;
} Trigger;
typedef struct TriggerDesc
--- 49,70 ----
*/
typedef struct Trigger
{
! Oid tgoid; /* OID of trigger (pg_trigger
row) */
/* Remaining fields are copied from pg_trigger, see pg_trigger.h */
! char *tgname;
! Oid tgfoid;
int16 tgtype;
bool tgenabled;
bool tgisconstraint;
! Oid tgconstrrelid;
bool tgdeferrable;
bool tginitdeferred;
int16 tgnargs;
int16 tgattr[FUNC_MAX_ARGS];
! char **tgargs;
int16 tgncols;
! char **tgcols;
! char *tgidentifier;
} Trigger;
typedef struct TriggerDesc
$ diff -c src/backend/parser/gram.y.orig src/backend/parser/gram.y
*** src/backend/parser/gram.y.orig 2004-07-15 10:16:32.000000000 -0400
--- src/backend/parser/gram.y 2004-09-22 16:58:40.000000000 -0400
***************
*** 219,225 ****
any_operator expr_list dotted_name attrs
target_list update_target_list
insert_column_list
insert_target_list def_list opt_indirection
! group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
transaction_mode_list
transaction_mode_list_or_empty
TableFuncElementList
--- 219,225 ----
any_operator expr_list dotted_name attrs
target_list update_target_list
insert_column_list
insert_target_list def_list opt_indirection
! group_clause TriggerFuncArgs TriggerRefList
select_limit
opt_select_limit opclass_item_list
transaction_mode_list
transaction_mode_list_or_empty
TableFuncElementList
***************
*** 342,348 ****
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
! DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING
EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
--- 342,348 ----
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
! DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING
EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
***************
*** 2060,2076 ****
CreateTrigStmt:
CREATE TRIGGER name TriggerActionTime TriggerEvents
opt_trigger_column_list ON
! qualified_name TriggerForSpec EXECUTE PROCEDURE
func_name '(' TriggerFuncArgs ')'
{
CreateTrigStmt *n =
makeNode(CreateTrigStmt);
n->trigname = $3;
n->relation = $8;
! n->funcname = $12;
n->cols = $6;
! n->args = $14;
n->before = $4;
! n->row = $9;
memcpy(n->actions, $5, 4);
n->isconstraint = FALSE;
n->deferrable = FALSE;
--- 2060,2077 ----
CreateTrigStmt:
CREATE TRIGGER name TriggerActionTime TriggerEvents
opt_trigger_column_list ON
! qualified_name TriggerRefList TriggerForSpec EXECUTE
PROCEDURE
func_name '(' TriggerFuncArgs ')'
{
CreateTrigStmt *n =
makeNode(CreateTrigStmt);
n->trigname = $3;
n->relation = $8;
! n->funcname = $13;
! n->tupleref = $9;
n->cols = $6;
! n->args = $15;
n->before = $4;
! n->row = $10;
memcpy(n->actions, $5, 4);
n->isconstraint = FALSE;
n->deferrable = FALSE;
***************
*** 2089,2094 ****
--- 2090,2096 ----
n->relation = $8;
n->funcname = $16;
n->args = $18;
+ n->tupleref = NIL;
n->before = FALSE;
n->row = TRUE;
memcpy(n->actions, $6, 4);
***************
*** 2138,2143 ****
--- 2140,2156 ----
| /* EMPTY */ { $$ = NIL; }
;
+ TriggerRefList:
+ REFERENCES ColLabel opt_as name_list
+ {
+ $$ = lcons(makeString($2), $4);
+ }
+ | /* EMPTY */
+ {
+ $$ = NIL;
+ }
+ ;
+
TriggerForSpec:
FOR TriggerForOpt TriggerForType
{
$ diff -c src/backend/nodes/copyfuncs.c.orig src/backend/nodes/copyfuncs.c
*** src/backend/nodes/copyfuncs.c.orig 2004-07-15 10:10:34.000000000 -0400
--- src/backend/nodes/copyfuncs.c 2004-09-01 18:03:36.000000000 -0400
***************
*** 2170,2175 ****
--- 2170,2176 ----
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(funcname);
COPY_NODE_FIELD(args);
+ COPY_NODE_FIELD(tupleref);
COPY_SCALAR_FIELD(before);
COPY_SCALAR_FIELD(row);
strcpy(newnode->actions, from->actions); /* in-line string field
*/
$ diff -c src/backend/nodes/equalfuncs.c.orig src/backend/nodes/equalfuncs.c
*** src/backend/nodes/equalfuncs.c.orig 2004-07-15 10:10:44.000000000 -0400
--- src/backend/nodes/equalfuncs.c 2004-09-01 18:07:44.000000000 -0400
***************
*** 1163,1168 ****
--- 1163,1169 ----
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(funcname);
COMPARE_NODE_FIELD(args);
+ COMPARE_NODE_FIELD(tupleref);
COMPARE_SCALAR_FIELD(before);
COMPARE_SCALAR_FIELD(row);
if (strcmp(a->actions, b->actions) != 0) /* in-line string field
*/
$ diff -c src/backend/commands/trigger.c.orig src/backend/commands/trigger.c
*** src/backend/commands/trigger.c.orig 2004-08-13 10:51:24.000000000 -0400
--- src/backend/commands/trigger.c 2004-10-20 17:37:46.000000000 -0400
***************
*** 73,92 ****
Relation rel;
AclResult aclresult;
Relation tgrel;
! SysScanDesc tgscan;
! ScanKeyData key;
Relation pgrel;
HeapTuple tuple;
! Oid fargtypes[FUNC_MAX_ARGS];
! Oid funcoid;
! Oid funcrettype;
! Oid trigoid;
! int found = 0;
! int i;
char constrtrigname[NAMEDATALEN];
char *trigname;
char *constrname;
! Oid constrrelid = InvalidOid;
ObjectAddress myself,
referenced;
--- 73,93 ----
Relation rel;
AclResult aclresult;
Relation tgrel;
! SysScanDesc tgscan;
! ScanKeyData key;
Relation pgrel;
HeapTuple tuple;
! Oid fargtypes[FUNC_MAX_ARGS];
! Oid funcoid;
! Oid funcrettype;
! Oid trigoid;
! int found = 0;
! int i;
char constrtrigname[NAMEDATALEN];
char *trigname;
char *constrname;
! char *identifier;
! Oid constrrelid = InvalidOid;
ObjectAddress myself,
referenced;
***************
*** 243,248 ****
--- 244,267 ----
}
}
+ if (stmt->tupleref)
+ {
+ char *nw = strVal(lfirst(stmt->tupleref));
+ char *nm = strVal(llast(stmt->tupleref));
+
+ if (strcmp(nw, "new") == 0)
+ TRIGGER_SETT_NEW(tgtype);
+ else if (strcmp(nw, "old") == 0)
+ TRIGGER_SETT_OLD(tgtype);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("referenced tuple must be OLD
or NEW")));
+ identifier = pstrdup(nm);
+ }
+ else
+ identifier = "";
+
/*
* Scan pg_trigger for existing triggers on relation. We do this
* mainly because we must count them; a secondary benefit is to give a
***************
*** 305,311 ****
values[Anum_pg_trigger_tgrelid - 1] =
ObjectIdGetDatum(RelationGetRelid(rel));
values[Anum_pg_trigger_tgname - 1] = DirectFunctionCall1(namein,
!
CStringGetDatum(trigname));
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = BoolGetDatum(true);
--- 324,330 ----
values[Anum_pg_trigger_tgrelid - 1] =
ObjectIdGetDatum(RelationGetRelid(rel));
values[Anum_pg_trigger_tgname - 1] = DirectFunctionCall1(namein,
!
CStringGetDatum(trigname));
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = BoolGetDatum(true);
***************
*** 316,322 ****
values[Anum_pg_trigger_tgdeferrable - 1] =
BoolGetDatum(stmt->deferrable);
values[Anum_pg_trigger_tginitdeferred - 1] =
BoolGetDatum(stmt->initdeferred);
-
if (stmt->args)
{
List *le;
--- 335,340 ----
***************
*** 429,434 ****
--- 447,455 ----
CStringGetDatum(""));
}
+ values[Anum_pg_trigger_tgidentifier - 1] = DirectFunctionCall1(namein,
+
CStringGetDatum(identifier));
+
tuple = heap_formtuple(tgrel->rd_att, values, nulls);
/* force tuple to have the desired OID */
***************
*** 451,456 ****
--- 472,478 ----
pfree(DatumGetPointer(values[Anum_pg_trigger_tgname - 1]));
pfree(DatumGetPointer(values[Anum_pg_trigger_tgargs - 1]));
pfree(DatumGetPointer(values[Anum_pg_trigger_tgcols - 1]));
+ pfree(DatumGetPointer(values[Anum_pg_trigger_tgidentifier - 1]));
/*
* Update relation's pg_class entry. Crucial side-effect: other
***************
*** 823,829 ****
while (HeapTupleIsValid(htup = systable_getnext(tgscan)))
{
Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);
! Trigger *build;
if (found >= ntrigs)
elog(ERROR, "too many trigger records found for
relation \"%s\"",
--- 845,852 ----
while (HeapTupleIsValid(htup = systable_getnext(tgscan)))
{
Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);
! Trigger *build;
! NameData *trigref;
if (found >= ntrigs)
elog(ERROR, "too many trigger records found for
relation \"%s\"",
***************
*** 832,839 ****
build->tgoid = HeapTupleGetOid(htup);
build->tgname = DatumGetCString(DirectFunctionCall1(nameout,
!
NameGetDatum(&pg_trigger->tgname)));
!
build->tgfoid = pg_trigger->tgfoid;
build->tgtype = pg_trigger->tgtype;
build->tgenabled = pg_trigger->tgenabled;
--- 855,861 ----
build->tgoid = HeapTupleGetOid(htup);
build->tgname = DatumGetCString(DirectFunctionCall1(nameout,
!
NameGetDatum(&pg_trigger->tgname)));
build->tgfoid = pg_trigger->tgfoid;
build->tgtype = pg_trigger->tgtype;
build->tgenabled = pg_trigger->tgenabled;
***************
*** 892,897 ****
--- 914,922 ----
else
build->tgcols = NULL;
+ trigref = (NameData *) fastgetattr(htup,
Anum_pg_trigger_tgidentifier, tgrel->rd_att, &isnull);
+ build->tgidentifier =
DatumGetCString(DirectFunctionCall1(nameout, trigref));
+
found++;
}
***************
*** 1040,1045 ****
--- 1065,1071 ----
newcols[j] = pstrdup(trigger->tgcols[j]);
trigger->tgcols = newcols;
}
+ trigger->tgidentifier = pstrdup(trigger->tgidentifier);
trigger++;
}
***************
*** 1145,1150 ****
--- 1171,1177 ----
pfree(trigger->tgcols[trigger->tgncols]);
pfree(trigger->tgcols);
}
+ pfree(trigger->tgidentifier);
trigger++;
}
pfree(trigdesc->triggers);
***************
*** 1211,1216 ****
--- 1238,1245 ----
for (j = 0; j < trig1->tgncols; j++)
if (strcmp(trig1->tgcols[j], trig2->tgcols[j])
!= 0)
return false;
+ if (strcmp(trig1->tgidentifier, trig2->tgidentifier) !=
0)
+ return false;
}
}
else if (trigdesc2 != NULL)
$ diff -c src/backend/catalog/information_schema.sql.orig
src/backend/catalog/information_schema.sql
*** src/backend/catalog/information_schema.sql.orig 2004-07-14
20:10:08.000000000 -0400
--- src/backend/catalog/information_schema.sql 2004-09-23 18:42:02.000000000
-0400
***************
*** 1423,1430 ****
CAST(
CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
AS character_data) AS condition_timing,
! CAST(null AS sql_identifier) AS condition_reference_old_table,
! CAST(null AS sql_identifier) AS condition_reference_new_table
FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
(SELECT 4, 'INSERT' UNION ALL
--- 1423,1434 ----
CAST(
CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
AS character_data) AS condition_timing,
! CAST(
! CASE WHEN t.tgtype & 32 = 32 THEN t.tgidentifier ELSE null END
! AS sql_identifier) AS condition_reference_old_table,
! CAST(
! CASE WHEN t.tgtype & 64 = 64 THEN t.tgidentifier ELSE null END
! AS sql_identifier) AS condition_reference_new_table
FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
(SELECT 4, 'INSERT' UNION ALL
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
