Re: Add PL/pgSQL extra check no_data_found

čt 8. 12. 2022 v 12:29 odesílatel Sergey Shinderuk <
s(dot)shinderuk(at)postgrespro(dot)ru> napsal:

> 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?
>

I am not against it. It makes sense.

I don't like the idea about possible replacement of INTO STRICT by INTO +
extra warnings.

Handling exceptions is significantly more expensive than in Oracle, and
using INTO without STRICT with the next test IF NOT FOUND THEN can save one
safepoint and one handling an exception. It should be mentioned in the
documentation. Using this very common Oracle's pattern can have a very
negative impact on performance in Postgres. If somebody does port from
Oracle, and wants compatible behavior then he should use INTO STRICT. I
think it is counterproductive to hide syntax differences when there is a
significant difference in performance (and will be).

Regards

Pavel

> --
> 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