Hello,
can anyone explain why the domain check is not generating an exception
when used within PL/pgSQL ‘RETURN QUERY’ statement?
See tf2() and tf3() above - in particular, the INSERT statement in line
100 and 163:
--8<---------------cut here---------------start------------->8---
1
2 -- x, y greater equal 0
3 -- y lower equal than x.
4 CREATE TYPE _t AS (
5 x double precision,
6 y double precision
7 );
8 CREATE DOMAIN t AS _t
9 CHECK (
10 (VALUE).x >= 0 AND
11 (VALUE).y >= 0 AND
12 (VALUE).y <= (VALUE).x
13 );
14
15 CREATE TABLE test (v t);
16
17 -- --------------------------------- Tests:
18
19 -- works as expected
20 insert into test values ('(1,1)'::t), ('(3, 2)'::t);
21 -- works as expected
22 select '(1,1)'::t;
23 select '(4,2)'::t;
24
25 -- works as expected / throws exception: t_check
26 select '(4,5)'::t;
27 /*
28 ,,* x=# SELECT '(4,5)'::t;
29 ERROR: 23514: value for domain t violates check constraint "t_check"
30 SCHEMA NAME: public
31 DATATYPE NAME: t
32 CONSTRAINT NAME: t_check
33 LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3670
34 ,,*/
35
36 -- works as expected: call tf1() fails with constraint error
37 CREATE OR REPLACE FUNCTION tf1() RETURNS t
38 AS $$
39 DECLARE
40 BEGIN
41 return '(4,5)'::t;
42 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
43
44
45 -- works not as expected:
46 CREATE OR REPLACE FUNCTION tf2() RETURNS SETOF t
47 AS $$
48 DECLARE
49 BEGIN
50 RETURN QUERY select 4::double precision, 5::double precision;
51 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
52 /*
53 *** *x=# SELECT tf2();
54 *** +-------+
55 *** | tf2 |
56 *** +-------+
57 *** | (4,5) |
58 *** +-------+
59 *** (1 row)
60 ***
61 *** Time: 0.821 ms
62 *** *x=# SELECT '(4,5)'::t;
63 *** ERROR: 23514: value for domain t violates check constraint "t_check"
64 *** SCHEMA NAME: public
65 *** DATATYPE NAME: t
66 *** CONSTRAINT NAME: t_check
67 *** LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3639
68 *** Time: 0.521 ms
69 *** *x=# SELECT tf2()::t;
70 *** +-------+
71 *** | tf2 |
72 *** +-------+
73 *** | (4,5) |
74 *** +-------+
75 ***
76 *** (1 row)
77 ***
78 *** Time: 0.546 ms
79 *** *x=# SELECT pg_typeof(tf2());
80 *** +-----------+
81 *** | pg_typeof |
82 *** +-----------+
83 *** | t |
84 *** +-----------+
85 *** (1 row)
86 ***
87 *** *x=# insert into test values ('(1,1)'::t), ('(3, 2)'::t);
88 *** INSERT 0 2
89 *** Time: 0.897 ms
90 *** *x=#
91 *** *x=# SELECT tf2();
92 *** +-------+
93 *** | tf2 |
94 *** +-------+
95 *** | (4,5) |
96 *** +-------+
97 *** (1 row)
98 ***
99 *** Time: 0.532 ms
100 *** *x=# INSERT into test VALUES ((select tf2())) returning *;
101 *** +-------+
102 *** | v |
103 *** +-------+
104 *** | (4,5) |
105 *** +-------+
106 *** (1 row)
107 ***
108 *** INSERT 0 1
109 *** Time: 0.759 ms
110 *** *x=# SELECT v::t from test;
111 *** +-------+
112 *** | v |
113 *** +-------+
114 *** | (1,1) |
115 *** | (3,2) |
116 *** | (4,5) |
117 *** +-------+
118 *** (3 rows)
119 ***
120 *** Time: 0.559 ms
121 ***
122 *** *x=# SELECT '(4,5)'::t;
123 *** ERROR: 23514: value for domain t violates check constraint "t_check"
124 *** SCHEMA NAME: public
125 *** DATATYPE NAME: t
126 *** CONSTRAINT NAME: t_check
127 *** LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3639
128 *** Time: 0.634 ms
129 ***
130 */
131
132 -- not expected, same example with REURN QUERY EXECUTE:
133 CREATE OR REPLACE FUNCTION tf3() RETURNS SETOF t
134 AS $$
135 DECLARE
136 BEGIN
137 RETURN QUERY EXECUTE
138 format(
139 $sql$
140 select 4::double precision,5::double precision
141 $sql$);
142 END; $$ LANGUAGE plpgsql SECURITY DEFINER;
143
144 /*
145 *** *x=# SELECT tf3();
146 *** +-------+
147 *** | tf3 |
148 *** +-------+
149 *** | (4,5) |
150 *** +-------+
151 *** (1 row)
152 ***
153 *** Time: 0.924 ms
154 *** *x=# SELECT tf3()::t;
155 *** +-------+
156 *** | tf3 |
157 *** +-------+
158 *** | (4,5) |
159 *** +-------+
160 *** (1 row)
161 ***
162 *** Time: 0.538 ms
163 *** *x=# insert into test ((select tf3()));
164 *** INSERT 0 1
165 *** Time: 0.840 ms
166 *** *x=# SELECT v::t from test;
167 *** +-------+
168 *** | v |
169 *** +-------+
170 *** | (1,1) |
171 *** | (3,2) |
172 *** | (4,5) |
173 *** | (4,5) |
174 *** +-------+
175 *** (4 rows)
176 */
--8<---------------cut here---------------end--------------->8---
Is that on purpose?
Have I missed something in the documentation?
--
Christian Barthel
