Index: backend/catalog/sql_features.txt =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/catalog/sql_features.txt,v retrieving revision 1.4 diff -c -r1.4 sql_features.txt *** backend/catalog/sql_features.txt 29 Apr 2003 03:21:29 -0000 1.4 --- backend/catalog/sql_features.txt 12 May 2003 01:19:26 -0000 *************** *** 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: backend/nodes/copyfuncs.c =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/nodes/copyfuncs.c,v retrieving revision 1.250 diff -c -r1.250 copyfuncs.c *** backend/nodes/copyfuncs.c 6 May 2003 00:20:32 -0000 1.250 --- backend/nodes/copyfuncs.c 20 May 2003 01:24:04 -0000 *************** *** 1723,1728 **** --- 1723,1739 ---- return newnode; } + static InhRelation * + _copyInhRelation(InhRelation *from) + { + InhRelation *newnode = makeNode(InhRelation); + + COPY_NODE_FIELD(relation); + COPY_SCALAR_FIELD(including_defaults); + + return newnode; + } + static DefineStmt * _copyDefineStmt(DefineStmt *from) { *************** *** 2684,2689 **** --- 2695,2703 ---- break; case T_CreateStmt: retval = _copyCreateStmt(from); + break; + case T_InhRelation: + retval = _copyInhRelation(from); break; case T_DefineStmt: retval = _copyDefineStmt(from); Index: backend/nodes/equalfuncs.c =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/nodes/equalfuncs.c,v retrieving revision 1.193 diff -c -r1.193 equalfuncs.c *** backend/nodes/equalfuncs.c 6 May 2003 00:20:32 -0000 1.193 --- backend/nodes/equalfuncs.c 20 May 2003 01:24:13 -0000 *************** *** 777,782 **** --- 777,791 ---- } static bool + _equalInhRelation(InhRelation *a, InhRelation *b) + { + COMPARE_NODE_FIELD(relation); + COMPARE_SCALAR_FIELD(including_defaults); + + return true; + } + + static bool _equalDefineStmt(DefineStmt *a, DefineStmt *b) { COMPARE_SCALAR_FIELD(kind); *************** *** 1798,1803 **** --- 1807,1815 ---- 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: backend/parser/analyze.c =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/parser/analyze.c,v retrieving revision 1.271 diff -c -r1.271 analyze.c *** backend/parser/analyze.c 6 May 2003 00:20:32 -0000 1.271 --- backend/parser/analyze.c 22 May 2003 02:04:34 -0000 *************** *** 21,26 **** --- 21,27 ---- #include "catalog/pg_index.h" #include "catalog/pg_type.h" #include "commands/prepare.h" + #include "miscadmin.h" #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/var.h" *************** *** 37,42 **** --- 38,44 ---- #include "parser/parse_type.h" #include "parser/parse_expr.h" #include "rewrite/rewriteManip.h" + #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" *************** *** 112,124 **** static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt, List **extras_before, List **extras_after); static void transformColumnDefinition(ParseState *pstate, ! CreateStmtContext *cxt, ! ColumnDef *column); static void transformTableConstraint(ParseState *pstate, ! CreateStmtContext *cxt, ! Constraint *constraint); static void transformIndexConstraints(ParseState *pstate, ! CreateStmtContext *cxt); static void transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt, bool isAddConstraint); --- 114,128 ---- static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt, List **extras_before, List **extras_after); static void transformColumnDefinition(ParseState *pstate, ! CreateStmtContext *cxt, ! ColumnDef *column); static void transformTableConstraint(ParseState *pstate, ! CreateStmtContext *cxt, ! Constraint *constraint); ! static void transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, ! InhRelation *inhrelation); static void transformIndexConstraints(ParseState *pstate, ! CreateStmtContext *cxt); static void transformFKConstraints(ParseState *pstate, CreateStmtContext *cxt, bool isAddConstraint); *************** *** 880,885 **** --- 884,894 ---- cxt.fkconstraints = lappend(cxt.fkconstraints, element); break; + case T_InhRelation: + transformInhRelation(pstate, &cxt, + (InhRelation *) element); + break; + default: elog(ERROR, "parser: unrecognized node (internal error)"); } *************** *** 1144,1149 **** --- 1153,1275 ---- elog(ERROR, "parser: unrecognized constraint (internal error)"); break; } + } + + /* + * transformInhRelation + * + * Change the LIKE portion of a CREATE TABLE statement into the + * column definitions which recreate the user defined column portions of . + */ + static void + transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, + InhRelation *inhRelation) + { + AttrNumber parent_attno; + + Relation relation; + TupleDesc tupleDesc; + TupleConstr *constr; + AclResult aclresult; + + relation = heap_openrv(inhRelation->relation, AccessShareLock); + + if (relation->rd_rel->relkind != RELKIND_RELATION) + elog(ERROR, "CREATE TABLE: inherited relation \"%s\" is not a table", + inhRelation->relation->relname); + + /* + * Check for SELECT privilages + */ + aclresult = pg_class_aclcheck(RelationGetRelid(relation), GetUserId(), + ACL_SELECT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, RelationGetRelationName(relation)); + + tupleDesc = RelationGetDescr(relation); + constr = tupleDesc->constr; + + /* + * Insert the inherited attributes into the cxt for the + * new table definition. + */ + for (parent_attno = 1; parent_attno <= tupleDesc->natts; + parent_attno++) + { + Form_pg_attribute attribute = tupleDesc->attrs[parent_attno - 1]; + char *attributeName = NameStr(attribute->attname); + ColumnDef *def; + TypeName *typename; + + /* + * Ignore dropped columns in the parent. + */ + if (attribute->attisdropped) + continue; + + /* + * Create a new inherited column. + * + * For constraints, ONLY the NOT NULL constraint is inherited + * by the new column definition per SQL99. + */ + def = makeNode(ColumnDef); + def->colname = pstrdup(attributeName); + typename = makeNode(TypeName); + typename->typeid = attribute->atttypid; + typename->typmod = attribute->atttypmod; + def->typename = typename; + def->inhcount = 0; + def->is_local = false; + def->is_not_null = attribute->attnotnull; + def->raw_default = NULL; + def->cooked_default = NULL; + def->constraints = NIL; + def->support = NULL; + + /* + * Add to column list + */ + cxt->columns = lappend(cxt->columns, def); + + /* + * Copy default if any, and the default has been requested + */ + if (attribute->atthasdef && inhRelation->including_defaults) + { + char *this_default = NULL; + AttrDefault *attrdef; + int i; + + /* Find default in constraint structure */ + Assert(constr != NULL); + attrdef = constr->defval; + for (i = 0; i < constr->num_defval; i++) + { + if (attrdef[i].adnum == parent_attno) + { + this_default = attrdef[i].adbin; + break; + } + } + Assert(this_default != NULL); + + /* + * If default expr could contain any vars, we'd need to + * fix 'em, but it can't; so default is ready to apply to + * child. + */ + + def->cooked_default = pstrdup(this_default); + } + } + + /* + * Close the parent rel, but keep our AccessShareLock on it until + * xact commit. That will prevent someone else from deleting or + * ALTERing the parent before the child is committed. + */ + heap_close(relation, NoLock); } static void Index: backend/parser/gram.y =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.414 diff -c -r2.414 gram.y *** backend/parser/gram.y 15 May 2003 16:35:28 -0000 2.414 --- backend/parser/gram.y 21 May 2003 18:23:44 -0000 *************** *** 165,170 **** --- 165,172 ---- %type opt_force opt_or_replace transaction_access_mode opt_grant_grant_option opt_revoke_grant_option + %type like_including_defaults + %type user_list %type OptGroupList *************** *** 336,346 **** CREATEUSER CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE ! DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP ! EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD --- 338,348 ---- CREATEUSER CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE ! DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP ! EACH ELSE ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD *************** *** 350,356 **** HANDLER HAVING HOLD HOUR_P ! ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCREMENT INDEX INHERITS INITIALLY INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION --- 352,358 ---- HANDLER HAVING HOLD HOUR_P ! 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 *************** *** 1642,1659 **** ; ! /* 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; } ; /* ConstraintElem specifies constraint syntax which is not embedded into * a column definition. ColConstraintElem specifies the embedded form. --- 1644,1674 ---- ; ! /* ! * 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 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; } + ; + /* ConstraintElem specifies constraint syntax which is not embedded into * a column definition. ColConstraintElem specifies the embedded form. *************** *** 7215,7220 **** --- 7230,7236 ---- | DAY_P | DEALLOCATE | DECLARE + | DEFAULTS | DEFERRED | DEFINER | DELETE_P *************** *** 7227,7232 **** --- 7243,7249 ---- | ENCODING | ENCRYPTED | ESCAPE + | EXCLUDING | EXCLUSIVE | EXECUTE | EXPLAIN *************** *** 7243,7248 **** --- 7260,7266 ---- | IMMEDIATE | IMMUTABLE | IMPLICIT_P + | INCLUDING | INCREMENT | INDEX | INHERITS Index: backend/parser/keywords.c =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/backend/parser/keywords.c,v retrieving revision 1.139 diff -c -r1.139 keywords.c *** backend/parser/keywords.c 15 May 2003 16:35:28 -0000 1.139 --- backend/parser/keywords.c 21 May 2003 18:24:03 -0000 *************** *** 102,107 **** --- 102,108 ---- {"decimal", DECIMAL_P}, {"declare", DECLARE}, {"default", DEFAULT}, + {"defaults", DEFAULTS}, {"deferrable", DEFERRABLE}, {"deferred", DEFERRED}, {"definer", DEFINER}, *************** *** 121,126 **** --- 122,128 ---- {"end", END_P}, {"escape", ESCAPE}, {"except", EXCEPT}, + {"excluding", EXCLUDING}, {"exclusive", EXCLUSIVE}, {"execute", EXECUTE}, {"exists", EXISTS}, *************** *** 151,156 **** --- 153,159 ---- {"immutable", IMMUTABLE}, {"implicit", IMPLICIT_P}, {"in", IN_P}, + {"including", INCLUDING}, {"increment", INCREMENT}, {"index", INDEX}, {"inherits", INHERITS}, Index: include/nodes/nodes.h =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/include/nodes/nodes.h,v retrieving revision 1.140 diff -c -r1.140 nodes.h *** include/nodes/nodes.h 8 Apr 2003 23:20:04 -0000 1.140 --- include/nodes/nodes.h 12 May 2003 00:25:24 -0000 *************** *** 280,285 **** --- 280,286 ---- T_InsertDefault, T_CreateOpClassItem, T_CompositeTypeStmt, + T_InhRelation, /* * TAGS FOR FUNCTION-CALL CONTEXT AND RESULTINFO NODES (see fmgr.h) Index: include/nodes/parsenodes.h =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.237 diff -c -r1.237 parsenodes.h *** include/nodes/parsenodes.h 2 May 2003 20:54:36 -0000 1.237 --- include/nodes/parsenodes.h 20 May 2003 01:23:27 -0000 *************** *** 351,356 **** --- 351,366 ---- } ColumnDef; /* + * inhRelation - Relations a CREATE TABLE is to inherit attributes of + */ + typedef struct InhRelation + { + NodeTag type; + RangeVar *relation; + 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? */ --- 863,869 ---- 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: test/regress/expected/inherit.out =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/test/regress/expected/inherit.out,v retrieving revision 1.8 diff -c -r1.8 inherit.out *** test/regress/expected/inherit.out 5 Mar 2003 20:01:04 -0000 1.8 --- test/regress/expected/inherit.out 22 May 2003 01:39:56 -0000 *************** *** 570,572 **** --- 570,615 ---- bar2 | 3 | 103 (8 rows) + /* Test inheritance of structure (LIKE) */ + CREATE TABLE inhx (xx text DEFAULT 'text'); + /* + * Test double inheritance + * + * Ensure that defaults are NOT included unless + * INCLUDING DEFAULTS is specified + */ + CREATE TABLE inhe (ee text, LIKE inhx) inherits (b); + INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4'); + SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */ + aa | bb | ee | xx + ---------+---------+----+--------- + ee-col1 | ee-col2 | | 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) + + CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ + ERROR: CREATE TABLE: attribute "xx" duplicated + CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS); + INSERT INTO inhf DEFAULT VALUES; + SELECT * FROM inhf; /* Single entry with value 'text' */ + xx + ------ + text + (1 row) + Index: test/regress/sql/inherit.sql =================================================================== RCS file: /home/rbt/work/postgresql/cvs//pgsql-server/src/test/regress/sql/inherit.sql,v retrieving revision 1.5 diff -c -r1.5 inherit.sql *** test/regress/sql/inherit.sql 5 Mar 2003 20:01:04 -0000 1.5 --- test/regress/sql/inherit.sql 22 May 2003 01:31:20 -0000 *************** *** 119,121 **** --- 119,144 ---- 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 DEFAULT 'text'); + + /* + * Test double inheritance + * + * Ensure that defaults are NOT included unless + * INCLUDING DEFAULTS is specified + */ + CREATE TABLE inhe (ee text, LIKE inhx) inherits (b); + INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4'); + SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */ + SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ + SELECT * FROM b; /* Has ee entry */ + SELECT * FROM a; /* Has ee entry */ + + 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' */