Add PL/pgSQL extra check no_data_found

From: Sergey Shinderuk <s(dot)shinderuk(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add PL/pgSQL extra check no_data_found
Date: 2022-12-08 11:29:20
Message-ID: 799a9ffb-a74a-52da-8976-d89ad9b37129@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I propose to add a new value "no_data_found" for the
plpgsql.extra_errors and plpgsql.extra_warnings parameters [1].

With plpgsql.extra_errors=no_data_found SELECT INTO raises no_data_found
exception when the result set is empty. With
plpgsql.extra_errors=too_many_rows,no_data_found SELECT INTO behaves
like SELECT INTO STRICT [2]. This could simplify migration from PL/SQL
and may be just more convenient.

One potential downside is that plpgsql.extra_errors=no_data_found could
break existing functions expecting to get null or checking IF found
explicitly. This is also true for the too_many_rows exception, but
arguably it's a programmer error, while no_data_found switches to a
different convention for handling (or not handling) an empty result with
SELECT INTO.

Otherwise the patch is straightforward.

What do you think?

--
Sergey Shinderuk https://postgrespro.com/

[1]
https://www.postgresql.org/docs/devel/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
[2]
https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

Attachment Content-Type Size
no_data_found.patch text/x-patch 4.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2022-12-08 11:35:28 Re: add \dpS to psql
Previous Message Melih Mutlu 2022-12-08 11:19:25 Re: [PATCH] psql: Add tab-complete for optional view parameters