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