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