MIME-Version: 1.0
In-Reply-To: 
 <CA+i5Jwa9E1RyApZYViBgG9yyCRT5V4WKRAeWDXuVe_1qf4uCsA@mail.gmail.com>
References: 
 <CA+i5Jwa9E1RyApZYViBgG9yyCRT5V4WKRAeWDXuVe_1qf4uCsA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 6 Jul 2023 14:54:46 -0700
Message-ID: 
 <CAKFQuwaaStSXsa0ZbBAxUujY02iUgCTJF=X3m0kCbPq=Lk+gLw@mail.gmail.com>
Subject: Re: How do Exception, do nothing and carry on
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000095916705ffd8900b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwaaStSXsa0ZbBAxUujY02iUgCTJF%3DX3m0kCbPq%3DLk%2BgLw%40mail.gmail.com>
Precedence: bulk

--00000000000095916705ffd8900b
Content-Type: text/plain; charset="UTF-8"

On Thursday, July 6, 2023, Shaozhong SHI <shishaozhong@gmail.com> wrote:

> The function in the following block code may fail.
>
> How to make it to carry on by ignoring exception?
>
> Something like Python's try catch.
>
> for i in (select distinct id from id_table)
>   loop
>          raise notice '%', i;
>            insert into my_table(id, last, arr, count) SELECT i, * from
> my_function(i) f(last int, arr int[], count int);
>            commit;
>
>   end loop;
>

Create a new function that traps [1] the failure and throws it away, then
call that function instead.

[1]
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

David J.

--00000000000095916705ffd8900b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<br><br>On Thursday, July 6, 2023, Shaozhong SHI &lt;<a href=3D"mailto:shis=
haozhong@gmail.com">shishaozhong@gmail.com</a>&gt; wrote:<br><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;p=
adding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div>The function in the=
 following block code may fail.</div><div><br></div><div>How to make it to =
carry on by ignoring exception?</div><div><br></div><div>Something like Pyt=
hon&#39;s try catch.</div><div><br></div><div>for i in (select distinct id =
from id_table)=C2=A0</div><div>=C2=A0 loop</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0raise notice &#39;%&#39;, i;</div><div><span style=3D"white-sp=
ace:normal"><span style=3D"white-space:pre-wrap">		</span></span></div><div=
>=C2=A0 =C2=A0<span style=3D"white-space:pre-wrap">	</span>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0insert into my_table(id, last, arr, count) SELECT i, * from my=
_function(i) f(last int, arr int[], count int);</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0commit;</div><div><br></div><div>=C2=A0 end loop;</=
div></div></div></blockquote><div><br></div><div>Create a new function that=
 traps [1] the failure and throws it away, then call that function instead.=
</div><div><br></div><div>[1]=C2=A0<a href=3D"https://www.postgresql.org/do=
cs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING">https://=
www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERR=
OR-TRAPPING</a></div><div><br></div><div>David J.</div><div><br></div>

--00000000000095916705ffd8900b--