From e7b3349a8ad7afaad565c573fbd65fb46af6abbe Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 28 Jan 2010 23:21:13 +0000 Subject: [PATCH] Type table feature This adds the CREATE TABLE name OF type command, per SQL standard. --- doc/src/sgml/information_schema.sgml | 20 +++- doc/src/sgml/ref/create_table.sgml | 58 +++++++++++- src/backend/bootstrap/bootparse.y | 3 +- src/backend/catalog/heap.c | 19 +++- src/backend/catalog/information_schema.sql | 16 ++-- src/backend/catalog/toasting.c | 3 +- src/backend/commands/cluster.c | 3 +- src/backend/commands/tablecmds.c | 65 +++++++++++-- src/backend/executor/execMain.c | 3 +- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 3 +- src/backend/nodes/outfuncs.c | 3 +- src/backend/parser/gram.y | 56 ++++++++--- src/backend/parser/parse_utilcmd.c | 58 +++++++++++- src/bin/pg_dump/pg_dump.c | 105 ++++++++++++++++----- src/bin/pg_dump/pg_dump.h | 3 +- src/bin/psql/describe.c | 15 ++- src/include/catalog/catversion.h | 4 +- src/include/catalog/heap.h | 3 +- src/include/catalog/pg_class.h | 62 ++++++------ src/include/nodes/parsenodes.h | 4 +- src/test/regress/expected/typed_table.out | 85 +++++++++++++++++ src/test/regress/parallel_schedule | 4 +- src/test/regress/serial_schedule | 3 +- src/test/regress/sql/typed_table.sql | 42 +++++++++ 25 files changed, 535 insertions(+), 108 deletions(-) create mode 100644 src/test/regress/expected/typed_table.out create mode 100644 src/test/regress/sql/typed_table.sql diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 4888b647d3..124bbc3ac2 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1,4 +1,4 @@ - + The Information Schema @@ -4750,19 +4750,29 @@ ORDER BY c.ordinal_position; user_defined_type_catalog sql_identifier - Applies to a feature not available in PostgreSQL + + If the table is a typed table, the name of the database that + contains the underlying data type (always the current + database), else null. + user_defined_type_schema sql_identifier - Applies to a feature not available in PostgreSQL + + If the table is a typed table, the name of the schema that + contains the underlying data type, else null. + user_defined_type_name sql_identifier - Applies to a feature not available in PostgreSQL + + If the table is a typed table, the name of the underlying data + type, else null. + @@ -4778,7 +4788,7 @@ ORDER BY c.ordinal_position; is_typed yes_or_no - Applies to a feature not available in PostgreSQL + YES if the table is a typed table, NO if not diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e315843187..7044e86685 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -32,6 +32,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE tablespace ] +CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name + OF type_name [ ( + { column_name WITH OPTIONS [ DEFAULT default_expr ] [ column_constraint [ ... ] ] + | table_constraint } + [, ... ] +) ] +[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] +[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] +[ TABLESPACE tablespace ] + where column_constraint is: [ CONSTRAINT constraint_name ] @@ -153,6 +163,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE type_name + + + Creates a typed table, which takes its + structure from the specified composite type (name optionally + schema-qualified). A typed table is tied to its type; for + example the table will be dropped if the type is dropped + (with DROP TYPE ... CASCADE). + + + + When a typed table is created, then the data types of the + columns are determined by the underlying composite type and are + not specified by the CREATE TABLE command. + But the CREATE TABLE command can add defaults + and constraints to the table and can specify storage parameters. + + + + column_name @@ -1182,6 +1213,17 @@ CREATE TABLE cinemas ( + + Create a composite type and a typed table: + +CREATE TYPE employee_type AS (name text, salary numeric); + +CREATE TABLE employees OF employee_type ( + PRIMARY KEY (name), + salary WITH OPTIONS DEFAULT 1000 +); + + @@ -1331,6 +1373,19 @@ CREATE TABLE cinemas ( and USING INDEX TABLESPACE are extensions. + + + Typed Tables + + + Typed tables implement a subset of the SQL standard. According to + the standard, a typed table has columns corresponding to the + underlying composite type as well as one other column that is + the self-referencing column. PostgreSQL does not + support these self-referencing columns explicitly, but the same + effect can be had using the OID feature. + + @@ -1341,6 +1396,7 @@ CREATE TABLE cinemas ( + diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index f2155c1b64..a6c1243b95 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.103 2010/01/02 16:57:36 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.104 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -217,6 +217,7 @@ Boot_CreateStmt: $5 ? GLOBALTABLESPACE_OID : 0, $3, $7, + InvalidOid, BOOTSTRAP_SUPERUSERID, tupdesc, NIL, diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 920e00f101..f86747e149 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.367 2010/01/22 16:40:18 rhaas Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.368 2010/01/28 23:21:11 petere Exp $ * * * INTERFACE ROUTINES @@ -72,7 +72,9 @@ static void AddNewRelationTuple(Relation pg_class_desc, Relation new_rel_desc, - Oid new_rel_oid, Oid new_type_oid, + Oid new_rel_oid, + Oid new_type_oid, + Oid reloftype, Oid relowner, char relkind, Datum relacl, @@ -669,6 +671,7 @@ InsertPgClassTuple(Relation pg_class_desc, values[Anum_pg_class_relname - 1] = NameGetDatum(&rd_rel->relname); values[Anum_pg_class_relnamespace - 1] = ObjectIdGetDatum(rd_rel->relnamespace); values[Anum_pg_class_reltype - 1] = ObjectIdGetDatum(rd_rel->reltype); + values[Anum_pg_class_reloftype - 1] = ObjectIdGetDatum(rd_rel->reloftype); values[Anum_pg_class_relowner - 1] = ObjectIdGetDatum(rd_rel->relowner); values[Anum_pg_class_relam - 1] = ObjectIdGetDatum(rd_rel->relam); values[Anum_pg_class_relfilenode - 1] = ObjectIdGetDatum(rd_rel->relfilenode); @@ -727,6 +730,7 @@ AddNewRelationTuple(Relation pg_class_desc, Relation new_rel_desc, Oid new_rel_oid, Oid new_type_oid, + Oid reloftype, Oid relowner, char relkind, Datum relacl, @@ -785,6 +789,7 @@ AddNewRelationTuple(Relation pg_class_desc, new_rel_reltup->relowner = relowner; new_rel_reltup->reltype = new_type_oid; + new_rel_reltup->reloftype = reloftype; new_rel_reltup->relkind = relkind; new_rel_desc->rd_att->tdtypeid = new_type_oid; @@ -876,6 +881,7 @@ heap_create_with_catalog(const char *relname, Oid reltablespace, Oid relid, Oid reltypeid, + Oid reloftypeid, Oid ownerid, TupleDesc tupdesc, List *cooked_constraints, @@ -1097,6 +1103,7 @@ heap_create_with_catalog(const char *relname, new_rel_desc, relid, new_type_oid, + reloftypeid, ownerid, relkind, PointerGetDatum(relacl), @@ -1139,6 +1146,14 @@ heap_create_with_catalog(const char *relname, recordDependencyOnOwner(RelationRelationId, relid, ownerid); + if (reloftypeid) + { + referenced.classId = TypeRelationId; + referenced.objectId = reloftypeid; + referenced.objectSubId = 0; + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } + if (relacl != NULL) { int nnewmembers; diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 64417b3ba8..453953306a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -4,7 +4,7 @@ * * Copyright (c) 2003-2010, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.64 2010/01/17 22:56:21 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.65 2010/01/28 23:21:11 petere Exp $ */ /* @@ -1799,25 +1799,25 @@ CREATE VIEW tables AS CAST(null AS sql_identifier) AS self_referencing_column_name, CAST(null AS character_data) AS reference_generation, - CAST(null AS sql_identifier) AS user_defined_type_catalog, - CAST(null AS sql_identifier) AS user_defined_type_schema, - CAST(null AS sql_identifier) AS user_defined_type_name, + CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog, + CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema, + CAST(t.typname AS sql_identifier) AS user_defined_type_name, CAST(CASE WHEN c.relkind = 'r' OR (c.relkind = 'v' AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, - CAST('NO' AS yes_or_no) AS is_typed, + CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed, CAST( CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME ELSE null END AS character_data) AS commit_action - FROM pg_namespace nc, pg_class c + FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace) + LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid) - WHERE c.relnamespace = nc.oid - AND c.relkind IN ('r', 'v') + WHERE c.relkind IN ('r', 'v') AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index b1faccfbf9..58890aa6a0 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.27 2010/01/06 03:03:58 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.28 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -203,6 +203,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio rel->rd_rel->reltablespace, toastOid, toast_typid, + InvalidOid, rel->rd_rel->relowner, tupdesc, NIL, diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 56d040590b..dc967390ba 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.194 2010/01/20 19:43:40 heikki Exp $ + * $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.195 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -720,6 +720,7 @@ make_new_heap(Oid OIDOldHeap, const char *NewName, Oid NewTableSpace) NewTableSpace, InvalidOid, InvalidOid, + InvalidOid, OldHeap->rd_rel->relowner, tupdesc, NIL, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 89447d3678..134b217d82 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.319 2010/01/28 07:31:42 heikki Exp $ + * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.320 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -361,6 +361,7 @@ DefineRelation(CreateStmt *stmt, char relkind) ListCell *listptr; AttrNumber attnum; static char *validnsps[] = HEAP_RELOPT_NAMESPACES; + Oid ofTypeId; /* * Truncate relname to appropriate length (probably a waste of time, as @@ -443,6 +444,11 @@ DefineRelation(CreateStmt *stmt, char relkind) (void) heap_reloptions(relkind, reloptions, true); + if (stmt->ofTypename) + ofTypeId = typenameTypeId(NULL, stmt->ofTypename, NULL); + else + ofTypeId = InvalidOid; + /* * Look up inheritance ancestors and generate relation schema, including * inherited attributes. @@ -521,6 +527,7 @@ DefineRelation(CreateStmt *stmt, char relkind) tablespaceId, InvalidOid, InvalidOid, + ofTypeId, GetUserId(), descriptor, list_concat(cookedDefaults, @@ -1230,17 +1237,46 @@ MergeAttributes(List *schema, List *supers, bool istemp, foreach(entry, schema) { ColumnDef *coldef = lfirst(entry); - ListCell *rest; + ListCell *rest = lnext(entry); + ListCell *prev = entry; + + if (coldef->typeName == NULL) + /* + * Typed table column option that does not belong to a + * column from the type. This works because the columns + * from the type come first in the list. + */ + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" does not exist", + coldef->colname))); - for_each_cell(rest, lnext(entry)) + while (rest != NULL) { ColumnDef *restdef = lfirst(rest); + ListCell *next = lnext(rest); /* need to save it in case we delete it */ if (strcmp(coldef->colname, restdef->colname) == 0) - ereport(ERROR, - (errcode(ERRCODE_DUPLICATE_COLUMN), - errmsg("column \"%s\" specified more than once", - coldef->colname))); + { + if (coldef->is_from_type) + { + /* merge the column options into the column from + * the type */ + coldef->is_not_null = restdef->is_not_null; + coldef->raw_default = restdef->raw_default; + coldef->cooked_default = restdef->cooked_default; + coldef->constraints = restdef->constraints; + coldef->is_from_type = false; + list_delete_cell(schema, rest, prev); + } + else + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("column \"%s\" specified more than once", + coldef->colname))); + } + prev = rest; + rest = next; } } @@ -1921,6 +1957,11 @@ renameatt(Oid myrelid, */ targetrelation = relation_open(myrelid, AccessExclusiveLock); + if (targetrelation->rd_rel->reloftype) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot rename column of typed table"))); + /* * permissions checking. this would normally be done in utility.c, but * this particular routine is recursive. @@ -3586,6 +3627,11 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel, Form_pg_type tform; Expr *defval; + if (rel->rd_rel->reloftype) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot add column to typed table"))); + attrdesc = heap_open(AttributeRelationId, RowExclusiveLock); /* @@ -4307,6 +4353,11 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName, List *children; ObjectAddress object; + if (rel->rd_rel->reloftype) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot drop column from typed table"))); + /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ATSimplePermissions(rel, false); diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 557ecc7e15..b0698489c4 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -26,7 +26,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.342 2010/01/15 09:19:02 heikki Exp $ + * $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.343 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -2162,6 +2162,7 @@ OpenIntoRel(QueryDesc *queryDesc) tablespaceId, InvalidOid, InvalidOid, + InvalidOid, GetUserId(), tupdesc, NIL, diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 8d1f1641d7..4de0f7c87e 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.459 2010/01/05 21:53:58 rhaas Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.460 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -2507,6 +2507,7 @@ _copyCreateStmt(CreateStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(tableElts); COPY_NODE_FIELD(inhRelations); + COPY_NODE_FIELD(ofTypename); COPY_NODE_FIELD(constraints); COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(oncommit); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 24e5377307..319070add1 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -22,7 +22,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.380 2010/01/05 21:53:58 rhaas Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.381 2010/01/28 23:21:11 petere Exp $ * *------------------------------------------------------------------------- */ @@ -1099,6 +1099,7 @@ _equalCreateStmt(CreateStmt *a, CreateStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(tableElts); COMPARE_NODE_FIELD(inhRelations); + COMPARE_NODE_FIELD(ofTypename); COMPARE_NODE_FIELD(constraints); COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(oncommit); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 5bd092eae2..7095080790 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.380 2010/01/05 21:53:58 rhaas Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.381 2010/01/28 23:21:12 petere Exp $ * * NOTES * Every node type that can appear in stored rules' parsetrees *must* @@ -1784,6 +1784,7 @@ _outCreateStmt(StringInfo str, CreateStmt *node) WRITE_NODE_FIELD(relation); WRITE_NODE_FIELD(tableElts); WRITE_NODE_FIELD(inhRelations); + WRITE_NODE_FIELD(ofTypename); WRITE_NODE_FIELD(constraints); WRITE_NODE_FIELD(options); WRITE_ENUM_FIELD(oncommit, OnCommitAction); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 981d261404..d7bbbbd1be 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.705 2010/01/25 20:55:32 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.706 2010/01/28 23:21:12 petere Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -277,6 +277,7 @@ static TypeName *TableFuncTypeName(List *columns); %type stmtblock stmtmulti OptTableElementList TableElementList OptInherit definition + OptTypedTableElementList TypedTableElementList reloptions opt_reloptions OptWith opt_distinct opt_definition func_args func_args_list func_args_with_defaults func_args_with_defaults_list @@ -347,8 +348,8 @@ static TypeName *TableFuncTypeName(List *columns); %type set_rest SetResetClause -%type TableElement ConstraintElem TableFuncElement -%type columnDef +%type TableElement TypedTableElement ConstraintElem TableFuncElement +%type columnDef columnOptions %type def_elem reloption_elem old_aggr_elem %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr func_expr AexprConst indirection_el @@ -2203,21 +2204,19 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' n->tablespacename = $11; $$ = (Node *)n; } - | CREATE OptTemp TABLE qualified_name OF qualified_name - '(' OptTableElementList ')' OptWith OnCommitOption OptTableSpace + | CREATE OptTemp TABLE qualified_name OF any_name + OptTypedTableElementList OptWith OnCommitOption OptTableSpace { - /* SQL99 CREATE TABLE OF (cols) seems to be satisfied - * by our inheritance capabilities. Let's try it... - */ CreateStmt *n = makeNode(CreateStmt); $4->istemp = $2; n->relation = $4; - n->tableElts = $8; - n->inhRelations = list_make1($6); + n->tableElts = $7; + n->ofTypename = makeTypeNameFromNameList($6); + n->ofTypename->location = @6; n->constraints = NIL; - n->options = $10; - n->oncommit = $11; - n->tablespacename = $12; + n->options = $8; + n->oncommit = $9; + n->tablespacename = $10; $$ = (Node *)n; } ; @@ -2243,6 +2242,11 @@ OptTableElementList: | /*EMPTY*/ { $$ = NIL; } ; +OptTypedTableElementList: + '(' TypedTableElementList ')' { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + TableElementList: TableElement { @@ -2254,12 +2258,28 @@ TableElementList: } ; +TypedTableElementList: + TypedTableElement + { + $$ = list_make1($1); + } + | TypedTableElementList ',' TypedTableElement + { + $$ = lappend($1, $3); + } + ; + TableElement: columnDef { $$ = $1; } | TableLikeClause { $$ = $1; } | TableConstraint { $$ = $1; } ; +TypedTableElement: + columnOptions { $$ = $1; } + | TableConstraint { $$ = $1; } + ; + columnDef: ColId Typename ColQualList { ColumnDef *n = makeNode(ColumnDef); @@ -2271,6 +2291,16 @@ columnDef: ColId Typename ColQualList } ; +columnOptions: ColId WITH OPTIONS ColQualList + { + ColumnDef *n = makeNode(ColumnDef); + n->colname = $1; + n->constraints = $4; + n->is_local = true; + $$ = (Node *)n; + } + ; + ColQualList: ColQualList ColConstraint { $$ = lappend($1, $2); } | /*EMPTY*/ { $$ = NIL; } diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index a1ed7e48e3..bf455701e3 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -19,7 +19,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.36 2010/01/02 16:57:50 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.37 2010/01/28 23:21:12 petere Exp $ * *------------------------------------------------------------------------- */ @@ -58,6 +58,7 @@ #include "utils/lsyscache.h" #include "utils/relcache.h" #include "utils/syscache.h" +#include "utils/typcache.h" /* State shared by transformCreateStmt and its subroutines */ @@ -104,6 +105,8 @@ static void transformTableConstraint(ParseState *pstate, Constraint *constraint); static void transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, InhRelation *inhrelation); +static void transformOfType(ParseState *pstate, CreateStmtContext *cxt, + TypeName *ofTypename); static char *chooseIndexName(const RangeVar *relation, IndexStmt *index_stmt); static IndexStmt *generateClonedIndexStmt(CreateStmtContext *cxt, Relation parent_index, AttrNumber *attmap); @@ -183,6 +186,11 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.pkey = NULL; cxt.hasoids = interpretOidsOption(stmt->options); + Assert(!stmt->ofTypename || !stmt->inhRelations); /* grammar enforces */ + + if (stmt->ofTypename) + transformOfType(pstate, &cxt, stmt->ofTypename); + /* * Run through each primary element in the table creation clause. Separate * column defs from constraints, and do preliminary analysis. @@ -266,8 +274,9 @@ transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt, /* Check for SERIAL pseudo-types */ is_serial = false; - if (list_length(column->typeName->names) == 1 && - !column->typeName->pct_type) + if (column->typeName + && list_length(column->typeName->names) == 1 + && !column->typeName->pct_type) { char *typname = strVal(linitial(column->typeName->names)); @@ -299,7 +308,8 @@ transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt, } /* Do necessary work on the column type declaration */ - transformColumnType(pstate, column); + if (column->typeName) + transformColumnType(pstate, column); /* Special actions for SERIAL pseudo-types */ if (is_serial) @@ -787,6 +797,46 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, heap_close(relation, NoLock); } +static void +transformOfType(ParseState *pstate, CreateStmtContext *cxt, TypeName *ofTypename) +{ + HeapTuple tuple; + Form_pg_type typ; + TupleDesc tupdesc; + int i; + Oid ofTypeId; + + AssertArg(ofTypename); + + tuple = typenameType(NULL, ofTypename, NULL); + typ = (Form_pg_type) GETSTRUCT(tuple); + ofTypeId = HeapTupleGetOid(tuple); + ofTypename->typeOid = ofTypeId; /* cached for later */ + + if (typ->typtype != TYPTYPE_COMPOSITE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("type %s is not a composite type", + format_type_be(ofTypeId)))); + + tupdesc = lookup_rowtype_tupdesc(ofTypeId, -1); + for (i = 0; i < tupdesc->natts; i++) + { + ColumnDef *n = makeNode(ColumnDef); + Form_pg_attribute attr = tupdesc->attrs[i]; + + n->colname = NameStr(attr->attname); + n->typeName = makeTypeNameFromOid(attr->atttypid, attr->atttypmod); + n->constraints = NULL; + n->is_local = true; + n->is_from_type = true; + cxt->columns = lappend(cxt->columns, n); + } + DecrTupleDescRefCount(tupdesc); + + ReleaseSysCache(tuple); +} + /* * chooseIndexName * diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b0764279e8..ae04b6f287 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12,7 +12,7 @@ * by PostgreSQL * * IDENTIFICATION - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.568 2010/01/22 16:40:19 rhaas Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.569 2010/01/28 23:21:12 petere Exp $ * *------------------------------------------------------------------------- */ @@ -3441,6 +3441,7 @@ getTables(int *numTables) int i_reltablespace; int i_reloptions; int i_toastreloptions; + int i_reloftype; /* Make sure we are in proper schema */ selectSourceSchema("pg_catalog"); @@ -3465,7 +3466,7 @@ getTables(int *numTables) * we cannot correctly identify inherited columns, owned sequences, etc. */ - if (g_fout->remoteVersion >= 80400) + if (g_fout->remoteVersion >= 80500) { /* * Left join to pick up dependency info linking sequences to their @@ -3478,6 +3479,40 @@ getTables(int *numTables) "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " "c.relfrozenxid, " + "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " + "d.refobjid AS owning_tab, " + "d.refobjsubid AS owning_col, " + "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " + "array_to_string(c.reloptions, ', ') AS reloptions, " + "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions " + "FROM pg_class c " + "LEFT JOIN pg_depend d ON " + "(c.relkind = '%c' AND " + "d.classid = c.tableoid AND d.objid = c.oid AND " + "d.objsubid = 0 AND " + "d.refclassid = c.tableoid AND d.deptype = 'a') " + "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " + "WHERE c.relkind in ('%c', '%c', '%c', '%c') " + "ORDER BY c.oid", + username_subquery, + RELKIND_SEQUENCE, + RELKIND_RELATION, RELKIND_SEQUENCE, + RELKIND_VIEW, RELKIND_COMPOSITE_TYPE); + } + else if (g_fout->remoteVersion >= 80400) + { + /* + * Left join to pick up dependency info linking sequences to their + * owning column, if any (note this dependency is AUTO as of 8.2) + */ + appendPQExpBuffer(query, + "SELECT c.tableoid, c.oid, c.relname, " + "c.relacl, c.relkind, c.relnamespace, " + "(%s c.relowner) AS rolname, " + "c.relchecks, c.relhastriggers, " + "c.relhasindex, c.relhasrules, c.relhasoids, " + "c.relfrozenxid, " + "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3510,6 +3545,7 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "relfrozenxid, " + "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3541,6 +3577,7 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " @@ -3572,6 +3609,7 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " "NULL AS reltablespace, " @@ -3599,6 +3637,7 @@ getTables(int *numTables) "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, relhasoids, " "0 AS relfrozenxid, " + "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " "NULL AS reltablespace, " @@ -3621,6 +3660,7 @@ getTables(int *numTables) "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " "0 AS relfrozenxid, " + "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " "NULL AS reltablespace, " @@ -3653,6 +3693,7 @@ getTables(int *numTables) "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " "0 as relfrozenxid, " + "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " "NULL AS reltablespace, " @@ -3702,6 +3743,7 @@ getTables(int *numTables) i_reltablespace = PQfnumber(res, "reltablespace"); i_reloptions = PQfnumber(res, "reloptions"); i_toastreloptions = PQfnumber(res, "toast_reloptions"); + i_reloftype = PQfnumber(res, "reloftype"); if (lockWaitTimeout && g_fout->remoteVersion >= 70300) { @@ -3735,6 +3777,10 @@ getTables(int *numTables) tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); + if (PQgetisnull(res, i, i_reloftype)) + tblinfo[i].reloftype = NULL; + else + tblinfo[i].reloftype = strdup(PQgetvalue(res, i, i_reloftype)); tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks)); if (PQgetisnull(res, i, i_owning_tab)) { @@ -10552,8 +10598,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (binary_upgrade) binary_upgrade_set_relfilenodes(q, tbinfo->dobj.catId.oid, false); - appendPQExpBuffer(q, "CREATE TABLE %s (", + appendPQExpBuffer(q, "CREATE TABLE %s", fmtId(tbinfo->dobj.name)); + if (tbinfo->reloftype) + appendPQExpBuffer(q, " OF %s", tbinfo->reloftype); actual_atts = 0; for (j = 0; j < tbinfo->numatts; j++) { @@ -10564,8 +10612,28 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if ((!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j]) || binary_upgrade) { + /* + * Default value --- suppress if inherited (except in + * binary-upgrade case, where we're not doing normal + * inheritance) or if it's to be printed separately. + */ + bool has_default = (tbinfo->attrdefs[j] != NULL + && (!tbinfo->inhAttrDef[j] || binary_upgrade) + && !tbinfo->attrdefs[j]->separate); + /* + * Not Null constraint --- suppress if inherited, except + * in binary-upgrade case. + */ + bool has_notnull = (tbinfo->notnull[j] + && (!tbinfo->inhNotNull[j] || binary_upgrade)); + + if (tbinfo->reloftype && !has_default && !has_notnull) + continue; + /* Format properly if not first attr */ - if (actual_atts > 0) + if (actual_atts == 0) + appendPQExpBuffer(q, " ("); + else appendPQExpBuffer(q, ","); appendPQExpBuffer(q, "\n "); actual_atts++; @@ -10587,7 +10655,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } /* Attribute type */ - if (g_fout->remoteVersion >= 70100) + if (tbinfo->reloftype) + { + appendPQExpBuffer(q, "WITH OPTIONS"); + } + else if (g_fout->remoteVersion >= 70100) { appendPQExpBuffer(q, "%s", tbinfo->atttypnames[j]); @@ -10600,23 +10672,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) tbinfo->atttypmod[j])); } - /* - * Default value --- suppress if inherited (except in - * binary-upgrade case, where we're not doing normal - * inheritance) or if it's to be printed separately. - */ - if (tbinfo->attrdefs[j] != NULL && - (!tbinfo->inhAttrDef[j] || binary_upgrade) && - !tbinfo->attrdefs[j]->separate) + if (has_default) appendPQExpBuffer(q, " DEFAULT %s", tbinfo->attrdefs[j]->adef_expr); - /* - * Not Null constraint --- suppress if inherited, except - * in binary-upgrade case. - */ - if (tbinfo->notnull[j] && - (!tbinfo->inhNotNull[j] || binary_upgrade)) + if (has_notnull) appendPQExpBuffer(q, " NOT NULL"); } } @@ -10631,7 +10691,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) if (constr->separate || !constr->conislocal) continue; - if (actual_atts > 0) + if (actual_atts == 0) + appendPQExpBuffer(q, " (\n "); + else appendPQExpBuffer(q, ",\n "); appendPQExpBuffer(q, "CONSTRAINT %s ", @@ -10641,7 +10703,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) actual_atts++; } - appendPQExpBuffer(q, "\n)"); + if (actual_atts) + appendPQExpBuffer(q, "\n)"); if (numParents > 0 && !binary_upgrade) { diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 0537c43671..fa5972a55f 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.161 2010/01/22 16:40:19 rhaas Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.162 2010/01/28 23:21:12 petere Exp $ * *------------------------------------------------------------------------- */ @@ -229,6 +229,7 @@ typedef struct _tableInfo bool hasoids; /* does it have OIDs? */ uint32 frozenxid; /* for restore frozen xid */ int ncheck; /* # of CHECK expressions */ + char *reloftype; /* underlying type for typed table */ /* these two are set only if table is a sequence owned by a column: */ Oid owning_tab; /* OID of table owning sequence */ int owning_col; /* attr # of column owning sequence */ diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 5b80a885a8..d42cf2394f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -8,7 +8,7 @@ * * Copyright (c) 2000-2010, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.235 2010/01/21 06:11:46 itagaki Exp $ + * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.236 2010/01/28 23:21:12 petere Exp $ */ #include "postgres_fe.h" @@ -1108,6 +1108,7 @@ describeOneTableDetails(const char *schemaname, bool hasexclusion; Oid tablespace; char *reloptions; + char *reloftype; } tableinfo; bool show_modifiers = false; bool retval; @@ -1127,7 +1128,8 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, c.relhasoids, " - "%s, c.reltablespace, c.relhasexclusion\n" + "%s, c.reltablespace, c.relhasexclusion, " + "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::text END\n" "FROM pg_catalog.pg_class c\n " "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" "WHERE c.oid = '%s'\n", @@ -1207,6 +1209,8 @@ describeOneTableDetails(const char *schemaname, atooid(PQgetvalue(res, 0, 7)) : 0; tableinfo.hasexclusion = (pset.sversion >= 80500) ? strcmp(PQgetvalue(res, 0, 8), "t") == 0 : false; + tableinfo.reloftype = (pset.sversion >= 80500 && strcmp(PQgetvalue(res, 0, 9), "") != 0) ? + strdup(PQgetvalue(res, 0, 9)) : 0; PQclear(res); res = NULL; @@ -2031,6 +2035,13 @@ describeOneTableDetails(const char *schemaname, } PQclear(result); + /* Table type */ + if (tableinfo.reloftype) + { + printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype); + printTableAddFooter(&cont, buf.data); + } + /* OIDs and options */ if (verbose) { diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 4f6ccbcf0c..192f870b81 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.580 2010/01/28 14:25:41 mha Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.581 2010/01/28 23:21:12 petere Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201001281 +#define CATALOG_VERSION_NO 201001282 #endif diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index faa4374ca5..9c16737ada 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/heap.h,v 1.95 2010/01/02 16:58:01 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/heap.h,v 1.96 2010/01/28 23:21:12 petere Exp $ * *------------------------------------------------------------------------- */ @@ -48,6 +48,7 @@ extern Oid heap_create_with_catalog(const char *relname, Oid reltablespace, Oid relid, Oid reltypeid, + Oid reloftypeid, Oid ownerid, TupleDesc tupdesc, List *cooked_constraints, diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index ffa7a4766d..aa35109edc 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_class.h,v 1.119 2010/01/05 01:06:56 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_class.h,v 1.120 2010/01/28 23:21:12 petere Exp $ * * NOTES * the genbki.pl script reads this file and generates .bki @@ -33,7 +33,8 @@ CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO { NameData relname; /* class name */ Oid relnamespace; /* OID of namespace containing this class */ - Oid reltype; /* OID of associated entry in pg_type */ + Oid reltype; /* OID of entry in pg_type for table's implicit row type */ + Oid reloftype; /* OID of entry in pg_type for underlying composite type */ Oid relowner; /* class owner */ Oid relam; /* index access method; 0 if not an index */ Oid relfilenode; /* identifier of physical storage file */ @@ -88,33 +89,34 @@ typedef FormData_pg_class *Form_pg_class; * ---------------- */ -#define Natts_pg_class 26 +#define Natts_pg_class 27 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 -#define Anum_pg_class_relowner 4 -#define Anum_pg_class_relam 5 -#define Anum_pg_class_relfilenode 6 -#define Anum_pg_class_reltablespace 7 -#define Anum_pg_class_relpages 8 -#define Anum_pg_class_reltuples 9 -#define Anum_pg_class_reltoastrelid 10 -#define Anum_pg_class_reltoastidxid 11 -#define Anum_pg_class_relhasindex 12 -#define Anum_pg_class_relisshared 13 -#define Anum_pg_class_relistemp 14 -#define Anum_pg_class_relkind 15 -#define Anum_pg_class_relnatts 16 -#define Anum_pg_class_relchecks 17 -#define Anum_pg_class_relhasoids 18 -#define Anum_pg_class_relhaspkey 19 -#define Anum_pg_class_relhasexclusion 20 -#define Anum_pg_class_relhasrules 21 -#define Anum_pg_class_relhastriggers 22 -#define Anum_pg_class_relhassubclass 23 -#define Anum_pg_class_relfrozenxid 24 -#define Anum_pg_class_relacl 25 -#define Anum_pg_class_reloptions 26 +#define Anum_pg_class_reloftype 4 +#define Anum_pg_class_relowner 5 +#define Anum_pg_class_relam 6 +#define Anum_pg_class_relfilenode 7 +#define Anum_pg_class_reltablespace 8 +#define Anum_pg_class_relpages 9 +#define Anum_pg_class_reltuples 10 +#define Anum_pg_class_reltoastrelid 11 +#define Anum_pg_class_reltoastidxid 12 +#define Anum_pg_class_relhasindex 13 +#define Anum_pg_class_relisshared 14 +#define Anum_pg_class_relistemp 15 +#define Anum_pg_class_relkind 16 +#define Anum_pg_class_relnatts 17 +#define Anum_pg_class_relchecks 18 +#define Anum_pg_class_relhasoids 19 +#define Anum_pg_class_relhaspkey 20 +#define Anum_pg_class_relhasexclusion 21 +#define Anum_pg_class_relhasrules 22 +#define Anum_pg_class_relhastriggers 23 +#define Anum_pg_class_relhassubclass 24 +#define Anum_pg_class_relfrozenxid 25 +#define Anum_pg_class_relacl 26 +#define Anum_pg_class_reloptions 27 /* ---------------- * initial contents of pg_class @@ -126,13 +128,13 @@ typedef FormData_pg_class *Form_pg_class; */ /* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */ -DATA(insert OID = 1247 ( pg_type PGNSP 71 PGUID 0 1247 0 0 0 0 0 f f f r 28 0 t f f f f f 3 _null_ _null_ )); +DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 1247 0 0 0 0 0 f f f r 28 0 t f f f f f 3 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1249 ( pg_attribute PGNSP 75 PGUID 0 1249 0 0 0 0 0 f f f r 19 0 f f f f f f 3 _null_ _null_ )); +DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 1249 0 0 0 0 0 f f f r 19 0 f f f f f f 3 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1255 ( pg_proc PGNSP 81 PGUID 0 1255 0 0 0 0 0 f f f r 25 0 t f f f f f 3 _null_ _null_ )); +DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 1255 0 0 0 0 0 f f f r 25 0 t f f f f f 3 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1259 ( pg_class PGNSP 83 PGUID 0 1259 0 0 0 0 0 f f f r 26 0 t f f f f f 3 _null_ _null_ )); +DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 1259 0 0 0 0 0 f f f r 27 0 t f f f f f 3 _null_ _null_ )); DESCR(""); #define RELKIND_INDEX 'i' /* secondary index */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 1ac74e0ee4..e0ae804c41 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.426 2010/01/22 16:40:19 rhaas Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.427 2010/01/28 23:21:13 petere Exp $ * *------------------------------------------------------------------------- */ @@ -463,6 +463,7 @@ typedef struct ColumnDef int inhcount; /* number of times column is inherited */ bool is_local; /* column has local (non-inherited) def'n */ bool is_not_null; /* NOT NULL constraint specified? */ + bool is_from_type; /* column definition came from table type */ char storage; /* attstorage setting, or 0 for default */ Node *raw_default; /* default value (untransformed parse tree) */ Node *cooked_default; /* default value (transformed expr tree) */ @@ -1356,6 +1357,7 @@ typedef struct CreateStmt List *tableElts; /* column definitions (list of ColumnDef) */ List *inhRelations; /* relations to inherit from (list of * inhRelation) */ + TypeName *ofTypename; /* OF typename */ List *constraints; /* constraints (list of Constraint nodes) */ List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out new file mode 100644 index 0000000000..e92cdf65e1 --- /dev/null +++ b/src/test/regress/expected/typed_table.out @@ -0,0 +1,85 @@ +CREATE TABLE ttable1 OF nothing; +ERROR: type "nothing" does not exist +CREATE TYPE person_type AS (id int, name text); +CREATE TABLE persons OF person_type; +SELECT * FROM persons; + id | name +----+------ +(0 rows) + +\d persons + Table "public.persons" + Column | Type | Modifiers +--------+---------+----------- + id | integer | + name | text | +Typed table of type: person_type + +CREATE FUNCTION get_all_persons() RETURNS SETOF person_type +LANGUAGE SQL +AS $$ + SELECT * FROM persons; +$$; +SELECT * FROM get_all_persons(); + id | name +----+------ +(0 rows) + +ALTER TABLE persons ADD COLUMN comment text; +ERROR: cannot add column to typed table +ALTER TABLE persons DROP COLUMN name; +ERROR: cannot drop column from typed table +ALTER TABLE persons RENAME COLUMN id TO num; +ERROR: cannot rename column of typed table +CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error +ERROR: column "myname" does not exist +CREATE TABLE persons2 OF person_type ( + id WITH OPTIONS PRIMARY KEY, + UNIQUE (name) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons2_pkey" for table "persons2" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "persons2_name_key" for table "persons2" +\d persons2 + Table "public.persons2" + Column | Type | Modifiers +--------+---------+----------- + id | integer | not null + name | text | +Indexes: + "persons2_pkey" PRIMARY KEY, btree (id) + "persons2_name_key" UNIQUE, btree (name) +Typed table of type: person_type + +CREATE TABLE persons3 OF person_type ( + PRIMARY KEY (id), + name WITH OPTIONS DEFAULT '' +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "persons3_pkey" for table "persons3" +\d persons3 + Table "public.persons3" + Column | Type | Modifiers +--------+---------+------------------ + id | integer | not null + name | text | default ''::text +Indexes: + "persons3_pkey" PRIMARY KEY, btree (id) +Typed table of type: person_type + +CREATE TABLE persons4 OF person_type ( + name WITH OPTIONS NOT NULL, + name WITH OPTIONS DEFAULT '' -- error, specified more than once +); +ERROR: column "name" specified more than once +DROP TYPE person_type RESTRICT; +ERROR: cannot drop type person_type because other objects depend on it +DETAIL: table persons depends on type person_type +function get_all_persons() depends on type person_type +table persons2 depends on type person_type +table persons3 depends on type person_type +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP TYPE person_type CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to table persons +drop cascades to function get_all_persons() +drop cascades to table persons2 +drop cascades to table persons3 diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7d5762f916..fa5f507e45 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -1,5 +1,5 @@ # ---------- -# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.57 2009/08/24 03:10:16 tgl Exp $ +# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.58 2010/01/28 23:21:13 petere Exp $ # # By convention, we put no more than twenty tests in any one parallel group; # this limits the number of connections needed to run the tests. @@ -52,7 +52,7 @@ test: copy copyselect # ---------- # Another group of parallel tests # ---------- -test: constraints triggers create_misc create_aggregate create_operator inherit vacuum drop_if_exists create_cast +test: constraints triggers create_misc create_aggregate create_operator inherit typed_table vacuum drop_if_exists create_cast # Depends on the above test: create_index create_view diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 4f61a2d575..037abf2341 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -1,4 +1,4 @@ -# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.54 2009/08/24 03:10:16 tgl Exp $ +# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.55 2010/01/28 23:21:13 petere Exp $ # This should probably be in an order similar to parallel_schedule. test: tablespace test: boolean @@ -60,6 +60,7 @@ test: create_operator test: create_index test: drop_if_exists test: inherit +test: typed_table test: vacuum test: create_view test: sanity_check diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/typed_table.sql new file mode 100644 index 0000000000..4e81f1dd6a --- /dev/null +++ b/src/test/regress/sql/typed_table.sql @@ -0,0 +1,42 @@ +CREATE TABLE ttable1 OF nothing; + +CREATE TYPE person_type AS (id int, name text); +CREATE TABLE persons OF person_type; +SELECT * FROM persons; +\d persons + +CREATE FUNCTION get_all_persons() RETURNS SETOF person_type +LANGUAGE SQL +AS $$ + SELECT * FROM persons; +$$; + +SELECT * FROM get_all_persons(); + +ALTER TABLE persons ADD COLUMN comment text; +ALTER TABLE persons DROP COLUMN name; +ALTER TABLE persons RENAME COLUMN id TO num; + +CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error + +CREATE TABLE persons2 OF person_type ( + id WITH OPTIONS PRIMARY KEY, + UNIQUE (name) +); + +\d persons2 + +CREATE TABLE persons3 OF person_type ( + PRIMARY KEY (id), + name WITH OPTIONS DEFAULT '' +); + +\d persons3 + +CREATE TABLE persons4 OF person_type ( + name WITH OPTIONS NOT NULL, + name WITH OPTIONS DEFAULT '' -- error, specified more than once +); + +DROP TYPE person_type RESTRICT; +DROP TYPE person_type CASCADE; -- 2.40.0