From dc2c25fc6246866ce809a8b6bf1d2a2856334b54 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 27 Jun 2006 03:43:20 +0000 Subject: [PATCH] Add INCLUDING CONSTRAINTS to CREATE TABLE LIKE. Greg Stark --- doc/src/sgml/ref/create_table.sgml | 20 ++++++++-- src/backend/commands/tablecmds.c | 59 ++++++++++++++++++++++----- src/backend/nodes/copyfuncs.c | 4 +- src/backend/nodes/equalfuncs.c | 4 +- src/backend/parser/analyze.c | 64 ++++++++++++++++++++++++++++-- src/backend/parser/gram.y | 32 +++++++++------ src/backend/parser/keywords.c | 3 +- src/include/commands/tablecmds.h | 7 +++- src/include/nodes/parsenodes.h | 13 +++++- src/test/regress/expected/inherit.out | 21 +++++++++- src/test/regress/expected/sanity_check.out | 3 +- src/test/regress/sql/inherit.sql | 15 ++++++- 12 files changed, 206 insertions(+), 39 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a4cdc25018..916f0c8b56 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -23,7 +23,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint - | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } + | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] ... } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] @@ -232,7 +232,7 @@ and table_constraint is: - LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] + LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] The LIKE clause specifies a table from which @@ -252,6 +252,20 @@ and table_constraint is: default behavior is to exclude default expressions, resulting in all columns of the new table having null defaults. + + Likewise constraints for the copied column definitions will only be + copied if INCLUDING CONSTRAINTS is specified. Note + that currently even when INCLUDING CONSTRAINTS is specified + only CHECK constraints are copied. Also, no distinction is made between + column constraints and table constraints -- when constraints are + requested all check constraints are copied. + + + Note also that unlike INHERITS copied columns and + constraints are not merged with similarly named columns and constraints. + If the same name is specified explicitly or in another + LIKE clause an error is signalled. + diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 825245ae8d..3192dd32f0 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.186 2006/06/27 03:21:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.187 2006/06/27 03:43:19 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -159,7 +159,7 @@ typedef struct NewColumnValue static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, bool istemp, List **supOids, List **supconstr, int *supOidCount); -static bool change_varattnos_of_a_node(Node *node, const AttrNumber *newattno); +static bool change_varattnos_walker(Node *node, const AttrNumber *newattno); static void StoreCatalogInheritance(Oid relationId, List *supers); static int findAttrByName(const char *attributeName, List *schema); static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass); @@ -1106,14 +1106,59 @@ MergeAttributes(List *schema, List *supers, bool istemp, } /* - * complementary static functions for MergeAttributes(). - * * Varattnos of pg_constraint.conbin must be rewritten when subclasses inherit * constraints from parent classes, since the inherited attributes could * be given different column numbers in multiple-inheritance cases. * * Note that the passed node tree is modified in place! + * + * This function is used elsewhere such as in analyze.c + * */ + +void +change_varattnos_of_a_node(Node *node, const AttrNumber *newattno) +{ + change_varattnos_walker(node, newattno); +} + +/* Generate a map for change_varattnos_of_a_node from two tupledesc's. */ + +AttrNumber * +varattnos_map(TupleDesc old, TupleDesc new) +{ + int i,j; + AttrNumber *attmap = palloc0(sizeof(AttrNumber)*old->natts); + for (i=1; i <= old->natts; i++) { + if (old->attrs[i-1]->attisdropped) { + attmap[i-1] = 0; + continue; + } + for (j=1; j<= new->natts; j++) + if (!strcmp(NameStr(old->attrs[i-1]->attname), NameStr(new->attrs[j-1]->attname))) + attmap[i-1] = j; + } + return attmap; +} + +/* Generate a map for change_varattnos_of_a_node from a tupledesc and a list of + * ColumnDefs */ + +AttrNumber * +varattnos_map_schema(TupleDesc old, List *schema) +{ + int i; + AttrNumber *attmap = palloc0(sizeof(AttrNumber)*old->natts); + for (i=1; i <= old->natts; i++) { + if (old->attrs[i-1]->attisdropped) { + attmap[i-1] = 0; + continue; + } + attmap[i-1] = findAttrByName(NameStr(old->attrs[i-1]->attname), schema); + } + return attmap; +} + static bool change_varattnos_walker(Node *node, const AttrNumber *newattno) { @@ -1140,12 +1185,6 @@ change_varattnos_walker(Node *node, const AttrNumber *newattno) (void *) newattno); } -static bool -change_varattnos_of_a_node(Node *node, const AttrNumber *newattno) -{ - return change_varattnos_walker(node, newattno); -} - /* * StoreCatalogInheritance * Updates the system catalogs with proper inheritance information. diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index e213df5b21..2f2111458e 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.336 2006/06/16 20:23:44 adunstan Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.337 2006/06/27 03:43:19 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -1940,7 +1940,7 @@ _copyInhRelation(InhRelation *from) InhRelation *newnode = makeNode(InhRelation); COPY_NODE_FIELD(relation); - COPY_SCALAR_FIELD(including_defaults); + COPY_NODE_FIELD(options); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index cc60fdd8dd..56d8d09a02 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -18,7 +18,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.272 2006/06/16 20:23:44 adunstan Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.273 2006/06/27 03:43:20 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -884,7 +884,7 @@ static bool _equalInhRelation(InhRelation *a, InhRelation *b) { COMPARE_NODE_FIELD(relation); - COMPARE_SCALAR_FIELD(including_defaults); + COMPARE_NODE_FIELD(options); return true; } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 4e30d2b96f..8b9b9eab53 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.335 2006/06/21 18:30:11 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.336 2006/06/27 03:43:20 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -21,6 +21,7 @@ #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/prepare.h" +#include "commands/tablecmds.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" @@ -1075,6 +1076,11 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, TupleConstr *constr; AclResult aclresult; + bool including_defaults = false; + bool including_constraints = false; + bool including_indexes = false; + ListCell *elem; + relation = heap_openrv(inhRelation->relation, AccessShareLock); if (relation->rd_rel->relkind != RELKIND_RELATION) @@ -1095,6 +1101,37 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, tupleDesc = RelationGetDescr(relation); constr = tupleDesc->constr; + foreach(elem, inhRelation->options) + { + int option = lfirst_int(elem); + switch (option) + { + case CREATE_TABLE_LIKE_INCLUDING_DEFAULTS: + including_defaults = true; + break; + case CREATE_TABLE_LIKE_EXCLUDING_DEFAULTS: + including_defaults = false; + break; + case CREATE_TABLE_LIKE_INCLUDING_CONSTRAINTS: + including_constraints = true; + break; + case CREATE_TABLE_LIKE_EXCLUDING_CONSTRAINTS: + including_constraints = false; + break; + case CREATE_TABLE_LIKE_INCLUDING_INDEXES: + including_indexes = true; + break; + case CREATE_TABLE_LIKE_EXCLUDING_INDEXES: + including_indexes = false; + break; + default: + elog(ERROR, "unrecognized CREATE TABLE LIKE option: %d", option); + } + } + + if (including_indexes) + elog(ERROR, "TODO"); + /* * Insert the inherited attributes into the cxt for the new table * definition. @@ -1123,7 +1160,7 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, def->typename = makeTypeNameFromOid(attribute->atttypid, attribute->atttypmod); def->inhcount = 0; - def->is_local = false; + def->is_local = true; def->is_not_null = attribute->attnotnull; def->raw_default = NULL; def->cooked_default = NULL; @@ -1138,7 +1175,7 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, /* * Copy default if any, and the default has been requested */ - if (attribute->atthasdef && inhRelation->including_defaults) + if (attribute->atthasdef && including_defaults) { char *this_default = NULL; AttrDefault *attrdef; @@ -1165,6 +1202,27 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, def->cooked_default = pstrdup(this_default); } } + + if (including_constraints && tupleDesc->constr) { + int ccnum; + AttrNumber *attmap = varattnos_map_schema(tupleDesc, cxt->columns); + + for(ccnum = 0; ccnum < tupleDesc->constr->num_check; ccnum++) { + char *ccname = tupleDesc->constr->check[ccnum].ccname; + char *ccbin = tupleDesc->constr->check[ccnum].ccbin; + Node *ccbin_node = stringToNode(ccbin); + Constraint *n = makeNode(Constraint); + + change_varattnos_of_a_node(ccbin_node, attmap); + + n->contype = CONSTR_CHECK; + n->name = pstrdup(ccname); + n->raw_expr = ccbin_node; + n->cooked_expr = NULL; + n->indexspace = NULL; + cxt->ckconstraints = lappend(cxt->ckconstraints, (Node*)n); + } + } /* * Close the parent rel, but keep our AccessShareLock on it until xact diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 14f8b43348..246d1ef3a3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.547 2006/06/16 23:50:48 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.548 2006/06/27 03:43:20 momjian Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -193,8 +193,6 @@ static void doNegateFloat(Value *v); opt_grant_grant_option opt_grant_admin_option opt_nowait opt_if_exists -%type like_including_defaults - %type OptRoleList %type OptRoleElem @@ -335,7 +333,9 @@ static void doNegateFloat(Value *v); %type unreserved_keyword func_name_keyword %type col_name_keyword reserved_keyword -%type TableConstraint TableLikeClause +%type TableConstraint TableLikeClause +%type TableLikeOptionList +%type TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr %type key_actions key_delete key_match key_update key_action @@ -385,7 +385,7 @@ static void doNegateFloat(Value *v); HANDLER HAVING HEADER_P HOLD HOUR_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT - INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P + INDEX INDEXES INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -1994,20 +1994,27 @@ ConstraintAttr: * which is a part of SQL 200N */ TableLikeClause: - LIKE qualified_name like_including_defaults + LIKE qualified_name TableLikeOptionList { InhRelation *n = makeNode(InhRelation); n->relation = $2; - n->including_defaults = $3; - + n->options = $3; $$ = (Node *)n; } ; -like_including_defaults: - INCLUDING DEFAULTS { $$ = true; } - | EXCLUDING DEFAULTS { $$ = false; } - | /* EMPTY */ { $$ = false; } +TableLikeOptionList: + TableLikeOptionList TableLikeOption { $$ = lappend_int($1, $2); } + | /* EMPTY */ { $$ = NIL; } + ; + +TableLikeOption: + INCLUDING DEFAULTS { $$ = CREATE_TABLE_LIKE_INCLUDING_DEFAULTS; } + | EXCLUDING DEFAULTS { $$ = CREATE_TABLE_LIKE_EXCLUDING_DEFAULTS; } + | INCLUDING CONSTRAINTS { $$ = CREATE_TABLE_LIKE_INCLUDING_CONSTRAINTS; } + | EXCLUDING CONSTRAINTS { $$ = CREATE_TABLE_LIKE_EXCLUDING_CONSTRAINTS; } + | INCLUDING INDEXES { $$ = CREATE_TABLE_LIKE_INCLUDING_INDEXES; } + | EXCLUDING INDEXES { $$ = CREATE_TABLE_LIKE_EXCLUDING_INDEXES; } ; @@ -8507,6 +8514,7 @@ unreserved_keyword: | INCLUDING | INCREMENT | INDEX + | INDEXES | INHERIT | INHERITS | INPUT_P diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index 4b1a53762e..6a022f585d 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.171 2006/03/05 15:58:32 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.172 2006/06/27 03:43:20 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -169,6 +169,7 @@ static const ScanKeyword ScanKeywords[] = { {"including", INCLUDING}, {"increment", INCREMENT}, {"index", INDEX}, + {"indexes", INDEXES}, {"inherit", INHERIT}, {"inherits", INHERITS}, {"initially", INITIALLY}, diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 79d2049cd0..3452713bd0 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/tablecmds.h,v 1.27 2006/03/05 15:58:55 momjian Exp $ + * $PostgreSQL: pgsql/src/include/commands/tablecmds.h,v 1.28 2006/06/27 03:43:20 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -16,6 +16,7 @@ #include "nodes/parsenodes.h" #include "utils/rel.h" +#include "access/tupdesc.h" extern Oid DefineRelation(CreateStmt *stmt, char relkind); @@ -47,6 +48,10 @@ extern void renameatt(Oid myrelid, extern void renamerel(Oid myrelid, const char *newrelname); +extern AttrNumber * varattnos_map(TupleDesc old, TupleDesc new); +extern AttrNumber * varattnos_map_schema(TupleDesc old, List *schema); +extern void change_varattnos_of_a_node(Node *node, const AttrNumber *newattno); + extern void register_on_commit_action(Oid relid, OnCommitAction action); extern void remove_on_commit_action(Oid relid); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 56d41a2fd8..2fb77fd685 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.311 2006/06/16 20:23:45 adunstan Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.312 2006/06/27 03:43:20 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -403,7 +403,7 @@ typedef struct InhRelation { NodeTag type; RangeVar *relation; - bool including_defaults; + List *options; } InhRelation; /* @@ -1026,6 +1026,15 @@ typedef struct CreateStmt char *tablespacename; /* table space to use, or NULL */ } CreateStmt; +typedef enum CreateStmtLikeOption { + CREATE_TABLE_LIKE_INCLUDING_DEFAULTS, + CREATE_TABLE_LIKE_EXCLUDING_DEFAULTS, + CREATE_TABLE_LIKE_INCLUDING_CONSTRAINTS, + CREATE_TABLE_LIKE_EXCLUDING_CONSTRAINTS, + CREATE_TABLE_LIKE_INCLUDING_INDEXES, + CREATE_TABLE_LIKE_EXCLUDING_INDEXES, +} CreateStmtLikeOption; + /* ---------- * Definitions for plain (non-FOREIGN KEY) constraints in CreateStmt * diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 9517c36baa..f4bdb8d825 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -606,7 +606,7 @@ SELECT * FROM a; /* Has ee entry */ CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ ERROR: column "xx" duplicated -CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS); +CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); INSERT INTO inhf DEFAULT VALUES; SELECT * FROM inhf; /* Single entry with value 'text' */ xx @@ -614,6 +614,25 @@ SELECT * FROM inhf; /* Single entry with value 'text' */ text (1 row) +ALTER TABLE inhx add constraint foo CHECK (xx = 'text'); +ALTER TABLE inhx ADD PRIMARY KEY (xx); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx" +CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */ +INSERT INTO inhg VALUES ('foo'); +DROP TABLE inhg; +CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */ +INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */ +INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */ +INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */ +ERROR: new row for relation "inhg" violates check constraint "foo" +SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ + x | xx | y +---+------+--- + x | text | y + x | text | y +(2 rows) + +DROP TABLE inhg; -- Test changing the type of inherited columns insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index d1b9e21530..35c44d7a46 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -23,6 +23,7 @@ SELECT relname, relhasindex hash_name_heap | t hash_txt_heap | t ihighway | t + inhx | t num_exp_add | t num_exp_div | t num_exp_ln | t @@ -70,7 +71,7 @@ SELECT relname, relhasindex shighway | t tenk1 | t tenk2 | t -(60 rows) +(61 rows) -- -- another sanity check: every system catalog that has OIDs should have diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index daeabb2c96..cd4221f899 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -140,10 +140,23 @@ SELECT * FROM a; /* Has ee entry */ CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ -CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS); +CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); INSERT INTO inhf DEFAULT VALUES; SELECT * FROM inhf; /* Single entry with value 'text' */ +ALTER TABLE inhx add constraint foo CHECK (xx = 'text'); +ALTER TABLE inhx ADD PRIMARY KEY (xx); +CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */ +INSERT INTO inhg VALUES ('foo'); +DROP TABLE inhg; +CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */ +INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */ +INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */ +INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */ +SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ +DROP TABLE inhg; + + -- Test changing the type of inherited columns insert into d values('test','one','two','three'); alter table a alter column aa type integer using bit_length(aa); -- 2.11.0