1-- 2-- CREATE_TYPE 3-- 4-- 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. 8-- 9CREATE TYPE widget ( 10 internallength = 24, 11 input = widget_in, 12 output = widget_out, 13 typmod_in = numerictypmodin, 14 typmod_out = numerictypmodout, 15 alignment = double 16); 17CREATE TYPE city_budget ( 18 internallength = 16, 19 input = int44in, 20 output = int44out, 21 element = int4, 22 category = 'x', -- just to verify the system will take it 23 preferred = true -- ditto 24); 25-- Test creation and destruction of shell types 26CREATE TYPE shell; 27CREATE TYPE shell; -- fail, type already present 28ERROR: type "shell" already exists 29DROP TYPE shell; 30DROP TYPE shell; -- fail, type not exist 31ERROR: type "shell" does not exist 32-- also, let's leave one around for purposes of pg_dump testing 33CREATE TYPE myshell; 34-- 35-- Test type-related default values (broken in releases before PG 7.2) 36-- 37-- This part of the test also exercises the "new style" approach of making 38-- a shell type and then filling it in. 39-- 40CREATE TYPE int42; 41CREATE TYPE text_w_default; 42-- Make dummy I/O routines using the existing internal support for int4, text 43CREATE FUNCTION int42_in(cstring) 44 RETURNS int42 45 AS 'int4in' 46 LANGUAGE internal STRICT IMMUTABLE; 47NOTICE: return type int42 is only a shell 48CREATE FUNCTION int42_out(int42) 49 RETURNS cstring 50 AS 'int4out' 51 LANGUAGE internal STRICT IMMUTABLE; 52NOTICE: argument type int42 is only a shell 53CREATE FUNCTION text_w_default_in(cstring) 54 RETURNS text_w_default 55 AS 'textin' 56 LANGUAGE internal STRICT IMMUTABLE; 57NOTICE: return type text_w_default is only a shell 58CREATE FUNCTION text_w_default_out(text_w_default) 59 RETURNS cstring 60 AS 'textout' 61 LANGUAGE internal STRICT IMMUTABLE; 62NOTICE: argument type text_w_default is only a shell 63CREATE TYPE int42 ( 64 internallength = 4, 65 input = int42_in, 66 output = int42_out, 67 alignment = int4, 68 default = 42, 69 passedbyvalue 70); 71CREATE TYPE text_w_default ( 72 internallength = variable, 73 input = text_w_default_in, 74 output = text_w_default_out, 75 alignment = int4, 76 default = 'zippo' 77); 78CREATE TABLE default_test (f1 text_w_default, f2 int42); 79INSERT INTO default_test DEFAULT VALUES; 80SELECT * FROM default_test; 81 f1 | f2 82-------+---- 83 zippo | 42 84(1 row) 85 86-- Test stand-alone composite type 87CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); 88CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' 89 SELECT * FROM default_test; 90' LANGUAGE SQL; 91SELECT * FROM get_default_test(); 92 f1 | f2 93-------+---- 94 zippo | 42 95(1 row) 96 97-- Test comments 98COMMENT ON TYPE bad IS 'bad comment'; 99ERROR: type "bad" does not exist 100COMMENT ON TYPE default_test_row IS 'good comment'; 101COMMENT ON TYPE default_test_row IS NULL; 102COMMENT ON COLUMN default_test_row.nope IS 'bad comment'; 103ERROR: column "nope" of relation "default_test_row" does not exist 104COMMENT ON COLUMN default_test_row.f1 IS 'good comment'; 105COMMENT ON COLUMN default_test_row.f1 IS NULL; 106-- Check shell type create for existing types 107CREATE TYPE text_w_default; -- should fail 108ERROR: type "text_w_default" already exists 109DROP TYPE default_test_row CASCADE; 110NOTICE: drop cascades to function get_default_test() 111DROP TABLE default_test; 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); 117ERROR: function array_out(not_existing_type) does not exist 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); 124WARNING: changing argument type of function base_fn_out from "opaque" to base_type 125WARNING: changing return type of function base_fn_in from opaque to base_type 126WARNING: changing return type of function base_fn_out from opaque to cstring 127DROP FUNCTION base_fn_in(cstring); -- error 128ERROR: cannot drop function base_fn_in(cstring) because other objects depend on it 129DETAIL: type base_type depends on function base_fn_in(cstring) 130function base_fn_out(base_type) depends on type base_type 131HINT: Use DROP ... CASCADE to drop the dependent objects too. 132DROP FUNCTION base_fn_out(opaque); -- error 133ERROR: function base_fn_out(opaque) does not exist 134DROP TYPE base_type; -- error 135ERROR: cannot drop type base_type because other objects depend on it 136DETAIL: function base_fn_out(base_type) depends on type base_type 137function base_fn_in(cstring) depends on type base_type 138HINT: Use DROP ... CASCADE to drop the dependent objects too. 139DROP TYPE base_type CASCADE; 140NOTICE: drop cascades to 2 other objects 141DETAIL: drop cascades to function base_fn_out(base_type) 142drop 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) 145CREATE TEMP TABLE mytab (foo widget(42,13,7)); -- should fail 146ERROR: invalid NUMERIC type modifier 147LINE 1: CREATE TEMP TABLE mytab (foo widget(42,13,7)); 148 ^ 149CREATE TEMP TABLE mytab (foo widget(42,13)); 150SELECT format_type(atttypid,atttypmod) FROM pg_attribute 151WHERE attrelid = 'mytab'::regclass AND attnum > 0; 152 format_type 153--------------- 154 widget(42,13) 155(1 row) 156 157