From e7b3349a8ad7afaad565c573fbd65fb46af6abbe Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 28 Jan 2010 23:21:13 +0000 Subject: 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 +++++++++++++++++++++++++++++++++++- 2 files changed, 72 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 4888b647d3c..124bbc3ac29 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 e315843187c..7044e86685d 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 ( + -- cgit v1.2.3