As previously mentioned, I'm working on implementing [subject]. I
think I've mostly worked it out, but I'm having trouble with the
GENERATED ALWAYS case.
My changes (see attached patch) can be summarized as follows:
- in backend/utils/adt/misc.c:
- added a force_default_value() function which takes a string
argument (the name of the column to force to default) and
currently does nothing.
- in backend/parser/gram.y:
- when GENERATED ... AS ... is encountered in a column definition,
it adds a node of the new T_Generated type to the constraint
list. This node contains a bool that differentiates between BY
DEFAULT and ALWAYS, and a pointer to a CreateSeqStmt (for
IDENTITY '(' OptSeqList ')') or a List constructed by the a_expr
production (for '(' a_expr ')')
- in backend/parser/analyze.c:
- factored out the code from transformColumnDefinition() that
creates the sequence and the DEFAULT constraint into a separate
CreateSerialColumn() function which takes as one of its arguments
is a List of sequence options. The SERIAL code passes in a NIL
list, while the GENERATED AS IDENTITY code passes in the option
list from the CreateSeqStmt.
- added a CreateAlwaysDefaultColumn() function which synthesizes a
CreateTrigStmt equivalent to CREATE TRIGGER foo BEFORE INSERT ON
bar FOR EACH ROW EXECUTE PROCEDURE force_default_value ('baz')
and adds it to the work list. This function is called by
transformColumnDefinition() if a Generated node with always set
to true is encountered.
Now I must be doing something wrong in CreateAlwaysDefaultColumn(),
because the CreateTrigStmt fails to execute:
| des=# create table test ( id int generated always as identity ( minvalue 1000 ),
word text );
| NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column
"test.id"
| NOTICE: CREATE TABLE will create implicit trigger "test_id_always_default" for
column "test.id"
| ERROR: relation "public.test" does not exist
GENERATED BY DEFAULT AS IDENTITY works fine though, so I must have
done *something* right:
| des=# create table test ( id int generated by default as identity ( minvalue 1000 ),
word text );
| NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column
"test.id"
| CREATE TABLE
| des=# select sequence_name, last_value, min_value, max_value from test_id_seq;
| sequence_name | last_value | min_value | max_value
| ---------------+------------+-----------+---------------------
| test_id_seq | 1000 | 1000 | 9223372036854775807
| (1 row)
|
On the other hand, I seem to have botched the definition of
force_default_value() in include/catalog/pg_proc.h, because adding the
trigger manually doesn't seem to work either:
| des=# \df force_default_value
| List of functions
| Result data type | Schema | Name | Argument data types
| ------------------+------------+---------------------+---------------------
| "trigger" | pg_catalog | force_default_value | text
| (1 row)
|
| des=# create trigger test_id_always_default before insert on test for each row
execute procedure force_default_value ('id');
| ERROR: function force_default_value() does not exist
Any suggestions?
DES
--
Dag-Erling Smørgrav - [EMAIL PROTECTED]
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/analyze.c,v
retrieving revision 1.283
diff -u -u -r1.283 analyze.c
--- src/backend/parser/analyze.c 1 Aug 2003 00:15:22 -0000 1.283
+++ src/backend/parser/analyze.c 3 Aug 2003 11:39:12 -0000
@@ -917,13 +917,116 @@
}
static void
+CreateSerialColumn(ParseState *pstate, CreateStmtContext *cxt,
+ ColumnDef *column, List *seqopts)
+{
+ Constraint *constraint;
+ char *sname;
+ char *snamespace;
+ char *qstring;
+ A_Const *snamenode;
+ FuncCall *funccallnode;
+ CreateSeqStmt *seqstmt;
+
+ /*
+ * Determine name and namespace to use for the sequence.
+ */
+ sname = makeObjectName(cxt->relation->relname, column->colname, "seq");
+ snamespace = get_namespace_name(RangeVarGetCreationNamespace(cxt->relation));
+
+ ereport(NOTICE,
+ (errmsg("%s will create implicit sequence \"%s\" for SERIAL column \"%s.%s\"",
+ cxt->stmtType, sname,
+ cxt->relation->relname, column->colname)));
+
+ /*
+ * Build a CREATE SEQUENCE command to create the sequence object,
+ * and add it to the list of things to be done before this
+ * CREATE/ALTER TABLE.
+ */
+ seqstmt = makeNode(CreateSeqStmt);
+ seqstmt->sequence = makeRangeVar(snamespace, sname);
+ seqstmt->options = seqopts;
+
+ cxt->blist = lappend(cxt->blist, seqstmt);
+
+ /*
+ * Mark the ColumnDef so that during execution, an appropriate
+ * dependency will be added from the sequence to the column.
+ */
+ column->support = makeRangeVar(snamespace, sname);
+
+ /*
+ * Create appropriate constraints for SERIAL. We do this in full,
+ * rather than shortcutting, so that we will detect any
+ * conflicting constraints the user wrote (like a different
+ * DEFAULT).
+ *
+ * Create an expression tree representing the function call
+ * nextval('"sequencename"')
+ */
+ qstring = quote_qualified_identifier(snamespace, sname);
+ snamenode = makeNode(A_Const);
+ snamenode->val.type = T_String;
+ snamenode->val.val.str = qstring;
+ funccallnode = makeNode(FuncCall);
+ funccallnode->funcname = SystemFuncName("nextval");
+ funccallnode->args = makeList1(snamenode);
+ funccallnode->agg_star = false;
+ funccallnode->agg_distinct = false;
+
+ constraint = makeNode(Constraint);
+ constraint->contype = CONSTR_DEFAULT;
+ constraint->name = sname;
+ constraint->raw_expr = (Node *) funccallnode;
+ constraint->cooked_expr = NULL;
+ constraint->keys = NIL;
+ column->constraints = lappend(column->constraints, constraint);
+
+ constraint = makeNode(Constraint);
+ constraint->contype = CONSTR_NOTNULL;
+ column->constraints = lappend(column->constraints, constraint);
+}
+
+static void
+CreateAlwaysDefaultColumn(ParseState *pstate, CreateStmtContext *cxt,
+ ColumnDef *column)
+{
+ char *tname;
+ char *tnamespace;
+ CreateTrigStmt *trigstmt;
+
+ tname = makeObjectName(cxt->relation->relname,
+ column->colname, "always_default");
+ tnamespace = get_namespace_name(RangeVarGetCreationNamespace(cxt->relation));
+
+ ereport(NOTICE,
+ (errmsg("%s will create implicit trigger \"%s\" for column \"%s.%s\"",
+ cxt->stmtType, tname,
+ cxt->relation->relname, column->colname)));
+
+ trigstmt = makeNode(CreateTrigStmt);
+ trigstmt->trigname = tname;
+ trigstmt->relation = makeRangeVar(tnamespace, cxt->relation->relname);
+ trigstmt->funcname = SystemFuncName("force_default_value");
+ trigstmt->args = makeList1(makeString(column->colname));
+ trigstmt->before = true;
+ trigstmt->row = true;
+ trigstmt->actions[0] = 'i';
+
+ cxt->blist = lappend(cxt->blist, trigstmt);
+}
+
+static void
transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
ColumnDef *column)
{
bool is_serial;
+ bool is_always_default;
bool saw_nullable;
Constraint *constraint;
List *clist;
+ List *seqopts;
cxt->columns = lappend(cxt->columns, column);
@@ -937,6 +1040,7 @@
strcmp(typname, "serial4") == 0)
{
is_serial = true;
+ seqopts = NIL;
column->typename->names = NIL;
column->typename->typeid = INT4OID;
}
@@ -944,6 +1048,7 @@
strcmp(typname, "serial8") == 0)
{
is_serial = true;
+ seqopts = NIL;
column->typename->names = NIL;
column->typename->typeid = INT8OID;
}
@@ -952,76 +1057,62 @@
/* Do necessary work on the column type declaration */
transformColumnType(pstate, column);
- /* Special actions for SERIAL pseudo-types */
- if (is_serial)
+ /* Check for GENERATED ... AS ... */
+ is_always_default = false;
+ foreach(clist, column->constraints)
{
- char *sname;
- char *snamespace;
- char *qstring;
- A_Const *snamenode;
- FuncCall *funccallnode;
- CreateSeqStmt *seqstmt;
+ Generated *generated;
- /*
- * Determine name and namespace to use for the sequence.
- */
- sname = makeObjectName(cxt->relation->relname, column->colname, "seq");
- snamespace = get_namespace_name(RangeVarGetCreationNamespace(cxt->relation));
-
- ereport(NOTICE,
- (errmsg("%s will create implicit sequence \"%s\" for SERIAL column \"%s.%s\"",
- cxt->stmtType, sname,
- cxt->relation->relname, column->colname)));
+ generated = lfirst(clist);
+ if (!IsA(generated, Generated))
+ continue;
- /*
- * Build a CREATE SEQUENCE command to create the sequence object,
- * and add it to the list of things to be done before this
- * CREATE/ALTER TABLE.
- */
- seqstmt = makeNode(CreateSeqStmt);
- seqstmt->sequence = makeRangeVar(snamespace, sname);
- seqstmt->options = NIL;
+ if (generated->always)
+ is_always_default = true;
- cxt->blist = lappend(cxt->blist, seqstmt);
+ if (IsA(generated->expr, CreateSeqStmt)) { /* AS IDENTITY */
+ CreateSeqStmt *stmt = (CreateSeqStmt *)generated->expr;
- /*
- * Mark the ColumnDef so that during execution, an appropriate
- * dependency will be added from the sequence to the column.
- */
- column->support = makeRangeVar(snamespace, sname);
+ /* is this column already serial? */
+ if (is_serial) {
+ ereport(WARNING,
+ (errmsg("multiple SERIAL / GENERATED AS IDENTITY on column \"%s.%s\"",
+ cxt->relation->relname, column->colname)));
+ continue;
+ }
+
+ /* check the type */
+ column->typename->typeid = LookupTypeName(column->typename);
+ if (column->typename->typeid != InvalidOid)
+ column->typename->names = NIL;
+ if (column->typename->typeid != INT4OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("GENERATED AS IDENTITY columns must be of integer type")));
- /*
- * Create appropriate constraints for SERIAL. We do this in full,
- * rather than shortcutting, so that we will detect any
- * conflicting constraints the user wrote (like a different
- * DEFAULT).
- *
- * Create an expression tree representing the function call
- * nextval('"sequencename"')
- */
- qstring = quote_qualified_identifier(snamespace, sname);
- snamenode = makeNode(A_Const);
- snamenode->val.type = T_String;
- snamenode->val.val.str = qstring;
- funccallnode = makeNode(FuncCall);
- funccallnode->funcname = SystemFuncName("nextval");
- funccallnode->args = makeList1(snamenode);
- funccallnode->agg_star = false;
- funccallnode->agg_distinct = false;
-
- constraint = makeNode(Constraint);
- constraint->contype = CONSTR_DEFAULT;
- constraint->name = sname;
- constraint->raw_expr = (Node *) funccallnode;
- constraint->cooked_expr = NULL;
- constraint->keys = NIL;
- column->constraints = lappend(column->constraints, constraint);
-
- constraint = makeNode(Constraint);
- constraint->contype = CONSTR_NOTNULL;
- column->constraints = lappend(column->constraints, constraint);
+ is_serial = true;
+ seqopts = stmt->options;
+ stmt->options = NIL;
+ } else { /* AS ( <expr> ) */
+ /*
+ * "GENERATED BY DEFAULT AS ( <expr> )" is not valid SQL,
+ * though it might make sense to accept it as an alternate
+ * syntax for "DEFAULT expr".
+ */
+ Assert(is_always_default);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("GENERATED AS ( <expr> ) is not yet supported")));
+ }
}
+ if (is_serial)
+ CreateSerialColumn(pstate, cxt, column, seqopts);
+
+ if (is_always_default)
+ CreateAlwaysDefaultColumn(pstate, cxt, column);
+
/* Process column constraints, if any... */
transformConstraintAttrs(column->constraints);
@@ -1045,6 +1136,12 @@
continue;
}
+ /*
+ * GENERATE ... AS ... was handled earlier
+ */
+ if (IsA(constraint, Generated))
+ continue;
+
Assert(IsA(constraint, Constraint));
switch (constraint->contype)
@@ -1188,7 +1285,7 @@
inhRelation->relation->relname)));
/*
- * Check for SELECT privilages
+ * Check for SELECT privilages
*/
aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(),
ACL_SELECT);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.427
diff -u -u -r2.427 gram.y
--- src/backend/parser/gram.y 19 Jul 2003 20:20:52 -0000 2.427
+++ src/backend/parser/gram.y 3 Aug 2003 10:55:19 -0000
@@ -317,6 +317,7 @@
%type <list> constraints_set_list
%type <boolean> constraints_set_mode
+%type <node> GeneratedAs
/*
* If you make any token changes, update the keyword table in
@@ -326,7 +327,7 @@
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD AFTER
- AGGREGATE ALL ALTER ANALYSE ANALYZE AND ANY ARRAY AS ASC
+ AGGREGATE ALL ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASSERTION ASSIGNMENT AT AUTHORIZATION
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
@@ -349,11 +350,11 @@
FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
- GLOBAL GRANT GROUP_P
+ GENERATED GLOBAL GRANT GROUP_P
HANDLER HAVING HOLD HOUR_P
- ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
+ IDENTITY ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -1604,6 +1605,39 @@
n->fk_del_action = (char) ($5 & 0xFF);
n->deferrable = FALSE;
n->initdeferred = FALSE;
+ $$ = (Node *)n;
+ }
+ | GENERATED ALWAYS GeneratedAs
+ {
+ Generated *n = makeNode(Generated);
+ n->always = true;
+ n->expr = $3;
+ $$ = (Node *)n;
+ }
+ | GENERATED BY DEFAULT GeneratedAs
+ {
+ Generated *n = makeNode(Generated);
+ n->always = false;
+ n->expr = $4;
+ $$ = (Node *)n;
+ }
+ ;
+
+GeneratedAs:
+ AS '(' a_expr ')'
+ {
+ $$ = $3;
+ }
+ | AS IDENTITY
+ {
+ CreateSeqStmt *n = makeNode(CreateSeqStmt);
+ n->options = NIL;
+ $$ = (Node *)n;
+ }
+ | AS IDENTITY '(' OptSeqList ')'
+ {
+ CreateSeqStmt *n = makeNode(CreateSeqStmt);
+ n->options = $4;
$$ = (Node *)n;
}
;
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/keywords.c,v
retrieving revision 1.140
diff -u -u -r1.140 keywords.c
--- src/backend/parser/keywords.c 25 Jun 2003 03:40:18 -0000 1.140
+++ src/backend/parser/keywords.c 2 Aug 2003 22:46:40 -0000
@@ -39,6 +39,7 @@
{"aggregate", AGGREGATE},
{"all", ALL},
{"alter", ALTER},
+ {"always", ALWAYS},
{"analyse", ANALYSE}, /* British spelling */
{"analyze", ANALYZE},
{"and", AND},
@@ -141,6 +142,7 @@
{"from", FROM},
{"full", FULL},
{"function", FUNCTION},
+ {"generated", GENERATED},
{"global", GLOBAL},
{"grant", GRANT},
{"group", GROUP_P},
@@ -148,6 +150,7 @@
{"having", HAVING},
{"hold", HOLD},
{"hour", HOUR_P},
+ {"identity", IDENTITY},
{"ilike", ILIKE},
{"immediate", IMMEDIATE},
{"immutable", IMMUTABLE},
Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.30
diff -u -u -r1.30 misc.c
--- src/backend/utils/adt/misc.c 27 Jun 2003 17:07:03 -0000 1.30
+++ src/backend/utils/adt/misc.c 3 Aug 2003 11:29:05 -0000
@@ -18,6 +18,7 @@
#include <time.h>
#include "commands/dbcommands.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "utils/builtins.h"
@@ -57,4 +58,30 @@
namestrcpy(db, get_database_name(MyDatabaseId));
PG_RETURN_NAME(db);
+}
+
+/*
+ * force_default_value()
+ * Force a column to its default value
+ */
+Datum
+force_default_value(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata;
+ int tgnargs;
+ char **tgargs;
+
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("%s() was not fired by trigger manager",
+ PG_FUNCNAME_MACRO)));
+
+ trigdata = (TriggerData *)fcinfo->context;
+ tgnargs = trigdata->tg_trigger->tgnargs;
+ tgargs = trigdata->tg_trigger->tgargs;
+
+ ereport(NOTICE, (errmsg("%s() was fired!", PG_FUNCNAME_MACRO)));
+
+ return PointerGetDatum(NULL);
}
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.310
diff -u -u -r1.310 pg_proc.h
--- src/include/catalog/pg_proc.h 30 Jul 2003 22:56:24 -0000 1.310
+++ src/include/catalog/pg_proc.h 3 Aug 2003 11:18:51 -0000
@@ -3419,6 +3419,9 @@
DATA(insert OID = 2509 ( pg_get_expr PGNSP PGUID 12 f f t f s 3 25 "25 26 16" pg_get_expr_ext - _null_ ));
DESCR("deparse an encoded expression with pretty-print option");
+DATA(insert OID = 2510 ( force_default_value PGNSP PGUID 12 f f t f v 1 2279 "25" force_default_value - _null_ ));
+DESCR("force default value for GENERATED ALWAYS columns");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/include/nodes/nodes.h,v
retrieving revision 1.144
diff -u -u -r1.144 nodes.h
--- src/include/nodes/nodes.h 3 Jul 2003 16:34:25 -0000 1.144
+++ src/include/nodes/nodes.h 2 Aug 2003 23:11:36 -0000
@@ -283,6 +283,7 @@
T_CreateOpClassItem,
T_CompositeTypeStmt,
T_InhRelation,
+ T_Generated,
/*
* TAGS FOR FUNCTION-CALL CONTEXT AND RESULTINFO NODES (see fmgr.h)
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.243
diff -u -u -r1.243 parsenodes.h
--- src/include/nodes/parsenodes.h 3 Jul 2003 16:34:25 -0000 1.243
+++ src/include/nodes/parsenodes.h 3 Aug 2003 10:21:44 -0000
@@ -975,6 +975,16 @@
bool skip_validation; /* skip validation of existing rows? */
} FkConstraint;
+/*
+ * Definitions for GENERATED ... AS ... in CreateStmt
+ */
+typedef struct Generated
+{
+ NodeTag type;
+ bool always; /* true if ALWAYS, false if BY DEFAULT */
+ Node *expr; /* Expression or CreateSeqStmt */
+} Generated;
+
/* ----------------------
* Create/Drop TRIGGER Statements
* ----------------------
Index: src/include/utils/builtins.h
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.224
diff -u -u -r1.224 builtins.h
--- src/include/utils/builtins.h 30 Jul 2003 22:56:24 -0000 1.224
+++ src/include/utils/builtins.h 3 Aug 2003 11:13:49 -0000
@@ -337,6 +337,7 @@
extern Datum nullvalue(PG_FUNCTION_ARGS);
extern Datum nonnullvalue(PG_FUNCTION_ARGS);
extern Datum current_database(PG_FUNCTION_ARGS);
+extern Datum force_default_value(PG_FUNCTION_ARGS);
/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
