Thread: PL/pgSQL RETURN QUERY and DOMAIN CHECKs

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