path: root/src/test/regress/expected/create_type.out

--
-- CREATE_TYPE
--
-- directory path and dlsuffix are passed to us in environment variables
\getenv libdir PG_LIBDIR
\getenv dlsuffix PG_DLSUFFIX
\set regresslib :libdir '/regress' :dlsuffix
--
-- Test the "old style" approach of making the I/O functions first,
-- with no explicit shell type creation.
--
CREATE FUNCTION widget_in(cstring)
   RETURNS widget
   AS :'regresslib'
   LANGUAGE C STRICT IMMUTABLE;
NOTICE:  type "widget" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION widget_out(widget)
   RETURNS cstring
   AS :'regresslib'
   LANGUAGE C STRICT IMMUTABLE;
NOTICE:  argument type widget is only a shell
LINE 1: CREATE FUNCTION widget_out(widget)
                                   ^
CREATE FUNCTION int44in(cstring)
   RETURNS city_budget
   AS :'regresslib'
   LANGUAGE C STRICT IMMUTABLE;
NOTICE:  type "city_budget" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION int44out(city_budget)
   RETURNS cstring
   AS :'regresslib'
   LANGUAGE C STRICT IMMUTABLE;
NOTICE:  argument type city_budget is only a shell
LINE 1: CREATE FUNCTION int44out(city_budget)
                                 ^
CREATE TYPE widget (
   internallength = 24,
   input = widget_in,
   output = widget_out,
   typmod_in = numerictypmodin,
   typmod_out = numerictypmodout,
   alignment = double
);
CREATE TYPE city_budget (
   internallength = 16,
   input = int44in,
   output = int44out,
   element = int4,
   category = 'x',   -- just to verify the system will take it
   preferred = true  -- ditto
);
-- Test creation and destruction of shell types
CREATE TYPE shell;
CREATE TYPE shell;   -- fail, type already present
ERROR:  type "shell" already exists
DROP TYPE shell;
DROP TYPE shell;     -- fail, type not exist
ERROR:  type "shell" does not exist
-- also, let's leave one around for purposes of pg_dump testing
CREATE TYPE myshell;
--
-- Test type-related default values (broken in releases before PG 7.2)
--
-- This part of the test also exercises the "new style" approach of making
-- a shell type and then filling it in.
--
CREATE TYPE int42;
CREATE TYPE text_w_default;
-- Make dummy I/O routines using the existing internal support for int4, text
CREATE FUNCTION int42_in(cstring)
   RETURNS int42
   AS 'int4in'
   LANGUAGE internal STRICT IMMUTABLE;
NOTICE:  return type int42 is only a shell
CREATE FUNCTION int42_out(int42)
   RETURNS cstring
   AS 'int4out'
   LANGUAGE internal STRICT IMMUTABLE;
NOTICE:  argument type int42 is only a shell
LINE 1: CREATE FUNCTION int42_out(int42)
                                  ^
CREATE FUNCTION text_w_default_in(cstring)
   RETURNS text_w_default
   AS 'textin'
   LANGUAGE internal STRICT IMMUTABLE;
NOTICE:  return type text_w_default is only a shell
CREATE FUNCTION text_w_default_out(text_w_default)
   RETURNS cstring
   AS 'textout'
   LANGUAGE internal STRICT IMMUTABLE;
NOTICE:  argument type text_w_default is only a shell
LINE 1: CREATE FUNCTION text_w_default_out(text_w_default)
                                           ^
CREATE TYPE int42 (
   internallength = 4,
   input = int42_in,
   output = int42_out,
   alignment = int4,
   default = 42,
   passedbyvalue
);
CREATE TYPE text_w_default (
   internallength = variable,
   input = text_w_default_in,
   output = text_w_default_out,
   alignment = int4,
   default = 'zippo'
);
CREATE TABLE default_test (f1 text_w_default, f2 int42);
INSERT INTO default_test DEFAULT VALUES;
SELECT * FROM default_test;
  f1   | f2 
-------+----
 zippo | 42
(1 row)

-- We need a shell type to test some CREATE TYPE failure cases with
CREATE TYPE bogus_type;
-- invalid: non-lowercase quoted identifiers
CREATE TYPE bogus_type (
	"Internallength" = 4,
	"Input" = int42_in,
	"Output" = int42_out,
	"Alignment" = int4,
	"Default" = 42,
	"Passedbyvalue"
);
WARNING:  type attribute "Internallength" not recognized
LINE 2:  "Internallength" = 4,
         ^
WARNING:  type attribute "Input" not recognized
LINE 3:  "Input" = int42_in,
         ^
WARNING:  type attribute "Output" not recognized
LINE 4:  "Output" = int42_out,
         ^
WARNING:  type attribute "Alignment" not recognized
LINE 5:  "Alignment" = int4,
         ^
WARNING:  type attribute "Default" not recognized
LINE 6:  "Default" = 42,
         ^
WARNING:  type attribute "Passedbyvalue" not recognized
LINE 7:  "Passedbyvalue"
         ^
ERROR:  type input function must be specified
-- invalid: input/output function incompatibility
CREATE TYPE bogus_type (INPUT = array_in,
    OUTPUT = array_out,
    ELEMENT = int,
    INTERNALLENGTH = 32);
ERROR:  type input function array_in must return type bogus_type
DROP TYPE bogus_type;
-- It no longer is possible to issue CREATE TYPE without making a shell first
CREATE TYPE bogus_type (INPUT = array_in,
    OUTPUT = array_out,
    ELEMENT = int,
    INTERNALLENGTH = 32);
ERROR:  type "bogus_type" does not exist
HINT:  Create the type as a shell type, then create its I/O functions, then do a full CREATE TYPE.
-- Test stand-alone composite type
CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42);
CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS '
  SELECT * FROM default_test;
' LANGUAGE SQL;
SELECT * FROM get_default_test();
  f1   | f2 
-------+----
 zippo | 42
(1 row)

-- Test comments
COMMENT ON TYPE bad IS 'bad comment';
ERROR:  type "bad" does not exist
COMMENT ON TYPE default_test_row IS 'good comment';
COMMENT ON TYPE default_test_row IS NULL;
COMMENT ON COLUMN default_test_row.nope IS 'bad comment';
ERROR:  column "nope" of relation "default_test_row" does not exist
COMMENT ON COLUMN default_test_row.f1 IS 'good comment';
COMMENT ON COLUMN default_test_row.f1 IS NULL;
-- Check shell type create for existing types
CREATE TYPE text_w_default;		-- should fail
ERROR:  type "text_w_default" already exists
DROP TYPE default_test_row CASCADE;
NOTICE:  drop cascades to function get_default_test()
DROP TABLE default_test;
-- Check dependencies are established when creating a new type
CREATE TYPE base_type;
CREATE FUNCTION base_fn_in(cstring) RETURNS base_type AS 'boolin'
    LANGUAGE internal IMMUTABLE STRICT;
NOTICE:  return type base_type is only a shell
CREATE FUNCTION base_fn_out(base_type) RETURNS cstring AS 'boolout'
    LANGUAGE internal IMMUTABLE STRICT;
NOTICE:  argument type base_type is only a shell
LINE 1: CREATE FUNCTION base_fn_out(base_type) RETURNS cstring AS 'b...
                                    ^
CREATE TYPE base_type(INPUT = base_fn_in, OUTPUT = base_fn_out);
DROP FUNCTION base_fn_in(cstring); -- error
ERROR:  cannot drop function base_fn_in(cstring) because other objects depend on it
DETAIL:  type base_type depends on function base_fn_in(cstring)
function base_fn_out(base_type) depends on type base_type
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP FUNCTION base_fn_out(base_type); -- error
ERROR:  cannot drop function base_fn_out(base_type) because other objects depend on it
DETAIL:  type base_type depends on function base_fn_out(base_type)
function base_fn_in(cstring) depends on type base_type
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE base_type; -- error
ERROR:  cannot drop type base_type because other objects depend on it
DETAIL:  function base_fn_in(cstring) depends on type base_type
function base_fn_out(base_type) depends on type base_type
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE base_type CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function base_fn_in(cstring)
drop cascades to function base_fn_out(base_type)
-- Check usage of typmod with a user-defined type
-- (we have borrowed numeric's typmod functions)
CREATE TEMP TABLE mytab (foo widget(42,13,7));     -- should fail
ERROR:  invalid NUMERIC type modifier
LINE 1: CREATE TEMP TABLE mytab (foo widget(42,13,7));
                                     ^
CREATE TEMP TABLE mytab (foo widget(42,13));
SELECT format_type(atttypid,atttypmod) FROM pg_attribute
WHERE attrelid = 'mytab'::regclass AND attnum > 0;
  format_type  
---------------
 widget(42,13)
(1 row)

-- might as well exercise the widget type while we're here
INSERT INTO mytab VALUES ('(1,2,3)'), ('(-44,5.5,12)');
TABLE mytab;
     foo      
--------------
 (1,2,3)
 (-44,5.5,12)
(2 rows)

-- and test format_type() a bit more, too
select format_type('varchar'::regtype, 42);
      format_type      
-----------------------
 character varying(38)
(1 row)

select format_type('bpchar'::regtype, null);
 format_type 
-------------
 character
(1 row)

-- this behavior difference is intentional
select format_type('bpchar'::regtype, -1);
 format_type 
-------------
 bpchar
(1 row)

-- Test non-error-throwing APIs using widget, which still throws errors
SELECT pg_input_is_valid('(1,2,3)', 'widget');
 pg_input_is_valid 
-------------------
 t
(1 row)

SELECT pg_input_is_valid('(1,2)', 'widget');  -- hard error expected
ERROR:  invalid input syntax for type widget: "(1,2)"
SELECT pg_input_is_valid('{"(1,2,3)"}', 'widget[]');
 pg_input_is_valid 
-------------------
 t
(1 row)

SELECT pg_input_is_valid('{"(1,2)"}', 'widget[]');  -- hard error expected
ERROR:  invalid input syntax for type widget: "(1,2)"
SELECT pg_input_is_valid('("(1,2,3)")', 'mytab');
 pg_input_is_valid 
-------------------
 t
(1 row)

SELECT pg_input_is_valid('("(1,2)")', 'mytab');  -- hard error expected
ERROR:  invalid input syntax for type widget: "(1,2)"
-- Test creation of an operator over a user-defined type
CREATE FUNCTION pt_in_widget(point, widget)
   RETURNS bool
   AS :'regresslib'
   LANGUAGE C STRICT;
CREATE OPERATOR <% (
   leftarg = point,
   rightarg = widget,
   procedure = pt_in_widget,
   commutator = >% ,
   negator = >=%
);
SELECT point '(1,2)' <% widget '(0,0,3)' AS t,
       point '(1,2)' <% widget '(0,0,1)' AS f;
 t | f 
---+---
 t | f
(1 row)

-- exercise city_budget type
CREATE TABLE city (
	name		name,
	location 	box,
	budget 		city_budget
);
INSERT INTO city VALUES
('Podunk', '(1,2),(3,4)', '100,127,1000'),
('Gotham', '(1000,34),(1100,334)', '123456,127,-1000,6789');
TABLE city;
  name  |       location       |        budget         
--------+----------------------+-----------------------
 Podunk | (3,4),(1,2)          | 100,127,1000,0
 Gotham | (1100,334),(1000,34) | 123456,127,-1000,6789
(2 rows)

--
-- Test CREATE/ALTER TYPE using a type that's compatible with varchar,
-- so we can re-use those support functions
--
CREATE TYPE myvarchar;
CREATE FUNCTION myvarcharin(cstring, oid, integer) RETURNS myvarchar
LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'varcharin';
NOTICE:  return type myvarchar is only a shell
CREATE FUNCTION myvarcharout(myvarchar) RETURNS cstring
LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'varcharout';
NOTICE:  argument type myvarchar is only a shell
LINE 1: CREATE FUNCTION myvarcharout(myvarchar) RETURNS cstring
                                     ^
CREATE FUNCTION myvarcharsend(myvarchar) RETURNS bytea
LANGUAGE internal STABLE PARALLEL SAFE STRICT AS 'varcharsend';
NOTICE:  argument type myvarchar is only a shell
LINE 1: CREATE FUNCTION myvarcharsend(myvarchar) RETURNS bytea
                                      ^
CREATE FUNCTION myvarcharrecv(internal, oid, integer) RETURNS myvarchar
LANGUAGE internal STABLE PARALLEL SAFE STRICT AS 'varcharrecv';
NOTICE:  return type myvarchar is only a shell
-- fail, it's still a shell:
ALTER TYPE myvarchar SET (storage = extended);
ERROR:  type "myvarchar" is only a shell
CREATE TYPE myvarchar (
    input = myvarcharin,
    output = myvarcharout,
    alignment = integer,
    storage = main
);
-- want to check updating of a domain over the target type, too
CREATE DOMAIN myvarchardom AS myvarchar;
ALTER TYPE myvarchar SET (storage = plain);  -- not allowed
ERROR:  cannot change type's storage to PLAIN
ALTER TYPE myvarchar SET (storage = extended);
ALTER TYPE myvarchar SET (
    send = myvarcharsend,
    receive = myvarcharrecv,
    typmod_in = varchartypmodin,
    typmod_out = varchartypmodout,
    -- these are bogus, but it's safe as long as we don't use the type:
    analyze = ts_typanalyze,
    subscript = raw_array_subscript_handler
);
SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
       typanalyze, typsubscript, typstorage
FROM pg_type WHERE typname = 'myvarchar';
  typinput   |  typoutput   |  typreceive   |    typsend    |    typmodin     |    typmodout     |  typanalyze   |        typsubscript         | typstorage 
-------------+--------------+---------------+---------------+-----------------+------------------+---------------+-----------------------------+------------
 myvarcharin | myvarcharout | myvarcharrecv | myvarcharsend | varchartypmodin | varchartypmodout | ts_typanalyze | raw_array_subscript_handler | x
(1 row)

SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
       typanalyze, typsubscript, typstorage
FROM pg_type WHERE typname = '_myvarchar';
 typinput | typoutput | typreceive |  typsend   |    typmodin     |    typmodout     |    typanalyze    |      typsubscript       | typstorage 
----------+-----------+------------+------------+-----------------+------------------+------------------+-------------------------+------------
 array_in | array_out | array_recv | array_send | varchartypmodin | varchartypmodout | array_typanalyze | array_subscript_handler | x
(1 row)

SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
       typanalyze, typsubscript, typstorage
FROM pg_type WHERE typname = 'myvarchardom';
 typinput  |  typoutput   | typreceive  |    typsend    | typmodin | typmodout |  typanalyze   | typsubscript | typstorage 
-----------+--------------+-------------+---------------+----------+-----------+---------------+--------------+------------
 domain_in | myvarcharout | domain_recv | myvarcharsend | -        | -         | ts_typanalyze | -            | x
(1 row)

SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
       typanalyze, typsubscript, typstorage
FROM pg_type WHERE typname = '_myvarchardom';
 typinput | typoutput | typreceive |  typsend   | typmodin | typmodout |    typanalyze    |      typsubscript       | typstorage 
----------+-----------+------------+------------+----------+-----------+------------------+-------------------------+------------
 array_in | array_out | array_recv | array_send | -        | -         | array_typanalyze | array_subscript_handler | x
(1 row)

-- ensure dependencies are straight
DROP FUNCTION myvarcharsend(myvarchar);  -- fail
ERROR:  cannot drop function myvarcharsend(myvarchar) because other objects depend on it
DETAIL:  type myvarchar depends on function myvarcharsend(myvarchar)
function myvarcharin(cstring,oid,integer) depends on type myvarchar
function myvarcharout(myvarchar) depends on type myvarchar
function myvarcharrecv(internal,oid,integer) depends on type myvarchar
type myvarchardom depends on function myvarcharsend(myvarchar)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE myvarchar;  -- fail
ERROR:  cannot drop type myvarchar because other objects depend on it
DETAIL:  function myvarcharin(cstring,oid,integer) depends on type myvarchar
function myvarcharout(myvarchar) depends on type myvarchar
function myvarcharsend(myvarchar) depends on type myvarchar
function myvarcharrecv(internal,oid,integer) depends on type myvarchar
type myvarchardom depends on type myvarchar
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP TYPE myvarchar CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to function myvarcharin(cstring,oid,integer)
drop cascades to function myvarcharout(myvarchar)
drop cascades to function myvarcharsend(myvarchar)
drop cascades to function myvarcharrecv(internal,oid,integer)
drop cascades to type myvarchardom