pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL RETURN QUERY and DOMAIN CHECKs
2+ messages / 2 participants
[nested] [flat]
* PL/pgSQL RETURN QUERY and DOMAIN CHECKs
@ 2023-08-23 14:24 Christian Barthel <bch@online.de>
0 siblings, 1 reply; 2+ messages in thread
From: Christian Barthel @ 2023-08-23 14:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PL/pgSQL RETURN QUERY and DOMAIN CHECKs
@ 2023-08-23 14:44 David G. Johnston <david.g.johnston@gmail.com>
parent: Christian Barthel <bch@online.de>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2023-08-23 14:44 UTC (permalink / raw)
To: Christian Barthel <bch@online.de>; +Cc: pgsql-general@lists.postgresql.org
On Wed, Aug 23, 2023, 07:25 Christian Barthel <bch@online.de> wrote:
> Hello,
>
> can anyone explain why the domain check is not generating an exception
> when used within PL/pgSQL ‘RETURN QUERY’ statement?
>
>
>
> 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 ***
>
The above (and the type definition...) is all that is relevant for the bug
report. Once you've gotten a value of some type nothing else in the system
that is simply passed that value is going to reevaluate the constraints.
David J.
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-08-23 14:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-23 14:24 PL/pgSQL RETURN QUERY and DOMAIN CHECKs Christian Barthel <bch@online.de>
2023-08-23 14:44 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox