1CREATE TABLE ttable1 OF nothing;
2
3CREATE TYPE person_type AS (id int, name text);
4CREATE TABLE persons OF person_type;
5CREATE TABLE IF NOT EXISTS persons OF person_type;
6SELECT * FROM persons;
7\d persons
8
9CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
10LANGUAGE SQL
11AS $$
12    SELECT * FROM persons;
13$$;
14
15SELECT * FROM get_all_persons();
16
17-- certain ALTER TABLE operations on typed tables are not allowed
18ALTER TABLE persons ADD COLUMN comment text;
19ALTER TABLE persons DROP COLUMN name;
20ALTER TABLE persons RENAME COLUMN id TO num;
21ALTER TABLE persons ALTER COLUMN name TYPE varchar;
22CREATE TABLE stuff (id int);
23ALTER TABLE persons INHERIT stuff;
24
25CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
26
27CREATE TABLE persons2 OF person_type (
28    id WITH OPTIONS PRIMARY KEY,
29    UNIQUE (name)
30);
31
32\d persons2
33
34CREATE TABLE persons3 OF person_type (
35    PRIMARY KEY (id),
36    name WITH OPTIONS DEFAULT ''
37);
38
39\d persons3
40
41CREATE TABLE persons4 OF person_type (
42    name WITH OPTIONS NOT NULL,
43    name WITH OPTIONS DEFAULT ''  -- error, specified more than once
44);
45
46DROP TYPE person_type RESTRICT;
47DROP TYPE person_type CASCADE;
48
49CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
50
51DROP TABLE stuff;
52
53
54-- implicit casting
55
56CREATE TYPE person_type AS (id int, name text);
57CREATE TABLE persons OF person_type;
58INSERT INTO persons VALUES (1, 'test');
59
60CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
61SELECT id, namelen(persons) FROM persons;
62
63CREATE TABLE persons2 OF person_type (
64    id WITH OPTIONS PRIMARY KEY,
65    UNIQUE (name)
66);
67
68\d persons2
69
70CREATE TABLE persons3 OF person_type (
71    PRIMARY KEY (id),
72    name NOT NULL DEFAULT ''
73);
74
75\d persons3
76