1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
--
-- CREATE_CAST
--
-- Create some types to test with
CREATE TYPE casttesttype;
CREATE FUNCTION casttesttype_in(cstring)
RETURNS casttesttype
AS 'textin'
LANGUAGE internal STRICT IMMUTABLE;
NOTICE: return type casttesttype is only a shell
CREATE FUNCTION casttesttype_out(casttesttype)
RETURNS cstring
AS 'textout'
LANGUAGE internal STRICT IMMUTABLE;
NOTICE: argument type casttesttype is only a shell
CREATE TYPE casttesttype (
internallength = variable,
input = casttesttype_in,
output = casttesttype_out,
alignment = int4
);
-- a dummy function to test with
CREATE FUNCTION casttestfunc(casttesttype) RETURNS int4 LANGUAGE SQL AS
$$ SELECT 1; $$;
SELECT casttestfunc('foo'::text); -- fails, as there's no cast
ERROR: function casttestfunc(text) does not exist
LINE 1: SELECT casttestfunc('foo'::text);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- Try binary coercion cast
CREATE CAST (text AS casttesttype) WITHOUT FUNCTION;
SELECT casttestfunc('foo'::text); -- doesn't work, as the cast is explicit
ERROR: function casttestfunc(text) does not exist
LINE 1: SELECT casttestfunc('foo'::text);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT casttestfunc('foo'::text::casttesttype); -- should work
casttestfunc
--------------
1
(1 row)
DROP CAST (text AS casttesttype); -- cleanup
-- Try IMPLICIT binary coercion cast
CREATE CAST (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT;
SELECT casttestfunc('foo'::text); -- Should work now
casttestfunc
--------------
1
(1 row)
-- Try I/O conversion cast.
SELECT 1234::int4::casttesttype; -- No cast yet, should fail
ERROR: cannot cast type integer to casttesttype
LINE 1: SELECT 1234::int4::casttesttype;
^
CREATE CAST (int4 AS casttesttype) WITH INOUT;
SELECT 1234::int4::casttesttype; -- Should work now
casttesttype
--------------
1234
(1 row)
DROP CAST (int4 AS casttesttype);
-- Try cast with a function
CREATE FUNCTION int4_casttesttype(int4) RETURNS casttesttype LANGUAGE SQL AS
$$ SELECT ('foo'::text || $1::text)::casttesttype; $$;
CREATE CAST (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT;
SELECT 1234::int4::casttesttype; -- Should work now
casttesttype
--------------
foo1234
(1 row)
DROP FUNCTION int4_casttesttype(int4) CASCADE;
NOTICE: drop cascades to cast from integer to casttesttype
-- Try it with a function that requires an implicit cast
CREATE FUNCTION bar_int4_text(int4) RETURNS text LANGUAGE SQL AS
$$ SELECT ('bar'::text || $1::text); $$;
CREATE CAST (int4 AS casttesttype) WITH FUNCTION bar_int4_text(int4) AS IMPLICIT;
SELECT 1234::int4::casttesttype; -- Should work now
casttesttype
--------------
bar1234
(1 row)
-- check dependencies generated for that
SELECT pg_describe_object(classid, objid, objsubid) as obj,
pg_describe_object(refclassid, refobjid, refobjsubid) as objref,
deptype
FROM pg_depend
WHERE classid = 'pg_cast'::regclass AND
objid = (SELECT oid FROM pg_cast
WHERE castsource = 'int4'::regtype
AND casttarget = 'casttesttype'::regtype)
ORDER BY refclassid;
obj | objref | deptype
-----------------------------------+---------------------------------+---------
cast from integer to casttesttype | type casttesttype | n
cast from integer to casttesttype | function bar_int4_text(integer) | n
cast from integer to casttesttype | cast from text to casttesttype | n
(3 rows)
|
