5 -- Note: widget_in/out were created in create_function_1, without any
6 -- prior shell-type creation. These commands therefore complete a test
7 -- of the "old style" approach of making the functions first.
13 typmod_in = numerictypmodin,
14 typmod_out = numerictypmodout,
17 CREATE TYPE city_budget (
22 category = 'x', -- just to verify the system will take it
23 preferred = true -- ditto
25 -- Test creation and destruction of shell types
27 CREATE TYPE shell; -- fail, type already present
28 ERROR: type "shell" already exists
30 DROP TYPE shell; -- fail, type not exist
31 ERROR: type "shell" does not exist
32 -- also, let's leave one around for purposes of pg_dump testing
35 -- Test type-related default values (broken in releases before PG 7.2)
37 -- This part of the test also exercises the "new style" approach of making
38 -- a shell type and then filling it in.
41 CREATE TYPE text_w_default;
42 -- Make dummy I/O routines using the existing internal support for int4, text
43 CREATE FUNCTION int42_in(cstring)
46 LANGUAGE internal STRICT IMMUTABLE;
47 NOTICE: return type int42 is only a shell
48 CREATE FUNCTION int42_out(int42)
51 LANGUAGE internal STRICT IMMUTABLE;
52 NOTICE: argument type int42 is only a shell
53 CREATE FUNCTION text_w_default_in(cstring)
54 RETURNS text_w_default
56 LANGUAGE internal STRICT IMMUTABLE;
57 NOTICE: return type text_w_default is only a shell
58 CREATE FUNCTION text_w_default_out(text_w_default)
61 LANGUAGE internal STRICT IMMUTABLE;
62 NOTICE: argument type text_w_default is only a shell
71 CREATE TYPE text_w_default (
72 internallength = variable,
73 input = text_w_default_in,
74 output = text_w_default_out,
78 CREATE TABLE default_test (f1 text_w_default, f2 int42);
79 INSERT INTO default_test DEFAULT VALUES;
80 SELECT * FROM default_test;
86 -- Test stand-alone composite type
87 CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42);
88 CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
89 SELECT * FROM default_test;
91 SELECT * FROM get_default_test();
98 COMMENT ON TYPE bad IS 'bad comment';
99 ERROR: type "bad" does not exist
100 COMMENT ON TYPE default_test_row IS 'good comment';
101 COMMENT ON TYPE default_test_row IS NULL;
102 COMMENT ON COLUMN default_test_row.nope IS 'bad comment';
103 ERROR: column "nope" of relation "default_test_row" does not exist
104 COMMENT ON COLUMN default_test_row.f1 IS 'good comment';
105 COMMENT ON COLUMN default_test_row.f1 IS NULL;
106 -- Check shell type create for existing types
107 CREATE TYPE text_w_default; -- should fail
108 ERROR: type "text_w_default" already exists
109 DROP TYPE default_test_row CASCADE;
110 NOTICE: drop cascades to function get_default_test()
111 DROP TABLE default_test;
112 -- Check type create with input/output incompatibility
113 CREATE TYPE not_existing_type (INPUT = array_in,
116 INTERNALLENGTH = 32);
117 ERROR: function array_out(not_existing_type) does not exist
118 -- Check dependency transfer of opaque functions when creating a new type
119 CREATE FUNCTION base_fn_in(cstring) RETURNS opaque AS 'boolin'
120 LANGUAGE internal IMMUTABLE STRICT;
121 CREATE FUNCTION base_fn_out(opaque) RETURNS opaque AS 'boolout'
122 LANGUAGE internal IMMUTABLE STRICT;
123 CREATE TYPE base_type(INPUT = base_fn_in, OUTPUT = base_fn_out);
124 WARNING: changing argument type of function base_fn_out from "opaque" to base_type
125 WARNING: changing return type of function base_fn_in from opaque to base_type
126 WARNING: changing return type of function base_fn_out from opaque to cstring
127 DROP FUNCTION base_fn_in(cstring); -- error
128 ERROR: cannot drop function base_fn_in(cstring) because other objects depend on it
129 DETAIL: type base_type depends on function base_fn_in(cstring)
130 function base_fn_out(base_type) depends on type base_type
131 HINT: Use DROP ... CASCADE to drop the dependent objects too.
132 DROP FUNCTION base_fn_out(opaque); -- error
133 ERROR: function base_fn_out(opaque) does not exist
134 DROP TYPE base_type; -- error
135 ERROR: cannot drop type base_type because other objects depend on it
136 DETAIL: function base_fn_out(base_type) depends on type base_type
137 function base_fn_in(cstring) depends on type base_type
138 HINT: Use DROP ... CASCADE to drop the dependent objects too.
139 DROP TYPE base_type CASCADE;
140 NOTICE: drop cascades to 2 other objects
141 DETAIL: drop cascades to function base_fn_out(base_type)
142 drop cascades to function base_fn_in(cstring)
143 -- Check usage of typmod with a user-defined type
144 -- (we have borrowed numeric's typmod functions)
145 CREATE TEMP TABLE mytab (foo widget(42,13,7)); -- should fail
146 ERROR: invalid NUMERIC type modifier
147 LINE 1: CREATE TEMP TABLE mytab (foo widget(42,13,7));
149 CREATE TEMP TABLE mytab (foo widget(42,13));
150 SELECT format_type(atttypid,atttypmod) FROM pg_attribute
151 WHERE attrelid = 'mytab'::regclass AND attnum > 0;