Skip to content

Commit e7b3349

Browse files
peterepetere
committed
Type table feature
This adds the CREATE TABLE name OF type command, per SQL standard.
1 parent 1f98ccc commit e7b3349

File tree

25 files changed

+535
-108
lines changed

25 files changed

+535
-108
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.44 2009/12/31 14:41:23 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.45 2010/01/28 23:21:10 petere Exp $ -->
22

33
<chapter id="information-schema">
44
<title>The Information Schema</title>
@@ -4750,19 +4750,29 @@ ORDER BY c.ordinal_position;
47504750
<row>
47514751
<entry><literal>user_defined_type_catalog</literal></entry>
47524752
<entry><type>sql_identifier</type></entry>
4753-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4753+
<entry>
4754+
If the table is a typed table, the name of the database that
4755+
contains the underlying data type (always the current
4756+
database), else null.
4757+
</entry>
47544758
</row>
47554759

47564760
<row>
47574761
<entry><literal>user_defined_type_schema</literal></entry>
47584762
<entry><type>sql_identifier</type></entry>
4759-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4763+
<entry>
4764+
If the table is a typed table, the name of the schema that
4765+
contains the underlying data type, else null.
4766+
</entry>
47604767
</row>
47614768

47624769
<row>
47634770
<entry><literal>user_defined_type_name</literal></entry>
47644771
<entry><type>sql_identifier</type></entry>
4765-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4772+
<entry>
4773+
If the table is a typed table, the name of the underlying data
4774+
type, else null.
4775+
</entry>
47664776
</row>
47674777

47684778
<row>
@@ -4778,7 +4788,7 @@ ORDER BY c.ordinal_position;
47784788
<row>
47794789
<entry><literal>is_typed</literal></entry>
47804790
<entry><type>yes_or_no</type></entry>
4781-
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
4791+
<entry><literal>YES</literal> if the table is a typed table, <literal>NO</literal> if not</entry>
47824792
</row>
47834793

47844794
<row>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 57 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.120 2009/12/07 05:22:21 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.121 2010/01/28 23:21:11 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -32,6 +32,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
3232
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
3333
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
3434

35+
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable>
36+
OF <replaceable class="PARAMETER">type_name</replaceable> [ (
37+
{ <replaceable class="PARAMETER">column_name</replaceable> WITH OPTIONS [ DEFAULT <replaceable>default_expr</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
38+
| <replaceable>table_constraint</replaceable> }
39+
[, ... ]
40+
) ]
41+
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
42+
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
43+
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
44+
3545
<phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
3646

3747
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -153,6 +163,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
153163
</listitem>
154164
</varlistentry>
155165

166+
<varlistentry>
167+
<term><literal>OF <replaceable class="PARAMETER">type_name</replaceable></literal></term>
168+
<listitem>
169+
<para>
170+
Creates a <firstterm>typed table</firstterm>, which takes its
171+
structure from the specified composite type (name optionally
172+
schema-qualified). A typed table is tied to its type; for
173+
example the table will be dropped if the type is dropped
174+
(with <literal>DROP TYPE ... CASCADE</literal>).
175+
</para>
176+
177+
<para>
178+
When a typed table is created, then the data types of the
179+
columns are determined by the underlying composite type and are
180+
not specified by the <literal>CREATE TABLE</literal> command.
181+
But the <literal>CREATE TABLE</literal> command can add defaults
182+
and constraints to the table and can specify storage parameters.
183+
</para>
184+
</listitem>
185+
</varlistentry>
186+
156187
<varlistentry>
157188
<term><replaceable class="PARAMETER">column_name</replaceable></term>
158189
<listitem>
@@ -1182,6 +1213,17 @@ CREATE TABLE cinemas (
11821213
</programlisting>
11831214
</para>
11841215

1216+
<para>
1217+
Create a composite type and a typed table:
1218+
<programlisting>
1219+
CREATE TYPE employee_type AS (name text, salary numeric);
1220+
1221+
CREATE TABLE employees OF employee_type (
1222+
PRIMARY KEY (name),
1223+
salary WITH OPTIONS DEFAULT 1000
1224+
);
1225+
</programlisting>
1226+
</para>
11851227
</refsect1>
11861228

11871229
<refsect1 id="SQL-CREATETABLE-compatibility">
@@ -1331,6 +1373,19 @@ CREATE TABLE cinemas (
13311373
and <literal>USING INDEX TABLESPACE</literal> are extensions.
13321374
</para>
13331375
</refsect2>
1376+
1377+
<refsect2>
1378+
<title>Typed Tables</title>
1379+
1380+
<para>
1381+
Typed tables implement a subset of the SQL standard. According to
1382+
the standard, a typed table has columns corresponding to the
1383+
underlying composite type as well as one other column that is
1384+
the <quote>self-referencing column</quote>. PostgreSQL does not
1385+
support these self-referencing columns explicitly, but the same
1386+
effect can be had using the OID feature.
1387+
</para>
1388+
</refsect2>
13341389
</refsect1>
13351390

13361391

@@ -1341,6 +1396,7 @@ CREATE TABLE cinemas (
13411396
<member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
13421397
<member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
13431398
<member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
1399+
<member><xref linkend="sql-createtype" endterm="sql-createtype-title"></member>
13441400
</simplelist>
13451401
</refsect1>
13461402
</refentry>

‎src/backend/bootstrap/bootparse.y

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.103 2010/01/02 16:57:36 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.104 2010/01/28 23:21:11 petere Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -217,6 +217,7 @@ Boot_CreateStmt:
217217
$5 ? GLOBALTABLESPACE_OID : 0,
218218
$3,
219219
$7,
220+
InvalidOid,
220221
BOOTSTRAP_SUPERUSERID,
221222
tupdesc,
222223
NIL,

‎src/backend/catalog/heap.c

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.367 2010/01/22 16:40:18 rhaas Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.368 2010/01/28 23:21:11 petere Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -72,7 +72,9 @@
7272

7373
static void AddNewRelationTuple(Relation pg_class_desc,
7474
Relation new_rel_desc,
75-
Oid new_rel_oid, Oid new_type_oid,
75+
Oid new_rel_oid,
76+
Oid new_type_oid,
77+
Oid reloftype,
7678
Oid relowner,
7779
char relkind,
7880
Datum relacl,
@@ -669,6 +671,7 @@ InsertPgClassTuple(Relation pg_class_desc,
669671
values[Anum_pg_class_relname - 1] = NameGetDatum(&rd_rel->relname);
670672
values[Anum_pg_class_relnamespace - 1] = ObjectIdGetDatum(rd_rel->relnamespace);
671673
values[Anum_pg_class_reltype - 1] = ObjectIdGetDatum(rd_rel->reltype);
674+
values[Anum_pg_class_reloftype - 1] = ObjectIdGetDatum(rd_rel->reloftype);
672675
values[Anum_pg_class_relowner - 1] = ObjectIdGetDatum(rd_rel->relowner);
673676
values[Anum_pg_class_relam - 1] = ObjectIdGetDatum(rd_rel->relam);
674677
values[Anum_pg_class_relfilenode - 1] = ObjectIdGetDatum(rd_rel->relfilenode);
@@ -727,6 +730,7 @@ AddNewRelationTuple(Relation pg_class_desc,
727730
Relation new_rel_desc,
728731
Oid new_rel_oid,
729732
Oid new_type_oid,
733+
Oid reloftype,
730734
Oid relowner,
731735
char relkind,
732736
Datum relacl,
@@ -785,6 +789,7 @@ AddNewRelationTuple(Relation pg_class_desc,
785789

786790
new_rel_reltup->relowner = relowner;
787791
new_rel_reltup->reltype = new_type_oid;
792+
new_rel_reltup->reloftype = reloftype;
788793
new_rel_reltup->relkind = relkind;
789794

790795
new_rel_desc->rd_att->tdtypeid = new_type_oid;
@@ -876,6 +881,7 @@ heap_create_with_catalog(const char *relname,
876881
Oid reltablespace,
877882
Oid relid,
878883
Oid reltypeid,
884+
Oid reloftypeid,
879885
Oid ownerid,
880886
TupleDesc tupdesc,
881887
List *cooked_constraints,
@@ -1097,6 +1103,7 @@ heap_create_with_catalog(const char *relname,
10971103
new_rel_desc,
10981104
relid,
10991105
new_type_oid,
1106+
reloftypeid,
11001107
ownerid,
11011108
relkind,
11021109
PointerGetDatum(relacl),
@@ -1139,6 +1146,14 @@ heap_create_with_catalog(const char *relname,
11391146

11401147
recordDependencyOnOwner(RelationRelationId, relid, ownerid);
11411148

1149+
if (reloftypeid)
1150+
{
1151+
referenced.classId = TypeRelationId;
1152+
referenced.objectId = reloftypeid;
1153+
referenced.objectSubId = 0;
1154+
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
1155+
}
1156+
11421157
if (relacl != NULL)
11431158
{
11441159
int nnewmembers;

‎src/backend/catalog/information_schema.sql

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
*
55
* Copyright (c) 2003-2010, PostgreSQL Global Development Group
66
*
7-
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.64 2010/01/17 22:56:21 tgl Exp $
7+
* $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.65 2010/01/28 23:21:11 petere Exp $
88
*/
99

1010
/*
@@ -1799,25 +1799,25 @@ CREATE VIEW tables AS
17991799
CAST(null AS sql_identifier) AS self_referencing_column_name,
18001800
CAST(null AS character_data) AS reference_generation,
18011801

1802-
CAST(null AS sql_identifier) AS user_defined_type_catalog,
1803-
CAST(null AS sql_identifier) AS user_defined_type_schema,
1804-
CAST(null AS sql_identifier) AS user_defined_type_name,
1802+
CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1803+
CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1804+
CAST(t.typname AS sql_identifier) AS user_defined_type_name,
18051805

18061806
CAST(CASE WHEN c.relkind = 'r'
18071807
OR (c.relkind = 'v'
18081808
AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
18091809
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
18101810

1811-
CAST('NO' AS yes_or_no) AS is_typed,
1811+
CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
18121812
CAST(
18131813
CASE WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE' -- FIXME
18141814
ELSE null END
18151815
AS character_data) AS commit_action
18161816

1817-
FROM pg_namespace nc, pg_class c
1817+
FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1818+
LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
18181819

1819-
WHERE c.relnamespace = nc.oid
1820-
AND c.relkind IN ('r', 'v')
1820+
WHERE c.relkind IN ('r', 'v')
18211821
AND (NOT pg_is_other_temp_schema(nc.oid))
18221822
AND (pg_has_role(c.relowner, 'USAGE')
18231823
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')

‎src/backend/catalog/toasting.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.27 2010/01/06 03:03:58 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.28 2010/01/28 23:21:11 petere Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -203,6 +203,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio
203203
rel->rd_rel->reltablespace,
204204
toastOid,
205205
toast_typid,
206+
InvalidOid,
206207
rel->rd_rel->relowner,
207208
tupdesc,
208209
NIL,

‎src/backend/commands/cluster.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.194 2010/01/20 19:43:40 heikki Exp $
14+
* $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.195 2010/01/28 23:21:11 petere Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -720,6 +720,7 @@ make_new_heap(Oid OIDOldHeap, const char *NewName, Oid NewTableSpace)
720720
NewTableSpace,
721721
InvalidOid,
722722
InvalidOid,
723+
InvalidOid,
723724
OldHeap->rd_rel->relowner,
724725
tupdesc,
725726
NIL,

0 commit comments

Comments
 (0)