1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
CREATE TABLE ttable1 OF nothing;
ERROR: type "nothing" does not exist
LINE 1: CREATE TABLE ttable1 OF nothing;
^
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
CREATE TABLE IF NOT EXISTS persons OF person_type;
NOTICE: relation "persons" already exists, skipping
SELECT * FROM persons;
id | name
----+------
(0 rows)
\d persons
Table "public.persons"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
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)
-- certain ALTER TABLE operations on typed tables are not allowed
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
ALTER TABLE persons ALTER COLUMN name TYPE varchar;
ERROR: cannot alter column type of typed table
LINE 1: ALTER TABLE persons ALTER COLUMN name TYPE varchar;
^
CREATE TABLE stuff (id int);
ALTER TABLE persons INHERIT stuff;
ERROR: cannot change inheritance 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)
);
\d persons2
Table "public.persons2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"persons2_pkey" PRIMARY KEY, btree (id)
"persons2_name_key" UNIQUE CONSTRAINT, btree (name)
Typed table of type: person_type
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
name WITH OPTIONS DEFAULT ''
);
\d persons3
Table "public.persons3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------
id | integer | | not null |
name | text | | | ''::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
CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
ERROR: type stuff is the row type of another table
DETAIL: A typed table must use a stand-alone composite type created with CREATE TYPE.
CREATE TYPE tt_enum_type AS ENUM ('a');
CREATE TABLE of_tt_enum_type OF tt_enum_type; -- not a composite type at all
ERROR: type tt_enum_type is not a composite type
DROP TYPE tt_enum_type;
DROP TABLE stuff;
-- implicit casting
CREATE TYPE person_type AS (id int, name text);
CREATE TABLE persons OF person_type;
INSERT INTO persons VALUES (1, 'test');
CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
SELECT id, namelen(persons) FROM persons;
id | namelen
----+---------
1 | 4
(1 row)
CREATE TABLE persons2 OF person_type (
id WITH OPTIONS PRIMARY KEY,
UNIQUE (name)
);
\d persons2
Table "public.persons2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"persons2_pkey" PRIMARY KEY, btree (id)
"persons2_name_key" UNIQUE CONSTRAINT, btree (name)
Typed table of type: person_type
CREATE TABLE persons3 OF person_type (
PRIMARY KEY (id),
name NOT NULL DEFAULT ''
);
\d persons3
Table "public.persons3"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------
id | integer | | not null |
name | text | | not null | ''::text
Indexes:
"persons3_pkey" PRIMARY KEY, btree (id)
Typed table of type: person_type
|
