Index: doc/src/sgml/ref/create_table.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v retrieving revision 1.100 diff -c -p -c -r1.100 create_table.sgml *** doc/src/sgml/ref/create_table.sgml 19 Feb 2006 00:04:26 -0000 1.100 --- doc/src/sgml/ref/create_table.sgml 20 Jun 2006 21:57:52 -0000 *************** PostgreSQL documentation *** 23,29 **** 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 ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] --- 23,29 ---- 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 | CONSTRAINTS } ] ... } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] *************** and table *** 232,238 **** ! LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] The LIKE clause specifies a table from which --- 232,238 ---- ! LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] The LIKE clause specifies a table from which *************** and table *** 252,257 **** --- 252,271 ---- 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. + Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.184 diff -c -p -c -r1.184 tablecmds.c *** src/backend/commands/tablecmds.c 10 May 2006 23:18:39 -0000 1.184 --- src/backend/commands/tablecmds.c 20 Jun 2006 21:57:53 -0000 *************** typedef struct NewColumnValue *** 159,165 **** 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 void StoreCatalogInheritance(Oid relationId, List *supers); static int findAttrByName(const char *attributeName, List *schema); static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass); --- 159,165 ---- 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_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); *************** MergeAttributes(List *schema, List *supe *** 1106,1119 **** } /* - * 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! */ static bool change_varattnos_walker(Node *node, const AttrNumber *newattno) { --- 1106,1164 ---- } /* * 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) { *************** change_varattnos_walker(Node *node, cons *** 1140,1151 **** (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. --- 1185,1190 ---- Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v retrieving revision 1.335 diff -c -p -c -r1.335 copyfuncs.c *** src/backend/nodes/copyfuncs.c 30 Apr 2006 18:30:38 -0000 1.335 --- src/backend/nodes/copyfuncs.c 20 Jun 2006 21:57:53 -0000 *************** _copyInhRelation(InhRelation *from) *** 1940,1946 **** InhRelation *newnode = makeNode(InhRelation); COPY_NODE_FIELD(relation); ! COPY_SCALAR_FIELD(including_defaults); return newnode; } --- 1940,1946 ---- InhRelation *newnode = makeNode(InhRelation); COPY_NODE_FIELD(relation); ! COPY_NODE_FIELD(options); return newnode; } Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v retrieving revision 1.271 diff -c -p -c -r1.271 equalfuncs.c *** src/backend/nodes/equalfuncs.c 30 Apr 2006 18:30:38 -0000 1.271 --- src/backend/nodes/equalfuncs.c 20 Jun 2006 21:57:53 -0000 *************** static bool *** 884,890 **** _equalInhRelation(InhRelation *a, InhRelation *b) { COMPARE_NODE_FIELD(relation); ! COMPARE_SCALAR_FIELD(including_defaults); return true; } --- 884,890 ---- _equalInhRelation(InhRelation *a, InhRelation *b) { COMPARE_NODE_FIELD(relation); ! COMPARE_NODE_FIELD(options); return true; } Index: src/backend/parser/analyze.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v retrieving revision 1.334 diff -c -p -c -r1.334 analyze.c *** src/backend/parser/analyze.c 30 Apr 2006 18:30:39 -0000 1.334 --- src/backend/parser/analyze.c 20 Jun 2006 21:57:53 -0000 *************** *** 21,26 **** --- 21,27 ---- #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" *************** transformInhRelation(ParseState *pstate, *** 1075,1080 **** --- 1076,1086 ---- 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) *************** transformInhRelation(ParseState *pstate, *** 1095,1100 **** --- 1101,1137 ---- 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. *************** transformInhRelation(ParseState *pstate, *** 1123,1129 **** def->typename = makeTypeNameFromOid(attribute->atttypid, attribute->atttypmod); def->inhcount = 0; ! def->is_local = false; def->is_not_null = attribute->attnotnull; def->raw_default = NULL; def->cooked_default = NULL; --- 1160,1166 ---- def->typename = makeTypeNameFromOid(attribute->atttypid, attribute->atttypmod); def->inhcount = 0; ! def->is_local = true; def->is_not_null = attribute->attnotnull; def->raw_default = NULL; def->cooked_default = NULL; *************** transformInhRelation(ParseState *pstate, *** 1138,1144 **** /* * Copy default if any, and the default has been requested */ ! if (attribute->atthasdef && inhRelation->including_defaults) { char *this_default = NULL; AttrDefault *attrdef; --- 1175,1181 ---- /* * Copy default if any, and the default has been requested */ ! if (attribute->atthasdef && including_defaults) { char *this_default = NULL; AttrDefault *attrdef; *************** transformInhRelation(ParseState *pstate, *** 1165,1170 **** --- 1202,1228 ---- 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 Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.545 diff -c -p -c -r2.545 gram.y *** src/backend/parser/gram.y 27 May 2006 17:38:45 -0000 2.545 --- src/backend/parser/gram.y 20 Jun 2006 21:57:54 -0000 *************** static void doNegateFloat(Value *v); *** 193,199 **** opt_grant_grant_option opt_grant_admin_option opt_nowait ! %type like_including_defaults %type OptRoleList %type OptRoleElem --- 193,199 ---- opt_grant_grant_option opt_grant_admin_option opt_nowait ! /*%type like_including_defaults*/ %type OptRoleList %type OptRoleElem *************** static void doNegateFloat(Value *v); *** 335,341 **** %type unreserved_keyword func_name_keyword %type col_name_keyword reserved_keyword ! %type TableConstraint TableLikeClause %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr %type key_actions key_delete key_match key_update key_action --- 335,343 ---- %type unreserved_keyword func_name_keyword %type col_name_keyword reserved_keyword ! %type TableConstraint TableLikeClause ! %type TableLikeOptionList ! %type TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr %type key_actions key_delete key_match key_update key_action *************** static void doNegateFloat(Value *v); *** 385,391 **** 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 INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION --- 387,393 ---- HANDLER HAVING HEADER_P HOLD HOUR_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT ! INDEX INDEXES INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION *************** ConstraintAttr: *** 1994,2013 **** * which is a part of SQL 200N */ TableLikeClause: ! LIKE qualified_name like_including_defaults { InhRelation *n = makeNode(InhRelation); n->relation = $2; ! n->including_defaults = $3; ! $$ = (Node *)n; } ; ! like_including_defaults: ! INCLUDING DEFAULTS { $$ = true; } ! | EXCLUDING DEFAULTS { $$ = false; } ! | /* EMPTY */ { $$ = false; } ; --- 1996,2022 ---- * which is a part of SQL 200N */ TableLikeClause: ! LIKE qualified_name TableLikeOptionList { InhRelation *n = makeNode(InhRelation); n->relation = $2; ! n->options = $3; $$ = (Node *)n; } ; ! 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; } ; *************** unreserved_keyword: *** 8421,8426 **** --- 8430,8436 ---- | INCLUDING | INCREMENT | INDEX + | INDEXES | INHERIT | INHERITS | INPUT_P Index: src/backend/parser/keywords.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v retrieving revision 1.171 diff -c -p -c -r1.171 keywords.c *** src/backend/parser/keywords.c 5 Mar 2006 15:58:32 -0000 1.171 --- src/backend/parser/keywords.c 20 Jun 2006 21:57:55 -0000 *************** static const ScanKeyword ScanKeywords[] *** 169,174 **** --- 169,175 ---- {"including", INCLUDING}, {"increment", INCREMENT}, {"index", INDEX}, + {"indexes", INDEXES}, {"inherit", INHERIT}, {"inherits", INHERITS}, {"initially", INITIALLY}, Index: src/include/commands/tablecmds.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/commands/tablecmds.h,v retrieving revision 1.27 diff -c -p -c -r1.27 tablecmds.h *** src/include/commands/tablecmds.h 5 Mar 2006 15:58:55 -0000 1.27 --- src/include/commands/tablecmds.h 20 Jun 2006 21:57:55 -0000 *************** *** 16,21 **** --- 16,22 ---- #include "nodes/parsenodes.h" #include "utils/rel.h" + #include "access/tupdesc.h" extern Oid DefineRelation(CreateStmt *stmt, char relkind); *************** extern void renameatt(Oid myrelid, *** 47,52 **** --- 48,57 ---- 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); Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v retrieving revision 1.310 diff -c -p -c -r1.310 parsenodes.h *** src/include/nodes/parsenodes.h 30 Apr 2006 18:30:40 -0000 1.310 --- src/include/nodes/parsenodes.h 20 Jun 2006 21:57:55 -0000 *************** typedef struct InhRelation *** 403,409 **** { NodeTag type; RangeVar *relation; ! bool including_defaults; } InhRelation; /* --- 403,409 ---- { NodeTag type; RangeVar *relation; ! List *options; } InhRelation; /* *************** typedef struct CreateStmt *** 1026,1031 **** --- 1026,1040 ---- 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 * Index: src/test/regress/expected/inherit.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/inherit.out,v retrieving revision 1.19 diff -c -p -c -r1.19 inherit.out *** src/test/regress/expected/inherit.out 12 Dec 2004 22:49:49 -0000 1.19 --- src/test/regress/expected/inherit.out 20 Jun 2006 21:57:55 -0000 *************** SELECT * FROM a; /* Has ee entry */ *** 606,612 **** CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ ERROR: column "xx" duplicated ! CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS); INSERT INTO inhf DEFAULT VALUES; SELECT * FROM inhf; /* Single entry with value 'text' */ xx --- 606,612 ---- CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ ERROR: column "xx" duplicated ! CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); INSERT INTO inhf DEFAULT VALUES; SELECT * FROM inhf; /* Single entry with value 'text' */ xx *************** SELECT * FROM inhf; /* Single entry with *** 614,619 **** --- 614,638 ---- 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); Index: src/test/regress/sql/inherit.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/inherit.sql,v retrieving revision 1.9 diff -c -p -c -r1.9 inherit.sql *** src/test/regress/sql/inherit.sql 12 Dec 2004 22:49:50 -0000 1.9 --- src/test/regress/sql/inherit.sql 20 Jun 2006 21:57:55 -0000 *************** SELECT * FROM a; /* Has ee entry */ *** 140,149 **** CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ ! CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS); INSERT INTO inhf DEFAULT VALUES; SELECT * FROM inhf; /* Single entry with value 'text' */ -- 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); --- 140,162 ---- CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ ! 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);