On Fri, Aug 29, 2025, at 10:30, Pavel Stehule wrote:
> pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <j...@compiler.org> napsal:
>> Can we think of some SQL-standard function way to also prevent against 0
>> rows?
>>
>
> I am afraid there is not nothing. NULL is the correct result in SQL.
> SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and
> raising an error when something is unexpected
>
> I can imagine allowing the NOT NULL flag for functions, and then the
> result can be checked on NOT NULL value.
I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?
Regarding DML functions, could we make the RETURN () trick work somehow?
Here is a failed attempt:
CREATE OR REPLACE FUNCTION test_update(_a int)
RETURNS bool
RETURN (
WITH update_cte AS (
UPDATE footab SET id = _a WHERE footab.id = _a RETURNING footab.id
)
SELECT id FROM update_cte
);
ERROR: WITH clause containing a data-modifying statement must be at the top
level
LINE 4: WITH update_cte AS (
^
I'm not sure if this is a standard requirement, or if it's just a
PostgreSQL-specific limitation?
/Joel
