Typed tables
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).
CREATE TYPE persons_type AS (name text, bdate date);
CREATE TABLE persons OF persons_type;
Or the fancy version:
CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
This is useful in conjunction with PL/Proxy and similar RPC-type setups.
On the frontend/proxy instances you only create the type, and the
backend instances you create the storage for the type, and the database
system would give you a little support keeping them in sync. Think
interface and implementation.
We have all the necessary bits available in the PostgreSQL system
already; they just need to be wired together a little differently for
this feature. The CREATE TABLE / OF command would use some parts of the
LIKE and/or INHERITS logic to make a copy of the composite type's
structure, and then we'd probably need a new column in pg_class to store
the relationship of the table to its type.
One thing I'm not sure of is whether to keep the implicit row type in
that case. That is, would the above command sequence still create a
"persons" type? We could keep that so as to preserve the property "a
table always has a row type of the same name", or we could skip it in
that case, so if you create a typed table in this sense, you need to use
the type that you created yourself beforehand.
Thoughts?
Peter Eisentraut <peter_e@gmx.net> writes:
One thing I'm not sure of is whether to keep the implicit row type in
that case. That is, would the above command sequence still create a
"persons" type?
Are you intending that the table and the original composite type are
independent, or are still tied together --- ie, does ALTER TABLE ADD
COLUMN or similar affect the composite type?
If not, you *must* have a rowtype that is associated with the table.
regards, tom lane
On Nov 5, 2009, at 10:24 AM, Peter Eisentraut wrote:
One thing I'm not sure of is whether to keep the implicit row type in
that case. That is, would the above command sequence still create a
"persons" type? We could keep that so as to preserve the property "a
table always has a row type of the same name"
+1 for keeping it.
Thoughts?
Any plans to allow the specification of multiple types to define the
table?
"CREATE TABLE employee OF employee_data_type, persons_data_type;"
On tor, 2009-11-05 at 12:38 -0500, Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
One thing I'm not sure of is whether to keep the implicit row type in
that case. That is, would the above command sequence still create a
"persons" type?Are you intending that the table and the original composite type are
independent, or are still tied together --- ie, does ALTER TABLE ADD
COLUMN or similar affect the composite type?
They need to stay tied together. But it's to be determined whether
ALTER TABLE ADD COLUMN would work on those tables or whether there would
be some kind of ALTER TYPE.
On tor, 2009-11-05 at 11:41 -0700, James Pye wrote:
Any plans to allow the specification of multiple types to define the
table?"CREATE TABLE employee OF employee_data_type, persons_data_type;"
Not really, but it does open up interesting possibilities, if we just
allow composite types to participate in inheritance relationships.
Think abstract base class. That's pretty much the idea. Come to think
of it, that's how the SQL standard defined inheritance. Sounds
interesting. And might actually be simpler to implement.
On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).CREATE TYPE persons_type AS (name text, bdate date);
CREATE TABLE persons OF persons_type;
Or the fancy version:
CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
I use composite types (via tables) all the time but I never use
'create type as'...because by doing so you lose the ability to alter
the type with 'alter table'.
Am I correct that I could use your idea to make this possible (albeit
quite ugly) by:
create type foo(a text, b text);
create table foo of foo;
alter table foo add column c text;
drop table foo; -- does this drop the type as well??
merlin
Merlin Moncure wrote:
On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).CREATE TYPE persons_type AS (name text, bdate date);
CREATE TABLE persons OF persons_type;
Or the fancy version:
CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
I use composite types (via tables) all the time but I never use
'create type as'...because by doing so you lose the ability to alter
the type with 'alter table'.Am I correct that I could use your idea to make this possible (albeit
quite ugly) by:create type foo(a text, b text);
create table foo of foo;
alter table foo add column c text;
drop table foo; -- does this drop the type as well??
That seems weird. Seems we should forbid that, and have an ALTER TYPE
command instead. I guess that means that we have to somehow memorize
that the type and the table are distinct. Also, if you create a type and
a table from it, pg_dump still needs to dump the CREATE TYPE command,
not just CREATE TABLE.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Peter Eisentraut <peter_e@gmx.net> wrote:
On tor, 2009-11-05 at 11:41 -0700, James Pye wrote:
"CREATE TABLE employee OF employee_data_type, persons_data_type;"
Not really, but it does open up interesting possibilities, if we just
allow composite types to participate in inheritance relationships.
Think abstract base class. That's pretty much the idea. Come to think
of it, that's how the SQL standard defined inheritance. Sounds
interesting. And might actually be simpler to implement.
Do you want to tightly bind the table with the underlying type?
In other words, do you think "copying column definitions" is not enough?
Like:
CREATE TABLE employee (LIKE employee_data_type, LIKE persons_data_type);
or
CREATE TABLE employee () INHERITS (employee_data_type, persons_data_type);
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
On Thu, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
This is useful in conjunction with PL/Proxy and similar RPC-type
setups. On the frontend/proxy instances you only create the type, and
the backend instances you create the storage for the type, and the
database system would give you a little support keeping them in sync.
Think interface and implementation.
Not sure I see why this is good. Why is issuing CREATE TYPE so much
easier than using CREATE TABLE? Is it worth the extra syntax and code to
support it? Can we do anything additional as a result of this? Is this
required by the standard or are we going past the standard?
--
Simon Riggs www.2ndQuadrant.com
This is useful in conjunction with PL/Proxy and similar RPC-type
setups. On the frontend/proxy instances you only create the type, and
the backend instances you create the storage for the type, and the
database system would give you a little support keeping them in sync.
Think interface and implementation.Not sure I see why this is good. Why is issuing CREATE TYPE so much
easier than using CREATE TABLE? Is it worth the extra syntax and code to
support it? Can we do anything additional as a result of this? Is this
required by the standard or are we going past the standard?
+1. I'd like to hear from Peter why this is neccessary in the first
place.
--
Tatsuo Ishii
SRA OSS, Inc. JapanOn Sun, 2009-11-08 at 21:17 +0000, Simon Riggs wrote:
Not sure I see why this is good. Why is issuing CREATE TYPE so much
easier than using CREATE TABLE? Is it worth the extra syntax and code to
support it? Can we do anything additional as a result of this?
These are tools to improve database design in particular situations.
Nobody really *needs* this, but then again, you don't really need CREATE
TYPE for composite types in the first place. Using CREATE TABLE instead
of CREATE TYPE creates a bunch of extra things you don't need. For
example, files are created, VACUUM and ANALYZE have to keep checking the
table, backup tools think they have to back up the table, and you have
to check that no one actually inserts anything into the table.
Is this required by the standard or are we going past the standard?
This is part of the SQL standard.
On Mon, 2009-11-09 at 12:15 +0200, Peter Eisentraut wrote:
Is this required by the standard or are we going past the standard?
This is part of the SQL standard.
+1
--
Simon Riggs www.2ndQuadrant.com
On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).CREATE TYPE persons_type AS (name text, bdate date);
CREATE TABLE persons OF persons_type;
Or the fancy version:
CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
And here is the first patch for that. The feature is complete as far as
I had wanted it. I would like to add ALTER TYPE support, but that can
come as a separate patch.
Attachments:
typed-tables.patchtext/x-patch; charset=UTF-8; name=typed-tables.patchDownload
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 97e95ed..37c6543 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4750,19 +4750,29 @@ ORDER BY c.ordinal_position;
<row>
<entry><literal>user_defined_type_catalog</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the database that
+ contains the underlying data type (always the current
+ database), else null.
+ </entry>
</row>
<row>
<entry><literal>user_defined_type_schema</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the schema that
+ contains the underlying data type, else null.
+ </entry>
</row>
<row>
<entry><literal>user_defined_type_name</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the underlying data
+ type, else null.
+ </entry>
</row>
<row>
@@ -4778,7 +4788,7 @@ ORDER BY c.ordinal_position;
<row>
<entry><literal>is_typed</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry><literal>YES</literal> if the table is a typed table, <literal>NO</literal> if not</entry>
</row>
<row>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 43764f1..28eb52e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -32,6 +32,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable>
+ OF <replaceable class="PARAMETER">type_name</replaceable> [ (
+ { <replaceable class="PARAMETER">column_name</replaceable> WITH OPTIONS [ DEFAULT <replaceable>default_expr</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
+ | <replaceable>table_constraint</replaceable> }
+ [, ... ]
+) ]
+[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
+[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
+
<phrase>where <replaceable class="PARAMETER">column_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
@@ -154,6 +164,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
</varlistentry>
<varlistentry>
+ <term><literal>OF <replaceable class="PARAMETER">type_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ Creates a <firstterm>typed table</firstterm>, 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 <literal>DROP TYPE ... CASCADE</literal>).
+ </para>
+
+ <para>
+ 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 <literal>CREATE TABLE</literal> command.
+ But the <literal>CREATE TABLE</literal> command can add defaults
+ and constraints to the table and can specify storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">column_name</replaceable></term>
<listitem>
<para>
@@ -1182,6 +1213,17 @@ CREATE TABLE cinemas (
</programlisting>
</para>
+ <para>
+ Create a composite type and a typed table:
+<programlisting>
+CREATE TYPE employee_type AS (name text, salary numeric);
+
+CREATE TABLE employees OF employee_type (
+ PRIMARY KEY (name),
+ salary WITH OPTIONS DEFAULT 1000
+);
+</programlisting>
+ </para>
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
@@ -1331,6 +1373,19 @@ CREATE TABLE cinemas (
and <literal>USING INDEX TABLESPACE</literal> are extensions.
</para>
</refsect2>
+
+ <refsect2>
+ <title>Typed Tables</title>
+
+ <para>
+ 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 <quote>self-referencing column</quote>. PostgreSQL does not
+ support these self-referencing columns explicitly, but the same
+ effect can be had using the OID feature.
+ </para>
+ </refsect2>
</refsect1>
@@ -1341,6 +1396,7 @@ CREATE TABLE cinemas (
<member><xref linkend="sql-altertable" endterm="sql-altertable-title"></member>
<member><xref linkend="sql-droptable" endterm="sql-droptable-title"></member>
<member><xref linkend="sql-createtablespace" endterm="sql-createtablespace-title"></member>
+ <member><xref linkend="sql-createtype" endterm="sql-createtype-title"></member>
</simplelist>
</refsect1>
</refentry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index b37ee39..7ec96de 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -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 81cd8c5..dfe5911 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -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,
@@ -665,6 +667,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);
@@ -723,6 +726,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,
@@ -781,6 +785,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;
@@ -872,6 +877,7 @@ heap_create_with_catalog(const char *relname,
Oid reltablespace,
Oid relid,
Oid reltypeid,
+ Oid reloftypeid,
Oid ownerid,
TupleDesc tupdesc,
List *cooked_constraints,
@@ -1093,6 +1099,7 @@ heap_create_with_catalog(const char *relname,
new_rel_desc,
relid,
new_type_oid,
+ reloftypeid,
ownerid,
relkind,
PointerGetDatum(relacl),
@@ -1135,6 +1142,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 6ef351a..6c34186 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -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/namespace.c b/src/backend/catalog/namespace.c
index 36e2a8b..69677c0 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -2277,6 +2277,11 @@ DeconstructQualifiedName(List *names,
errmsg("cross-database references are not implemented: %s",
NameListToString(names))));
break;
+ case 0:
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("improper qualified name (zero-length name list)")));
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index e0fac3d..4666771 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -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 99d6258..39497f0 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -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 30e49b1..f42aa2d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -363,6 +363,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
@@ -445,6 +446,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.
@@ -523,6 +529,7 @@ DefineRelation(CreateStmt *stmt, char relkind)
tablespaceId,
InvalidOid,
InvalidOid,
+ ofTypeId,
GetUserId(),
descriptor,
list_concat(cookedDefaults,
@@ -1232,17 +1239,33 @@ MergeAttributes(List *schema, List *supers, bool istemp,
foreach(entry, schema)
{
ColumnDef *coldef = lfirst(entry);
- ListCell *rest;
+ ListCell *rest = lnext(entry);
+ ListCell *prev = entry;
- 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)
+ {
+ if (coldef->is_from_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;
}
}
@@ -3584,6 +3607,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);
/*
@@ -4332,6 +4360,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 4aa4e25..89a21a7 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -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 0faa05d..d1bbf2d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -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 62bf3b1..3adeacd 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -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 d7c62ed..dca39df 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -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 8201f29..abdc41f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -277,6 +277,7 @@ static TypeName *TableFuncTypeName(List *columns);
%type <list> 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 <vsetstmt> set_rest SetResetClause
-%type <node> TableElement ConstraintElem TableFuncElement
-%type <node> columnDef
+%type <node> TableElement TypedTableElement ConstraintElem TableFuncElement
+%type <node> columnDef columnOptions
%type <defelt> def_elem reloption_elem old_aggr_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr func_expr AexprConst indirection_el
@@ -2194,21 +2195,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 <UDT> (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;
}
;
@@ -2234,6 +2233,11 @@ OptTableElementList:
| /*EMPTY*/ { $$ = NIL; }
;
+OptTypedTableElementList:
+ '(' TypedTableElementList ')' { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
TableElementList:
TableElement
{
@@ -2245,12 +2249,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);
@@ -2262,6 +2282,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 b2ba8f8..ff9453d 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -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 99dd578..a459950 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -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 77786a6..781959e 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -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;
/* 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 790368f..621343a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -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;
@@ -2028,6 +2032,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 7aa8e06..d6ae6a4 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201001071
+#define CATALOG_VERSION_NO 201001091
#endif
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 775f0f7..8b1ac22 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -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 c5b15f5..8e43daa 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -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 ba51833..3493f56 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -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) */
@@ -1355,6 +1356,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 0000000..fce1f60
--- /dev/null
+++ b/src/test/regress/expected/typed_table.out
@@ -0,0 +1,81 @@
+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
+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 3605898..2cf9f35 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -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 dc97b54..0c2b5ce 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -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 0000000..8a8806e
--- /dev/null
+++ b/src/test/regress/sql/typed_table.sql
@@ -0,0 +1,39 @@
+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;
+
+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;
On 1/10/10 2:34 PM, Peter Eisentraut wrote:
On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).
Nice. Can we come up with a better name for the feature, though?
"Composite Type Tables"? "Type-Table Inheritance"?
--Josh Berkus
On sön, 2010-01-10 at 15:27 -0800, Josh Berkus wrote:
On 1/10/10 2:34 PM, Peter Eisentraut wrote:
On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).Nice. Can we come up with a better name for the feature, though?
"Composite Type Tables"? "Type-Table Inheritance"?
"Typed tables" is the official SQL standard name for the feature, and
it's also used in DB2 documentation. So I kind of would prefer to keep
it.
On Mon, Nov 9, 2009 at 5:15 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On Sun, 2009-11-08 at 21:17 +0000, Simon Riggs wrote:
Not sure I see why this is good. Why is issuing CREATE TYPE so much
easier than using CREATE TABLE? Is it worth the extra syntax and code to
support it? Can we do anything additional as a result of this?These are tools to improve database design in particular situations.
Nobody really *needs* this, but then again, you don't really need CREATE
TYPE for composite types in the first place. Using CREATE TABLE instead
of CREATE TYPE creates a bunch of extra things you don't need. For
example, files are created, VACUUM and ANALYZE have to keep checking the
table, backup tools think they have to back up the table, and you have
to check that no one actually inserts anything into the table.
you also get the ability to alter the type though, which at present
outweighs the disadvantages in most cases (IMO).
I happen to be a fan of your proposal...mainly because it highlights
the highly under-appreciated composite type handling of the database.
I especially am excited about getting 'ALTER TYPE' in the future :-).
Do you think that we will ever able to apply constraints to composite
type that will be enforced on a cast?
merlin
Peter,
"Typed tables" is the official SQL standard name for the feature, and
it's also used in DB2 documentation. So I kind of would prefer to keep
it.
Sorry, I missed the SQL standard part in the thread. Ignore the noise.
Oh, and BTW, +1 on accepting this, pending patch quality and all that.
--Josh Berkus
Peter Eisentraut wrote:
On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).CREATE TYPE persons_type AS (name text, bdate date);
CREATE TABLE persons OF persons_type;
Or the fancy version:
CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
And here is the first patch for that. The feature is complete as far as
I had wanted it. I would like to add ALTER TYPE support, but that can
come as a separate patch.
+1
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance. For various reasons, we've internally adopted using create
table for all composites and use a c-like naming convenstion of
appending _t to such beasts.
I'll just throw a little meat into the pack wolves....constraints....?
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance.
I don't really understand the purpose of that.
For various reasons, we've internally adopted using create
table for all composites and use a c-like naming convenstion of
appending _t to such beasts.
Yes, I have a similar convention.
Peter Eisentraut wrote:
On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance.I don't really understand the purpose of that.
What is the point of CREATE TYPE name AS () syntax? Why would one use create
type when there is create table? Does it provide additional functionality I am
unaware of or does it exist for comformance reasons?
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
Peter Eisentraut wrote:
On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance.I don't really understand the purpose of that.
What is the point of CREATE TYPE name AS () syntax? Why would one use create
type when there is create table? Does it provide additional functionality I am
unaware of or does it exist for comformance reasons?
Well, that is a very deep question. ;-) I suppose a concise answer
would be that types are for passing data around between functions, and
tables are for storing data on disk.
2010/1/12 Peter Eisentraut <peter_e@gmx.net>:
On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
Peter Eisentraut wrote:
On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance.I don't really understand the purpose of that.
What is the point of CREATE TYPE name AS () syntax? Why would one use create
type when there is create table? Does it provide additional functionality I am
unaware of or does it exist for comformance reasons?Well, that is a very deep question. ;-) I suppose a concise answer
would be that types are for passing data around between functions, and
tables are for storing data on disk.
it should help only for "small" tables. It's looks well, but it can be
very slow and very memory expensive for bigger tables. I thing, we
need some QUERY->cursor translation mechanism. Memory based solution
(with arrays) is better than nothing, but it cannot be for all.
Pavel
Show quoted text
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Peter Eisentraut wrote:
On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
Peter Eisentraut wrote:
On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance.I don't really understand the purpose of that.
What is the point of CREATE TYPE name AS () syntax? Why would one use create
type when there is create table? Does it provide additional functionality I am
unaware of or does it exist for comformance reasons?Well, that is a very deep question. ;-) I suppose a concise answer
would be that types are for passing data around between functions, and
tables are for storing data on disk.
In practice, tables can be used for passing data around or storing it on disk.
So, I guess my question remains unanswered as to what the composite type offers
that a table doesn't; other than a name that better suits the task.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
In practice, tables can be used for passing data around or storing it on disk.
So, I guess my question remains unanswered as to what the composite type offers
that a table doesn't; other than a name that better suits the task.
The arguments of functions are types, not tables. So you need types if
you want to use functions.
Peter Eisentraut wrote:
On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
In practice, tables can be used for passing data around or storing it on disk.
So, I guess my question remains unanswered as to what the composite type offers
that a table doesn't; other than a name that better suits the task.The arguments of functions are types, not tables. So you need types if
you want to use functions.
What is the point of this discussion? We're not going to remove the
facility for composite types, regardless of whether or not some people
regard them as unnecessary. And "a name that better suits the task" is
not to be sneered at anyway.
cheers
andrew
Peter Eisentraut wrote:
On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
In practice, tables can be used for passing data around or storing it on disk.
So, I guess my question remains unanswered as to what the composite type offers
that a table doesn't; other than a name that better suits the task.The arguments of functions are types, not tables. So you need types if
you want to use functions.
really....
create table mytype_t (a int, b int);
create function mytype_func(t mytype_t) returns int as
$$
select ($1).a + ($1).b;
$$ language sql;
select mytype_func((10, 10)::mytype_t);
mytype_func
-------------
20
(1 row)
A table is a record type (backend/util/adt/rowtypes.c) as is a
composite. One difference is pg_class.relkind is 'r' for relation vs.
'c' for composite.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
What is the point of this discussion? We're not going to remove the
facility for composite types, regardless of whether or not some people
regard them as unnecessary. And "a name that better suits the task" is
not to be sneered at anyway.
I never asked for anything to be removed nor do I sneer :) Honestly, I
was only trying to understand why it existed.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
On Tue, Jan 12, 2010 at 9:00 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
What is the point of this discussion? We're not going to remove the facility
for composite types, regardless of whether or not some people regard them as
unnecessary. And "a name that better suits the task" is not to be sneered at
anyway.
nobody is arguing to remove the create type syntax. I suppose in
hindsight more thought might have been given to the overlap w/create
table. Also you have to admit that having both 'create type' and
'create type as' which do completely different things is pretty
awkward. in addition, we have 'create table' which gives us three
different methods of creating types, each with their own nuance and
advantages. please understand, I'm not griping: the postgresql type
system is wonderful...there's nothing else quite like it out there.
The questions I am posing are this:
*) should 'create type as' get an 'alter'? ( I think most would think so)
*) if so, how do you distinguish between the composite and non
composite version? How would this command look?
*) should we be able to define check constraints on composite types
(presumably, enforced on a cast)?
*) should 'create type as' should be walled off with 'create table'
handling most cases of type creation? (previously would have said yes,
but with typed table enhancement, probably not)
merlin
merlin
On tis, 2010-01-12 at 09:54 -0500, Merlin Moncure wrote:
*) should 'create type as' get an 'alter'? ( I think most would think so)
Working on that right now ...
*) if so, how do you distinguish between the composite and non
composite version? How would this command look?
I'm only dealing with the composite types right now, and the syntax is
ALTER TYPE name ADD/DROP ATTRIBUTE name, per SQL standard.
*) should we be able to define check constraints on composite types
(presumably, enforced on a cast)?
That could be an interesting feature addition. It'd basically be the
composite-type version of domains.
*) should 'create type as' should be walled off with 'create table'
handling most cases of type creation? (previously would have said yes,
but with typed table enhancement, probably not)
This might be a matter of taste, but also note that these interfaces are
prescribed by the SQL standard, so if you have them, they should do the
things the spec says.
On 01/12/2010 06:43 AM, Andrew Chernow wrote:
What is the point of this discussion? We're not going to remove the
facility for composite types, regardless of whether or not some people
regard them as unnecessary. And "a name that better suits the task" is
not to be sneered at anyway.I never asked for anything to be removed nor do I sneer :) Honestly, I
was only trying to understand why it existed.
It exists because once upon a time when SRFs were first created, and you
were using a function returning SETOF RECORD, you would either have to
enumerate every column definition in your query, or create a "dummy"
table that had the right columns/types to match your return tuple.
That solution was generally viewed as grotty -- the former is a lot of
typing and clutter, and the latter creates a table with the only purpose
being to get the needed composite type created. Therefore we added the
ability to skip the table creation and just produce the needed composite
type.
HTH
Joe