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-- We need a shell type to test some CREATE TYPE failure cases with 88CREATE TYPE bogus_type; 89 90-- invalid: non-lowercase quoted identifiers 91CREATE TYPE bogus_type ( 92 "Internallength" = 4, 93 "Input" = int42_in, 94 "Output" = int42_out, 95 "Alignment" = int4, 96 "Default" = 42, 97 "Passedbyvalue" 98); 99 100-- invalid: input/output function incompatibility 101CREATE TYPE bogus_type (INPUT = array_in, 102 OUTPUT = array_out, 103 ELEMENT = int, 104 INTERNALLENGTH = 32); 105 106DROP TYPE bogus_type; 107 108-- It no longer is possible to issue CREATE TYPE without making a shell first 109CREATE TYPE bogus_type (INPUT = array_in, 110 OUTPUT = array_out, 111 ELEMENT = int, 112 INTERNALLENGTH = 32); 113 114-- Test stand-alone composite type 115 116CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); 117 118CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' 119 SELECT * FROM default_test; 120' LANGUAGE SQL; 121 122SELECT * FROM get_default_test(); 123 124-- Test comments 125COMMENT ON TYPE bad IS 'bad comment'; 126COMMENT ON TYPE default_test_row IS 'good comment'; 127COMMENT ON TYPE default_test_row IS NULL; 128COMMENT ON COLUMN default_test_row.nope IS 'bad comment'; 129COMMENT ON COLUMN default_test_row.f1 IS 'good comment'; 130COMMENT ON COLUMN default_test_row.f1 IS NULL; 131 132-- Check shell type create for existing types 133CREATE TYPE text_w_default; -- should fail 134 135DROP TYPE default_test_row CASCADE; 136 137DROP TABLE default_test; 138 139-- Check dependencies are established when creating a new type 140CREATE TYPE base_type; 141CREATE FUNCTION base_fn_in(cstring) RETURNS base_type AS 'boolin' 142 LANGUAGE internal IMMUTABLE STRICT; 143CREATE FUNCTION base_fn_out(base_type) RETURNS cstring AS 'boolout' 144 LANGUAGE internal IMMUTABLE STRICT; 145CREATE TYPE base_type(INPUT = base_fn_in, OUTPUT = base_fn_out); 146DROP FUNCTION base_fn_in(cstring); -- error 147DROP FUNCTION base_fn_out(base_type); -- error 148DROP TYPE base_type; -- error 149DROP TYPE base_type CASCADE; 150 151-- Check usage of typmod with a user-defined type 152-- (we have borrowed numeric's typmod functions) 153 154CREATE TEMP TABLE mytab (foo widget(42,13,7)); -- should fail 155CREATE TEMP TABLE mytab (foo widget(42,13)); 156 157SELECT format_type(atttypid,atttypmod) FROM pg_attribute 158WHERE attrelid = 'mytab'::regclass AND attnum > 0; 159 160-- might as well exercise the widget type while we're here 161INSERT INTO mytab VALUES ('(1,2,3)'), ('(-44,5.5,12)'); 162TABLE mytab; 163 164-- and test format_type() a bit more, too 165select format_type('varchar'::regtype, 42); 166select format_type('bpchar'::regtype, null); 167-- this behavior difference is intentional 168select format_type('bpchar'::regtype, -1); 169 170-- 171-- Test CREATE/ALTER TYPE using a type that's compatible with varchar, 172-- so we can re-use those support functions 173-- 174CREATE TYPE myvarchar; 175 176CREATE FUNCTION myvarcharin(cstring, oid, integer) RETURNS myvarchar 177LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'varcharin'; 178 179CREATE FUNCTION myvarcharout(myvarchar) RETURNS cstring 180LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'varcharout'; 181 182CREATE FUNCTION myvarcharsend(myvarchar) RETURNS bytea 183LANGUAGE internal STABLE PARALLEL SAFE STRICT AS 'varcharsend'; 184 185CREATE FUNCTION myvarcharrecv(internal, oid, integer) RETURNS myvarchar 186LANGUAGE internal STABLE PARALLEL SAFE STRICT AS 'varcharrecv'; 187 188-- fail, it's still a shell: 189ALTER TYPE myvarchar SET (storage = extended); 190 191CREATE TYPE myvarchar ( 192 input = myvarcharin, 193 output = myvarcharout, 194 alignment = integer, 195 storage = main 196); 197 198-- want to check updating of a domain over the target type, too 199CREATE DOMAIN myvarchardom AS myvarchar; 200 201ALTER TYPE myvarchar SET (storage = plain); -- not allowed 202 203ALTER TYPE myvarchar SET (storage = extended); 204 205ALTER TYPE myvarchar SET ( 206 send = myvarcharsend, 207 receive = myvarcharrecv, 208 typmod_in = varchartypmodin, 209 typmod_out = varchartypmodout, 210 -- these are bogus, but it's safe as long as we don't use the type: 211 analyze = ts_typanalyze, 212 subscript = raw_array_subscript_handler 213); 214 215SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout, 216 typanalyze, typsubscript, typstorage 217FROM pg_type WHERE typname = 'myvarchar'; 218 219SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout, 220 typanalyze, typsubscript, typstorage 221FROM pg_type WHERE typname = '_myvarchar'; 222 223SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout, 224 typanalyze, typsubscript, typstorage 225FROM pg_type WHERE typname = 'myvarchardom'; 226 227SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout, 228 typanalyze, typsubscript, typstorage 229FROM pg_type WHERE typname = '_myvarchardom'; 230 231-- ensure dependencies are straight 232DROP FUNCTION myvarcharsend(myvarchar); -- fail 233DROP TYPE myvarchar; -- fail 234 235DROP TYPE myvarchar CASCADE; 236