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