Type table feature

This adds the CREATE TABLE name OF type command, per SQL standard.

4 files changed, 131 insertions, 3 deletions

diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out
new file mode 100644
index 0000000000..e92cdf65e1
--- /dev/null
+++ b/src/test/regress/expected/typed_table.out
@@ -0,0 +1,85 @@
+CREATE TABLE ttable1 OF nothing;
+ERROR:  type "nothing" does not exist
+CREATE TYPE person_type AS (id int, name text);
+CREATE TABLE persons OF person_type;
+SELECT * FROM persons;
+ id | name 
+----+------
+(0 rows)
+
+\d persons
+    Table "public.persons"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ id     | integer | 
+ name   | text    | 
+Typed table of type: person_type
+
+CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
+LANGUAGE SQL
+AS $$
+    SELECT * FROM persons;
+$$;
+SELECT * FROM get_all_persons();
+ id | name 
+----+------
+(0 rows)
+
+ALTER TABLE persons ADD COLUMN comment text;
+ERROR:  cannot add column to typed table
+ALTER TABLE persons DROP COLUMN name;
+ERROR:  cannot drop column from typed table
+ALTER TABLE persons RENAME COLUMN id TO num;
+ERROR:  cannot rename column of typed table
+CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
+ERROR:  column "myname" does not exist
+CREATE TABLE persons2 OF person_type (
+    id WITH OPTIONS PRIMARY KEY,
+    UNIQUE (name)
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "persons2_pkey" for table "persons2"
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "persons2_name_key" for table "persons2"
+\d persons2
+   Table "public.persons2"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ id     | integer | not null
+ name   | text    | 
+Indexes:
+    "persons2_pkey" PRIMARY KEY, btree (id)
+    "persons2_name_key" UNIQUE, btree (name)
+Typed table of type: person_type
+
+CREATE TABLE persons3 OF person_type (
+    PRIMARY KEY (id),
+    name WITH OPTIONS DEFAULT ''
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "persons3_pkey" for table "persons3"
+\d persons3
+       Table "public.persons3"
+ Column |  Type   |    Modifiers     
+--------+---------+------------------
+ id     | integer | not null
+ name   | text    | default ''::text
+Indexes:
+    "persons3_pkey" PRIMARY KEY, btree (id)
+Typed table of type: person_type
+
+CREATE TABLE persons4 OF person_type (
+    name WITH OPTIONS NOT NULL,
+    name WITH OPTIONS DEFAULT ''  -- error, specified more than once
+);
+ERROR:  column "name" specified more than once
+DROP TYPE person_type RESTRICT;
+ERROR:  cannot drop type person_type because other objects depend on it
+DETAIL:  table persons depends on type person_type
+function get_all_persons() depends on type person_type
+table persons2 depends on type person_type
+table persons3 depends on type person_type
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP TYPE person_type CASCADE;
+NOTICE:  drop cascades to 4 other objects
+DETAIL:  drop cascades to table persons
+drop cascades to function get_all_persons()
+drop cascades to table persons2
+drop cascades to table persons3
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7d5762f916..fa5f507e45 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -1,5 +1,5 @@
 # ----------
-# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.57 2009/08/24 03:10:16 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.58 2010/01/28 23:21:13 petere Exp $
 #
 # By convention, we put no more than twenty tests in any one parallel group;
 # this limits the number of connections needed to run the tests.
@@ -52,7 +52,7 @@ test: copy copyselect
 # ----------
 # Another group of parallel tests
 # ----------
-test: constraints triggers create_misc create_aggregate create_operator inherit vacuum drop_if_exists create_cast
+test: constraints triggers create_misc create_aggregate create_operator inherit typed_table vacuum drop_if_exists create_cast
 
 # Depends on the above
 test: create_index create_view
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 4f61a2d575..037abf2341 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.54 2009/08/24 03:10:16 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.55 2010/01/28 23:21:13 petere Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: tablespace
 test: boolean
@@ -60,6 +60,7 @@ test: create_operator
 test: create_index
 test: drop_if_exists
 test: inherit
+test: typed_table
 test: vacuum
 test: create_view
 test: sanity_check
diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/typed_table.sql
new file mode 100644
index 0000000000..4e81f1dd6a
--- /dev/null
+++ b/src/test/regress/sql/typed_table.sql
@@ -0,0 +1,42 @@
+CREATE TABLE ttable1 OF nothing;
+
+CREATE TYPE person_type AS (id int, name text);
+CREATE TABLE persons OF person_type;
+SELECT * FROM persons;
+\d persons
+
+CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
+LANGUAGE SQL
+AS $$
+    SELECT * FROM persons;
+$$;
+
+SELECT * FROM get_all_persons();
+
+ALTER TABLE persons ADD COLUMN comment text;
+ALTER TABLE persons DROP COLUMN name;
+ALTER TABLE persons RENAME COLUMN id TO num;
+
+CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
+
+CREATE TABLE persons2 OF person_type (
+    id WITH OPTIONS PRIMARY KEY,
+    UNIQUE (name)
+);
+
+\d persons2
+
+CREATE TABLE persons3 OF person_type (
+    PRIMARY KEY (id),
+    name WITH OPTIONS DEFAULT ''
+);
+
+\d persons3
+
+CREATE TABLE persons4 OF person_type (
+    name WITH OPTIONS NOT NULL,
+    name WITH OPTIONS DEFAULT ''  -- error, specified more than once
+);
+
+DROP TYPE person_type RESTRICT;
+DROP TYPE person_type CASCADE;