Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYon5-002oJb-1l for pgsql-general@arkaria.postgresql.org; Wed, 23 Aug 2023 14:24:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qYon3-00DROC-OX for pgsql-general@arkaria.postgresql.org; Wed, 23 Aug 2023 14:24:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYon3-00DRO0-Cb for pgsql-general@lists.postgresql.org; Wed, 23 Aug 2023 14:24:57 +0000 Received: from mout.kundenserver.de ([212.227.17.13]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYomv-000XNi-V2 for pgsql-general@lists.postgresql.org; Wed, 23 Aug 2023 14:24:55 +0000 Received: from shell.onfire.org ([217.196.148.42]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.183]) with ESMTPSA (Nemesis) id 1MowSu-1pvvWb0Uog-00qQMy for ; Wed, 23 Aug 2023 16:24:47 +0200 From: Christian Barthel To: pgsql-general@lists.postgresql.org Subject: PL/pgSQL RETURN QUERY and DOMAIN CHECKs X-Gpg-Key: https://onfire.org/bch/pgp/pubkey.asc X-Archive: expiry=7 Date: Wed, 23 Aug 2023 16:24:46 +0200 Message-ID: <87y1i1j0pd.fsf@shell.onfire.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.2 (berkeley-unix) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:eB9A2XqfOBZqYQ5yxQ6c9ZfJarzuNX99XxP1WJtG9sqNlHW5BK0 9z3qnmDySweZrFJVNHcztjdqXb2HJ3LLHaiIQ4YEfG3d3c9tGSIEpt6db6d4WdWomMOQQZv 2x2u70EQ/ctVT2d36tP2zcqIsBnRxko2snjyuMqrzZY8zIXkdbrhnBy4ZAYnrc/pQtzmVQx I4qlcJF6LWbFQwCoU/xcA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:lU/USYY1dlI=;6ccK/tMCBQxH0qAYNriEvcyA5hs 8ugWduhMO8C0uS/WWHRfSJ2wNZbQP+T10rUlqBHpV30Mj0NSPr3uBYeGXtvK87IKwEl2tVorN r0Cwevy2dHyxzncNgHRZTl/lYjCHHSuwqLOCCqZ+9xkJjjB8M06qJnz+heplThsuVK7r6Yb3O Yf50qODyCxDznLKGz0b1ZnYk+EMzoVwIVZv4BrrJs/ceBGcuwLJYkzt/w4PGL48yfqpMwmx1g 0kAfb5+TzEnHAQsUlWJbwE0E5u49VoI9+2Pf6+CYsXGuE0Ttvfmmfvr6Fq0t6qoWZKm6C8nUC j2ZAztlquZgsC6ldVTBh/tDhcOZSEh7FAkkRklpNjpe9yDJaQrYt5oKHjUPp7XxorxIDc4v+x T/NQXpMtFplVQ+HWwzM7ym3UuhiimzyDSMkn6kbKkYkJ0mMjPF2T/TyJtSQVOwfsnndwAAlCA pDLtdQv7QOwGEsFflFV7JL9Uh+WFsVd5ofPSRIhEzIcP+SNgGJFCOy0gpUMPafvoASnQS1h51 Y4hYWD7nWmuEaU5Yz0j/tCresOuJQHwqfi3SULwhc93JnWBDyp4265p6Jt3U/xFLRN+yhf7o+ Ky4NfqajkYBb5HUhr/jcwUifd9gV9ZWn6Ak7USh1uFh6UQroTMreBI5yyTpdHttRigWDTRIeM OBHbNzdaTIlcAe94LfjEHqqgjzGnR3EiBwY9ktqtyNCskK12d2Wgvmw/IN9OO6H7XFqHfQ802 ov5xdxk+vBqnB+xUdW+MCOd4sSUGAiM8O1hooZlr7HCekE76DpBsj0jaxsfhCJ650/aYeakT2 EFrra7+xn/yjGbJyWbAuRgByx8Xfl3Hzju4UiylxOv8qsj0wZCO9MYv61v3YX6d9hx61xJsf6 WNk6SStlbcNN8hg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello, can anyone explain why the domain check is not generating an exception when used within PL/pgSQL =E2=80=98RETURN QUERY=E2=80=99 statement? See tf2() and tf3() above - in particular, the INSERT statement in line 100 and 163: --8<---------------cut here---------------start------------->8--- 1=20 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 >=3D 0 AND 11 (VALUE).y >=3D 0 AND 12 (VALUE).y <=3D (VALUE).x 13 ); 14=20 15 CREATE TABLE test (v t); 16=20 17 -- --------------------------------- Tests: 18=20 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=20 25 -- works as expected / throws exception: t_check 26 select '(4,5)'::t; 27 /* 28 ,,* x=3D# 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=20 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=20 44=20 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=3D# SELECT tf2(); 54 *** +-------+ 55 *** | tf2 | 56 *** +-------+ 57 *** | (4,5) | 58 *** +-------+ 59 *** (1 row) 60 ***=20 61 *** Time: 0.821 ms 62 *** *x=3D# SELECT '(4,5)'::t; 63 *** ERROR: 23514: value for domain t violates check constraint "t_che= ck" 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=3D# SELECT tf2()::t; 70 *** +-------+ 71 *** | tf2 | 72 *** +-------+ 73 *** | (4,5) | 74 *** +-------+ 75 ***=20 76 *** (1 row) 77 ***=20 78 *** Time: 0.546 ms 79 *** *x=3D# SELECT pg_typeof(tf2()); 80 *** +-----------+ 81 *** | pg_typeof | 82 *** +-----------+ 83 *** | t | 84 *** +-----------+ 85 *** (1 row) 86 ***=20 87 *** *x=3D# insert into test values ('(1,1)'::t), ('(3, 2)'::t); 88 *** INSERT 0 2 89 *** Time: 0.897 ms 90 *** *x=3D#=20 91 *** *x=3D# SELECT tf2(); 92 *** +-------+ 93 *** | tf2 | 94 *** +-------+ 95 *** | (4,5) | 96 *** +-------+ 97 *** (1 row) 98 ***=20 99 *** Time: 0.532 ms 100 *** *x=3D# INSERT into test VALUES ((select tf2())) returning *; 101 *** +-------+ 102 *** | v | 103 *** +-------+ 104 *** | (4,5) | 105 *** +-------+ 106 *** (1 row) 107 ***=20 108 *** INSERT 0 1 109 *** Time: 0.759 ms 110 *** *x=3D# SELECT v::t from test; 111 *** +-------+ 112 *** | v | 113 *** +-------+ 114 *** | (1,1) | 115 *** | (3,2) | 116 *** | (4,5) | 117 *** +-------+ 118 *** (3 rows) 119 ***=20 120 *** Time: 0.559 ms 121 ***=20 122 *** *x=3D# SELECT '(4,5)'::t; 123 *** ERROR: 23514: value for domain t violates check constraint "t_che= ck" 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 ***=20 130 */ 131=20 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=20 144 /* 145 *** *x=3D# SELECT tf3(); 146 *** +-------+ 147 *** | tf3 | 148 *** +-------+ 149 *** | (4,5) | 150 *** +-------+ 151 *** (1 row) 152 ***=20 153 *** Time: 0.924 ms 154 *** *x=3D# SELECT tf3()::t; 155 *** +-------+ 156 *** | tf3 | 157 *** +-------+ 158 *** | (4,5) | 159 *** +-------+ 160 *** (1 row) 161 ***=20 162 *** Time: 0.538 ms 163 *** *x=3D# insert into test ((select tf3())); 164 *** INSERT 0 1 165 *** Time: 0.840 ms 166 *** *x=3D# 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? --=20 Christian Barthel