1--
2-- CREATE_TYPE
3--
4
5--
6-- Note: widget_in/out were created in create_function_1, without any
7-- prior shell-type creation.  These commands therefore complete a test
8-- of the "old style" approach of making the functions first.
9--
10CREATE TYPE widget (
11   internallength = 24,
12   input = widget_in,
13   output = widget_out,
14   typmod_in = numerictypmodin,
15   typmod_out = numerictypmodout,
16   alignment = double
17);
18
19CREATE TYPE city_budget (
20   internallength = 16,
21   input = int44in,
22   output = int44out,
23   element = int4,
24   category = 'x',   -- just to verify the system will take it
25   preferred = true  -- ditto
26);
27
28-- Test creation and destruction of shell types
29CREATE TYPE shell;
30CREATE TYPE shell;   -- fail, type already present
31DROP TYPE shell;
32DROP TYPE shell;     -- fail, type not exist
33
34-- also, let's leave one around for purposes of pg_dump testing
35CREATE TYPE myshell;
36
37--
38-- Test type-related default values (broken in releases before PG 7.2)
39--
40-- This part of the test also exercises the "new style" approach of making
41-- a shell type and then filling it in.
42--
43CREATE TYPE int42;
44CREATE TYPE text_w_default;
45
46-- Make dummy I/O routines using the existing internal support for int4, text
47CREATE FUNCTION int42_in(cstring)
48   RETURNS int42
49   AS 'int4in'
50   LANGUAGE internal STRICT IMMUTABLE;
51CREATE FUNCTION int42_out(int42)
52   RETURNS cstring
53   AS 'int4out'
54   LANGUAGE internal STRICT IMMUTABLE;
55CREATE FUNCTION text_w_default_in(cstring)
56   RETURNS text_w_default
57   AS 'textin'
58   LANGUAGE internal STRICT IMMUTABLE;
59CREATE FUNCTION text_w_default_out(text_w_default)
60   RETURNS cstring
61   AS 'textout'
62   LANGUAGE internal STRICT IMMUTABLE;
63
64CREATE TYPE int42 (
65   internallength = 4,
66   input = int42_in,
67   output = int42_out,
68   alignment = int4,
69   default = 42,
70   passedbyvalue
71);
72
73CREATE TYPE text_w_default (
74   internallength = variable,
75   input = text_w_default_in,
76   output = text_w_default_out,
77   alignment = int4,
78   default = 'zippo'
79);
80
81CREATE TABLE default_test (f1 text_w_default, f2 int42);
82
83INSERT INTO default_test DEFAULT VALUES;
84
85SELECT * FROM default_test;
86
87-- invalid: non-lowercase quoted identifiers
88CREATE TYPE case_int42 (
89	"Internallength" = 4,
90	"Input" = int42_in,
91	"Output" = int42_out,
92	"Alignment" = int4,
93	"Default" = 42,
94	"Passedbyvalue"
95);
96
97-- Test stand-alone composite type
98
99CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42);
100
101CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
102  SELECT * FROM default_test;
103' LANGUAGE SQL;
104
105SELECT * FROM get_default_test();
106
107-- Test comments
108COMMENT ON TYPE bad IS 'bad comment';
109COMMENT ON TYPE default_test_row IS 'good comment';
110COMMENT ON TYPE default_test_row IS NULL;
111COMMENT ON COLUMN default_test_row.nope IS 'bad comment';
112COMMENT ON COLUMN default_test_row.f1 IS 'good comment';
113COMMENT ON COLUMN default_test_row.f1 IS NULL;
114
115-- Check shell type create for existing types
116CREATE TYPE text_w_default;		-- should fail
117
118DROP TYPE default_test_row CASCADE;
119
120DROP TABLE default_test;
121
122-- Check type create with input/output incompatibility
123CREATE TYPE not_existing_type (INPUT = array_in,
124    OUTPUT = array_out,
125    ELEMENT = int,
126    INTERNALLENGTH = 32);
127
128-- Check dependency transfer of opaque functions when creating a new type
129CREATE FUNCTION base_fn_in(cstring) RETURNS opaque AS 'boolin'
130    LANGUAGE internal IMMUTABLE STRICT;
131CREATE FUNCTION base_fn_out(opaque) RETURNS opaque AS 'boolout'
132    LANGUAGE internal IMMUTABLE STRICT;
133CREATE TYPE base_type(INPUT = base_fn_in, OUTPUT = base_fn_out);
134DROP FUNCTION base_fn_in(cstring); -- error
135DROP FUNCTION base_fn_out(opaque); -- error
136DROP TYPE base_type; -- error
137DROP TYPE base_type CASCADE;
138
139-- Check usage of typmod with a user-defined type
140-- (we have borrowed numeric's typmod functions)
141
142CREATE TEMP TABLE mytab (foo widget(42,13,7));     -- should fail
143CREATE TEMP TABLE mytab (foo widget(42,13));
144
145SELECT format_type(atttypid,atttypmod) FROM pg_attribute
146WHERE attrelid = 'mytab'::regclass AND attnum > 0;
147
148-- might as well exercise the widget type while we're here
149INSERT INTO mytab VALUES ('(1,2,3)'), ('(-44,5.5,12)');
150TABLE mytab;
151
152-- and test format_type() a bit more, too
153select format_type('varchar'::regtype, 42);
154select format_type('bpchar'::regtype, null);
155-- this behavior difference is intentional
156select format_type('bpchar'::regtype, -1);
157