Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite7b3349

Browse files
committed
Type table feature
This adds the CREATE TABLE name OF type command, per SQL standard.
1 parent1f98ccc commite7b3349

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
staticvoidAddNewRelationTuple(Relationpg_class_desc,
7474
Relationnew_rel_desc,
75-
Oidnew_rel_oid,Oidnew_type_oid,
75+
Oidnew_rel_oid,
76+
Oidnew_type_oid,
77+
Oidreloftype,
7678
Oidrelowner,
7779
charrelkind,
7880
Datumrelacl,
@@ -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
Relationnew_rel_desc,
728731
Oidnew_rel_oid,
729732
Oidnew_type_oid,
733+
Oidreloftype,
730734
Oidrelowner,
731735
charrelkind,
732736
Datumrelacl,
@@ -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
Oidreltablespace,
877882
Oidrelid,
878883
Oidreltypeid,
884+
Oidreloftypeid,
879885
Oidownerid,
880886
TupleDesctupdesc,
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
intnnewmembers;

‎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(nullAS sql_identifier)AS self_referencing_column_name,
18001800
CAST(nullAS character_data)AS reference_generation,
18011801

1802-
CAST(nullAS sql_identifier)AS user_defined_type_catalog,
1803-
CAST(nullAS sql_identifier)AS user_defined_type_schema,
1804-
CAST(nullAS sql_identifier)AS user_defined_type_name,
1802+
CAST(CASE WHENt.typnameIS NOT NULL THEN current_database() ELSEnull ENDAS sql_identifier)AS user_defined_type_catalog,
1803+
CAST(nt.nspnameAS sql_identifier)AS user_defined_type_schema,
1804+
CAST(t.typnameAS sql_identifier)AS user_defined_type_name,
18051805

18061806
CAST(CASE WHENc.relkind='r'
18071807
OR (c.relkind='v'
18081808
AND EXISTS (SELECT1FROM pg_rewriteWHERE ev_class=c.oidAND ev_type='3'AND is_instead))
18091809
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_insertable_into,
18101810

1811-
CAST('NO'AS yes_or_no)AS is_typed,
1811+
CAST(CASE WHENt.typnameIS NOT NULL THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_typed,
18121812
CAST(
18131813
CASE WHENnc.oid= pg_my_temp_schema() THEN'PRESERVE'-- FIXME
18141814
ELSEnull END
18151815
AS character_data)AS commit_action
18161816

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

1819-
WHEREc.relnamespace=nc.oid
1820-
ANDc.relkindIN ('r','v')
1820+
WHEREc.relkindIN ('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)
[8]ページ先頭
©2009-2025 Movatter.jp