Thread

[PATCH v2 4/4] POC: Add naive grammar to specify the column logical order in CREATE TABLE

Julien Rouhaud <julien.rouhaud@free.fr> — 2022-06-07T10:46:06Z

e.g.:

CREATE TABLE ab (b integer, a integer) ORDER (a, b).

Author: Julien Rouhaud
Reviewed-by: FIXME
Discussion: FIXME
---
 src/backend/commands/tablecmds.c | 39 +++++++++++++++++++++++++++++-
 src/backend/nodes/copyfuncs.c    |  1 +
 src/backend/nodes/equalfuncs.c   |  1 +
 src/backend/nodes/outfuncs.c     |  1 +
 src/backend/parser/gram.y        | 41 +++++++++++++++++++++++++++-----
 src/include/nodes/parsenodes.h   |  1 +
 6 files changed, 77 insertions(+), 7 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 06b9733717..660a18c638 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -945,12 +945,49 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 														   colDef->compression);
 	}
 
+	/* Change rels logical attributes position if needed */
+	if (stmt->orderElts != NIL)
+	{
+		if (list_length(stmt->orderElts) != descriptor->natts)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					 errmsg("Invalid number of elements in ORDER clause"),
+					 errdetail("expected %d, found %d", descriptor->natts,
+							   list_length(stmt->orderElts))));
+
+		for (int i = 0; i < descriptor->natts; i++)
+		{
+			Form_pg_attribute att = TupleDescAttr(descriptor, i);
+			ListCell *lc;
+			AttrNumber attnum = 1;
+			bool		found = false;
+
+			foreach(lc, stmt->orderElts)
+			{
+				char	   *colname = strVal(lfirst(lc));
+
+				if (strcmp(colname, NameStr(att->attname)) == 0)
+				{
+					found = true;
+					att->attnum = attnum;
+					break;
+				}
+				attnum++;
+			}
+
+			if (!found)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						 errmsg("order attribute \"%s\" not found in \"%s\" attributes",
+								NameStr(att->attname), relname)));
+		}
+	}
 	/*
 	 * FIXME
 	 * testing mode: inverse all attribute positions.  This has to be done
 	 * after that defaults have been computed.
 	 */
-	if (relkind == RELKIND_RELATION && inheritOids == NIL
+	else if (relkind == RELKIND_RELATION && inheritOids == NIL
 			&& descriptor->natts > 1
 			/* lame attempt to discard CTAS */
 			&& queryString != NULL)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7ee1876361..c1bb961f1a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4067,6 +4067,7 @@ CopyCreateStmtFields(const CreateStmt *from, CreateStmt *newnode)
 {
 	COPY_NODE_FIELD(relation);
 	COPY_NODE_FIELD(tableElts);
+	COPY_NODE_FIELD(orderElts);
 	COPY_NODE_FIELD(inhRelations);
 	COPY_NODE_FIELD(partspec);
 	COPY_NODE_FIELD(partbound);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e1f9cb4f41..9acf351d2a 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1647,6 +1647,7 @@ _equalCreateStmt(const CreateStmt *a, const CreateStmt *b)
 {
 	COMPARE_NODE_FIELD(relation);
 	COMPARE_NODE_FIELD(tableElts);
+	COMPARE_NODE_FIELD(orderElts);
 	COMPARE_NODE_FIELD(inhRelations);
 	COMPARE_NODE_FIELD(partbound);
 	COMPARE_NODE_FIELD(partspec);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index d687c1e013..e557ca1141 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2870,6 +2870,7 @@ _outCreateStmtInfo(StringInfo str, const CreateStmt *node)
 {
 	WRITE_NODE_FIELD(relation);
 	WRITE_NODE_FIELD(tableElts);
+	WRITE_NODE_FIELD(orderElts);
 	WRITE_NODE_FIELD(inhRelations);
 	WRITE_NODE_FIELD(partspec);
 	WRITE_NODE_FIELD(partbound);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 969c9c158f..c24adf59de 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -425,6 +425,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <list>	parse_toplevel stmtmulti routine_body_stmt_list
 				OptTableElementList TableElementList OptInherit definition
+				OptOrderElementList OrderElementList
 				OptTypedTableElementList TypedTableElementList
 				reloptions opt_reloptions
 				OptWith opt_definition func_args func_args_list
@@ -524,6 +525,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				 SetResetClause FunctionSetResetClause
 
 %type <node>	TableElement TypedTableElement ConstraintElem TableFuncElement
+				OrderElement
 %type <node>	columnDef columnOptions
 %type <defelt>	def_elem reloption_elem old_aggr_elem operator_def_elem
 %type <node>	def_arg columnElem where_clause where_or_current_clause
@@ -3577,6 +3579,7 @@ copy_generic_opt_arg_list_item:
  *****************************************************************************/
 
 CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
+			OptOrderElementList
 			OptInherit OptPartitionSpec table_access_method_clause OptWith
 			OnCommitOption OptTableSpace
 				{
@@ -3585,14 +3588,15 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$4->relpersistence = $2;
 					n->relation = $4;
 					n->tableElts = $6;
-					n->inhRelations = $8;
-					n->partspec = $9;
+					n->orderElts = $8;
+					n->inhRelations = $9;
+					n->partspec = $10;
 					n->ofTypename = NULL;
 					n->constraints = NIL;
-					n->accessMethod = $10;
-					n->options = $11;
-					n->oncommit = $12;
-					n->tablespacename = $13;
+					n->accessMethod = $11;
+					n->options = $12;
+					n->oncommit = $13;
+					n->tablespacename = $14;
 					n->if_not_exists = false;
 					$$ = (Node *) n;
 				}
@@ -3605,6 +3609,7 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$7->relpersistence = $2;
 					n->relation = $7;
 					n->tableElts = $9;
+					n->orderElts = NIL;
 					n->inhRelations = $11;
 					n->partspec = $12;
 					n->ofTypename = NULL;
@@ -3625,6 +3630,7 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$4->relpersistence = $2;
 					n->relation = $4;
 					n->tableElts = $7;
+					n->orderElts = NIL;
 					n->inhRelations = NIL;
 					n->partspec = $8;
 					n->ofTypename = makeTypeNameFromNameList($6);
@@ -3646,6 +3652,7 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$7->relpersistence = $2;
 					n->relation = $7;
 					n->tableElts = $10;
+					n->orderElts = NIL;
 					n->inhRelations = NIL;
 					n->partspec = $11;
 					n->ofTypename = makeTypeNameFromNameList($9);
@@ -3667,6 +3674,7 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$4->relpersistence = $2;
 					n->relation = $4;
 					n->tableElts = $8;
+					n->orderElts = NIL;
 					n->inhRelations = list_make1($7);
 					n->partbound = $9;
 					n->partspec = $10;
@@ -3688,6 +3696,7 @@ CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
 					$7->relpersistence = $2;
 					n->relation = $7;
 					n->tableElts = $11;
+					n->orderElts = NIL;
 					n->inhRelations = list_make1($10);
 					n->partbound = $12;
 					n->partspec = $13;
@@ -3735,6 +3744,26 @@ OptTemp:	TEMPORARY					{ $$ = RELPERSISTENCE_TEMP; }
 			| /*EMPTY*/					{ $$ = RELPERSISTENCE_PERMANENT; }
 		;
 
+OptOrderElementList:
+			ORDER '(' OrderElementList ')'		{ $$ = $3; }
+			| /*EMPTY*/							{ $$ = NIL; }
+		;
+
+OrderElementList:
+			OrderElement
+				{
+					$$ = list_make1($1);
+				}
+			| OrderElementList ',' OrderElement
+				{
+					$$ = lappend($1, $3);
+				}
+		;
+
+OrderElement:
+			columnElem							{ $$ = $1; }
+		;
+
 OptTableElementList:
 			TableElementList					{ $$ = $1; }
 			| /*EMPTY*/							{ $$ = NIL; }
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2e6c355db2..929cf66c7e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2532,6 +2532,7 @@ typedef struct CreateStmt
 	NodeTag		type;
 	RangeVar   *relation;		/* relation to create */
 	List	   *tableElts;		/* column definitions (list of ColumnDef) */
+	List	   *orderElts;		/* column logical order (list of String) */
 	List	   *inhRelations;	/* relations to inherit from (list of
 								 * RangeVar) */
 	PartitionBoundSpec *partbound;	/* FOR VALUES clause */
-- 
2.33.1


--5462ml7mocwud3y7--