Index: doc/src/sgml/ref/create_table.sgml =================================================================== RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/create_table.sgml,v retrieving revision 1.68 diff -c -r1.68 create_table.sgml *** doc/src/sgml/ref/create_table.sgml 2003/05/04 00:03:55 1.68 --- doc/src/sgml/ref/create_table.sgml 2003/05/12 13:49:45 *************** *** 18,24 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ] ! | table_constraint } [, ... ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] --- 18,25 ---- CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ] ! | table_constraint ! | LIKE parent_table } [, ... ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] *************** *** 168,173 **** --- 169,190 ---- The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null. + + + + + + LIKE parent_table + + + The LIKE clause specifies a table from which + the new table automatically inherits all column names, column datatypes, and + NOT NULL constraints. + + + Unlike INHERITS the new table and inherited table + are completly decoupled after creation has been completed. Data inserted + into the new table will not be reflected in the parent table. Index: src/backend/catalog/sql_features.txt =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/catalog/sql_features.txt,v retrieving revision 1.4 diff -c -r1.4 sql_features.txt *** src/backend/catalog/sql_features.txt 2003/04/29 03:21:29 1.4 --- src/backend/catalog/sql_features.txt 2003/05/12 13:49:45 *************** *** 308,314 **** T131 Recursive query NO T141 SIMILAR predicate YES T151 DISTINCT predicate YES ! T171 LIKE clause in table definition NO T191 Referential action RESTRICT YES T201 Comparable data types for referential constraints YES T211 Basic trigger capability NO --- 308,314 ---- T131 Recursive query NO T141 SIMILAR predicate YES T151 DISTINCT predicate YES ! T171 LIKE clause in table definition YES T191 Referential action RESTRICT YES T201 Comparable data types for referential constraints YES T211 Basic trigger capability NO Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v retrieving revision 1.72 diff -c -r1.72 tablecmds.c *** src/backend/commands/tablecmds.c 2003/04/29 22:13:08 1.72 --- src/backend/commands/tablecmds.c 2003/05/12 13:49:45 *************** *** 509,517 **** { List *entry; List *inhSchema = NIL; ! List *parentOids = NIL; List *constraints = NIL; ! bool parentHasOids = false; bool have_bogus_defaults = false; char *bogus_marker = "Bogus!"; /* marks conflicting * defaults */ --- 509,518 ---- { List *entry; List *inhSchema = NIL; ! List *dataParentOids = NIL; ! List *structureParentOids = NIL; List *constraints = NIL; ! bool dataParentHasOids = false; bool have_bogus_defaults = false; char *bogus_marker = "Bogus!"; /* marks conflicting * defaults */ *************** *** 547,553 **** child_attno = 0; foreach(entry, supers) { ! RangeVar *parent = (RangeVar *) lfirst(entry); Relation relation; TupleDesc tupleDesc; TupleConstr *constr; --- 548,555 ---- child_attno = 0; foreach(entry, supers) { ! InhRelation *inhRel = (InhRelation *) lfirst(entry); ! RangeVar *parent = inhRel->relation; Relation relation; TupleDesc tupleDesc; TupleConstr *constr; *************** *** 559,588 **** if (relation->rd_rel->relkind != RELKIND_RELATION) elog(ERROR, "CREATE TABLE: inherited relation \"%s\" is not a table", parent->relname); ! /* Permanent rels cannot inherit from temporary ones */ ! if (!istemp && isTempNamespace(RelationGetNamespace(relation))) elog(ERROR, "CREATE TABLE: cannot inherit from temp relation \"%s\"", parent->relname); /* * We should have an UNDER permission flag for this, but for now, * demand that creator of a child table own the parent. */ ! if (!pg_class_ownercheck(RelationGetRelid(relation), GetUserId())) aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(relation)); /* ! * Reject duplications in the list of parents. */ ! if (oidMember(RelationGetRelid(relation), parentOids)) elog(ERROR, "CREATE TABLE: inherited relation \"%s\" duplicated", parent->relname); ! parentOids = lappendo(parentOids, RelationGetRelid(relation)); ! setRelhassubclassInRelation(RelationGetRelid(relation), true); ! parentHasOids |= relation->rd_rel->relhasoids; tupleDesc = RelationGetDescr(relation); constr = tupleDesc->constr; --- 561,616 ---- if (relation->rd_rel->relkind != RELKIND_RELATION) elog(ERROR, "CREATE TABLE: inherited relation \"%s\" is not a table", parent->relname); ! ! /* ! * Permanent rels cannot inherit from temporary ones if we're inheriting ! * data as well as structure. ! */ ! if (!istemp && isTempNamespace(RelationGetNamespace(relation)) ! && !inhRel->structure_only) elog(ERROR, "CREATE TABLE: cannot inherit from temp relation \"%s\"", parent->relname); /* * We should have an UNDER permission flag for this, but for now, * demand that creator of a child table own the parent. + * + * If we're inheriting only the structure, then the SELECT privilage + * will suffice. */ ! if (!inhRel->structure_only ! && !pg_class_ownercheck(RelationGetRelid(relation), GetUserId())) aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(relation)); + else if (inhRel->structure_only) + { + AclResult aclresult; + aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(), ACL_SELECT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, + RelationGetRelationName(relation)); + } + /* ! * Reject duplications in the either list of parents. */ ! if (oidMember(RelationGetRelid(relation), dataParentOids) ! || oidMember(RelationGetRelid(relation), structureParentOids)) elog(ERROR, "CREATE TABLE: inherited relation \"%s\" duplicated", parent->relname); ! /* Keep track of each set of inherited parents in separate lists */ ! if (inhRel->structure_only) ! structureParentOids = lappendo(structureParentOids, RelationGetRelid(relation)); ! else ! { ! dataParentOids = lappendo(dataParentOids, RelationGetRelid(relation)); ! setRelhassubclassInRelation(RelationGetRelid(relation), true); ! /* Data based parent -- we want to inherit their OID attribute */ ! dataParentHasOids |= relation->rd_rel->relhasoids; ! } tupleDesc = RelationGetDescr(relation); constr = tupleDesc->constr; *************** *** 667,675 **** } /* ! * Copy default if any */ ! if (attribute->atthasdef) { char *this_default = NULL; AttrDefault *attrdef; --- 695,703 ---- } /* ! * Copy default if any, and we've been asked to include defaults */ ! if (attribute->atthasdef && inhRel->including_defaults) { char *this_default = NULL; AttrDefault *attrdef; *************** *** 711,720 **** } /* ! * Now copy the constraints of this parent, adjusting attnos using ! * the completed newattno[] map */ ! if (constr && constr->num_check > 0) { ConstrCheck *check = constr->check; int i; --- 739,749 ---- } /* ! * Now copy the constraints of this parent if we've been asked ! * to inherit constraints. Adjust attnos using the completed ! * newattno[] map */ ! if (constr && constr->num_check > 0 && inhRel->including_constraints) { ConstrCheck *check = constr->check; int i; *************** *** 829,837 **** } } ! *supOids = parentOids; *supconstr = constraints; ! *supHasOids = parentHasOids; return schema; } --- 858,866 ---- } } ! *supOids = dataParentOids; *supconstr = constraints; ! *supHasOids = dataParentHasOids; return schema; } Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/nodes/copyfuncs.c,v retrieving revision 1.250 diff -c -r1.250 copyfuncs.c *** src/backend/nodes/copyfuncs.c 2003/05/06 00:20:32 1.250 --- src/backend/nodes/copyfuncs.c 2003/05/12 13:49:45 *************** *** 1723,1728 **** --- 1723,1741 ---- return newnode; } + static InhRelation * + _copyInhRelation(InhRelation *from) + { + InhRelation *newnode = makeNode(InhRelation); + + COPY_NODE_FIELD(relation); + COPY_SCALAR_FIELD(structure_only); + COPY_SCALAR_FIELD(including_constraints); + COPY_SCALAR_FIELD(including_defaults); + + return newnode; + } + static DefineStmt * _copyDefineStmt(DefineStmt *from) { *************** *** 2684,2689 **** --- 2697,2705 ---- break; case T_CreateStmt: retval = _copyCreateStmt(from); + break; + case T_InhRelation: + retval = _copyInhRelation(from); break; case T_DefineStmt: retval = _copyDefineStmt(from); Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/nodes/equalfuncs.c,v retrieving revision 1.193 diff -c -r1.193 equalfuncs.c *** src/backend/nodes/equalfuncs.c 2003/05/06 00:20:32 1.193 --- src/backend/nodes/equalfuncs.c 2003/05/12 13:49:46 *************** *** 777,782 **** --- 777,793 ---- } static bool + _equalInhRelation(InhRelation *a, InhRelation *b) + { + COMPARE_NODE_FIELD(relation); + COMPARE_SCALAR_FIELD(structure_only); + COMPARE_SCALAR_FIELD(including_constraints); + COMPARE_SCALAR_FIELD(including_defaults); + + return true; + } + + static bool _equalDefineStmt(DefineStmt *a, DefineStmt *b) { COMPARE_SCALAR_FIELD(kind); *************** *** 1798,1803 **** --- 1809,1817 ---- break; case T_CreateStmt: retval = _equalCreateStmt(a, b); + break; + case T_InhRelation: + retval = _equalInhRelation(a,b); break; case T_DefineStmt: retval = _equalDefineStmt(a, b); Index: src/backend/parser/analyze.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/analyze.c,v retrieving revision 1.271 diff -c -r1.271 analyze.c *** src/backend/parser/analyze.c 2003/05/06 00:20:32 1.271 --- src/backend/parser/analyze.c 2003/05/12 13:49:46 *************** *** 880,885 **** --- 880,890 ---- cxt.fkconstraints = lappend(cxt.fkconstraints, element); break; + case T_InhRelation: + /* No pre-transformation needed */ + cxt.inhRelations = lappend(cxt.inhRelations, element); + break; + default: elog(ERROR, "parser: unrecognized node (internal error)"); } *************** *** 905,910 **** --- 910,916 ---- q->utilityStmt = (Node *) stmt; stmt->tableElts = cxt.columns; stmt->constraints = cxt.ckconstraints; + stmt->inhRelations = cxt.inhRelations; *extras_before = nconc(*extras_before, cxt.blist); *extras_after = nconc(cxt.alist, *extras_after); *************** *** 1244,1250 **** foreach(inher, cxt->inhRelations) { ! RangeVar *inh = lfirst(inher); Relation rel; int count; --- 1250,1257 ---- foreach(inher, cxt->inhRelations) { ! InhRelation *inhRel = (InhRelation *) lfirst(inher); ! RangeVar *inh = inhRel->relation; Relation rel; int count; Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.413 diff -c -r2.413 gram.y *** src/backend/parser/gram.y 2003/05/04 00:03:55 2.413 --- src/backend/parser/gram.y 2003/05/12 13:49:48 *************** *** 220,227 **** transaction_mode_list_or_empty TableFuncElementList prep_type_clause prep_type_list ! execute_param_clause %type into_clause OptTempTableName %type createfunc_opt_item --- 220,229 ---- transaction_mode_list_or_empty TableFuncElementList prep_type_clause prep_type_list ! execute_param_clause inherited_list + %type inherited_relation + %type into_clause OptTempTableName %type createfunc_opt_item *************** *** 1643,1657 **** ; ! /* SQL99 supports wholesale borrowing of a table definition via the LIKE clause. * This seems to be a poor man's inheritance capability, with the resulting * tables completely decoupled except for the original commonality in definitions. ! * Seems to have much in common with CREATE TABLE AS. - thomas 2002-06-19 */ ! TableLikeClause: LIKE any_name { ! elog(ERROR, "LIKE in table definitions not yet supported"); ! $$ = NULL; } ; --- 1645,1667 ---- ; ! /* ! * SQL99 supports wholesale borrowing of a table definition via the LIKE clause. * This seems to be a poor man's inheritance capability, with the resulting * tables completely decoupled except for the original commonality in definitions. ! * ! * This is very similar to CREATE TABLE AS except for the INCLUDING DEFAULTS extension ! * which is a part of SQL 200N */ ! TableLikeClause: LIKE qualified_name { ! InhRelation *n = makeNode(InhRelation); ! n->relation = $2; ! n->structure_only = true; ! n->including_constraints = false; ! n->including_defaults = false; ! ! $$ = (Node *)n; } ; *************** *** 1800,1808 **** | SET DEFAULT { $$ = FKCONSTR_ACTION_SETDEFAULT; } ; ! OptInherit: INHERITS '(' qualified_name_list ')' { $$ = $3; } | /*EMPTY*/ { $$ = NIL; } ; OptWithOids: WITH OIDS { $$ = TRUE; } --- 1810,1836 ---- | SET DEFAULT { $$ = FKCONSTR_ACTION_SETDEFAULT; } ; ! OptInherit: INHERITS '(' inherited_list ')' { $$ = $3; } | /*EMPTY*/ { $$ = NIL; } ; + + inherited_list: + inherited_relation { $$ = makeList1($1); } + | inherited_list ',' inherited_relation { $$ = lappend($1, $3); } + ; + + inherited_relation: + qualified_name + { + InhRelation *n = makeNode(InhRelation); + n->relation = $1; + n->structure_only = false; + n->including_constraints = true; + n->including_defaults = true; + $$ = (Node *)n; + } + ; + OptWithOids: WITH OIDS { $$ = TRUE; } Index: src/include/nodes/nodes.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/nodes.h,v retrieving revision 1.140 diff -c -r1.140 nodes.h *** src/include/nodes/nodes.h 2003/04/08 23:20:04 1.140 --- src/include/nodes/nodes.h 2003/05/12 13:49:49 *************** *** 280,285 **** --- 280,286 ---- T_InsertDefault, T_CreateOpClassItem, T_CompositeTypeStmt, + T_InhRelation, /* * TAGS FOR FUNCTION-CALL CONTEXT AND RESULTINFO NODES (see fmgr.h) Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.237 diff -c -r1.237 parsenodes.h *** src/include/nodes/parsenodes.h 2003/05/02 20:54:36 1.237 --- src/include/nodes/parsenodes.h 2003/05/12 13:49:50 *************** *** 351,356 **** --- 351,368 ---- } ColumnDef; /* + * inhRelation - Relations a CREATE TABLE is to inherit attributes of + */ + typedef struct InhRelation + { + NodeTag type; + RangeVar *relation; + bool structure_only; + bool including_constraints; + bool including_defaults; + } InhRelation; + + /* * IndexElem - index parameters (used in CREATE INDEX) * * For a plain index, each 'name' is an attribute name in the heap relation; *************** *** 853,859 **** NodeTag type; RangeVar *relation; /* relation to create */ List *tableElts; /* column definitions (list of ColumnDef) */ ! List *inhRelations; /* relations to inherit from */ List *constraints; /* constraints (list of Constraint nodes) */ bool hasoids; /* should it have OIDs? */ OnCommitAction oncommit; /* what do we do at COMMIT? */ --- 865,871 ---- NodeTag type; RangeVar *relation; /* relation to create */ List *tableElts; /* column definitions (list of ColumnDef) */ ! List *inhRelations; /* relations to inherit from (list of inhRelation) */ List *constraints; /* constraints (list of Constraint nodes) */ bool hasoids; /* should it have OIDs? */ OnCommitAction oncommit; /* what do we do at COMMIT? */ Index: src/test/regress/expected/inherit.out =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/inherit.out,v retrieving revision 1.8 diff -c -r1.8 inherit.out *** src/test/regress/expected/inherit.out 2003/03/05 20:01:04 1.8 --- src/test/regress/expected/inherit.out 2003/05/12 13:49:51 *************** *** 570,572 **** --- 570,599 ---- bar2 | 3 | 103 (8 rows) + /* Test inheritance of structure (LIKE) */ + CREATE TABLE inhx (xx text); + CREATE TABLE inhe (ee text, LIKE inhx) inherits (b); + INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', 'ee-col3', 'ee-col4'); + SELECT * FROM inhe; /* Columns aa, bb, xx, ee */ + aa | bb | xx | ee + ---------+---------+---------+--------- + ee-col1 | ee-col2 | ee-col3 | ee-col4 + (1 row) + + SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ + xx + ---- + (0 rows) + + SELECT * FROM b; /* Has ee entry */ + aa | bb + ---------+--------- + ee-col1 | ee-col2 + (1 row) + + SELECT * FROM a; /* Has ee entry */ + aa + --------- + ee-col1 + (1 row) + Index: src/test/regress/sql/inherit.sql =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/inherit.sql,v retrieving revision 1.5 diff -c -r1.5 inherit.sql *** src/test/regress/sql/inherit.sql 2003/03/05 20:01:04 1.5 --- src/test/regress/sql/inherit.sql 2003/05/12 13:49:51 *************** *** 119,121 **** --- 119,132 ---- update bar set f2 = f2 + 100 where f1 in (select f1 from foo); SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid; + + + /* Test inheritance of structure (LIKE) */ + CREATE TABLE inhx (xx text); + + CREATE TABLE inhe (ee text, LIKE inhx) inherits (b); + INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', 'ee-col3', 'ee-col4'); + SELECT * FROM inhe; /* Columns aa, bb, xx, ee */ + SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ + SELECT * FROM b; /* Has ee entry */ + SELECT * FROM a; /* Has ee entry */