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