1
CREATE TABLE ttable1 OF nothing;
2
ERROR: type "nothing" does not exist
3
CREATE TYPE person_type AS (id int, name text);
4
CREATE TABLE persons OF person_type;
5
CREATE TABLE IF NOT EXISTS persons OF person_type;
6
NOTICE: relation "persons" already exists, skipping
13
Table "public.persons"
14
Column | Type | Modifiers
15
--------+---------+-----------
18
Typed table of type: person_type
20
CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
23
SELECT * FROM persons;
25
SELECT * FROM get_all_persons();
30
-- certain ALTER TABLE operations on typed tables are not allowed
31
ALTER TABLE persons ADD COLUMN comment text;
32
ERROR: cannot add column to typed table
33
ALTER TABLE persons DROP COLUMN name;
34
ERROR: cannot drop column from typed table
35
ALTER TABLE persons RENAME COLUMN id TO num;
36
ERROR: cannot rename column of typed table
37
ALTER TABLE persons ALTER COLUMN name TYPE varchar;
38
ERROR: cannot alter column type of typed table
39
CREATE TABLE stuff (id int);
40
ALTER TABLE persons INHERIT stuff;
41
ERROR: cannot change inheritance of typed table
42
CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
43
ERROR: column "myname" does not exist
44
CREATE TABLE persons2 OF person_type (
45
id WITH OPTIONS PRIMARY KEY,
49
Table "public.persons2"
50
Column | Type | Modifiers
51
--------+---------+-----------
52
id | integer | not null
55
"persons2_pkey" PRIMARY KEY, btree (id)
56
"persons2_name_key" UNIQUE CONSTRAINT, btree (name)
57
Typed table of type: person_type
59
CREATE TABLE persons3 OF person_type (
61
name WITH OPTIONS DEFAULT ''
64
Table "public.persons3"
65
Column | Type | Modifiers
66
--------+---------+------------------
67
id | integer | not null
68
name | text | default ''::text
70
"persons3_pkey" PRIMARY KEY, btree (id)
71
Typed table of type: person_type
73
CREATE TABLE persons4 OF person_type (
74
name WITH OPTIONS NOT NULL,
75
name WITH OPTIONS DEFAULT '' -- error, specified more than once
77
ERROR: column "name" specified more than once
78
DROP TYPE person_type RESTRICT;
79
ERROR: cannot drop type person_type because other objects depend on it
80
DETAIL: table persons depends on type person_type
81
function get_all_persons() depends on type person_type
82
table persons2 depends on type person_type
83
table persons3 depends on type person_type
84
HINT: Use DROP ... CASCADE to drop the dependent objects too.
85
DROP TYPE person_type CASCADE;
86
NOTICE: drop cascades to 4 other objects
87
DETAIL: drop cascades to table persons
88
drop cascades to function get_all_persons()
89
drop cascades to table persons2
90
drop cascades to table persons3
91
CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
92
ERROR: type stuff is not a composite type
95
CREATE TYPE person_type AS (id int, name text);
96
CREATE TABLE persons OF person_type;
97
INSERT INTO persons VALUES (1, 'test');
98
CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
99
SELECT id, namelen(persons) FROM persons;
105
DROP TYPE person_type CASCADE;
106
NOTICE: drop cascades to 2 other objects
107
DETAIL: drop cascades to table persons
108
drop cascades to function namelen(person_type)