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-- Test stand-alone composite type
88
89CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42);
90
91CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
92  SELECT * FROM default_test;
93' LANGUAGE SQL;
94
95SELECT * FROM get_default_test();
96
97-- Test comments
98COMMENT ON TYPE bad IS 'bad comment';
99COMMENT ON TYPE default_test_row IS 'good comment';
100COMMENT ON TYPE default_test_row IS NULL;
101COMMENT ON COLUMN default_test_row.nope IS 'bad comment';
102COMMENT ON COLUMN default_test_row.f1 IS 'good comment';
103COMMENT ON COLUMN default_test_row.f1 IS NULL;
104
105-- Check shell type create for existing types
106CREATE TYPE text_w_default;		-- should fail
107
108DROP TYPE default_test_row CASCADE;
109
110DROP TABLE default_test;
111
112-- Check type create with input/output incompatibility
113CREATE TYPE not_existing_type (INPUT = array_in,
114    OUTPUT = array_out,
115    ELEMENT = int,
116    INTERNALLENGTH = 32);
117
118-- Check dependency transfer of opaque functions when creating a new type
119CREATE FUNCTION base_fn_in(cstring) RETURNS opaque AS 'boolin'
120    LANGUAGE internal IMMUTABLE STRICT;
121CREATE FUNCTION base_fn_out(opaque) RETURNS opaque AS 'boolout'
122    LANGUAGE internal IMMUTABLE STRICT;
123CREATE TYPE base_type(INPUT = base_fn_in, OUTPUT = base_fn_out);
124DROP FUNCTION base_fn_in(cstring); -- error
125DROP FUNCTION base_fn_out(opaque); -- error
126DROP TYPE base_type; -- error
127DROP TYPE base_type CASCADE;
128
129-- Check usage of typmod with a user-defined type
130-- (we have borrowed numeric's typmod functions)
131
132CREATE TEMP TABLE mytab (foo widget(42,13,7));     -- should fail
133CREATE TEMP TABLE mytab (foo widget(42,13));
134
135SELECT format_type(atttypid,atttypmod) FROM pg_attribute
136WHERE attrelid = 'mytab'::regclass AND attnum > 0;
137