pá 29. 8. 2025 v 11:51 odesílatel Joel Jacobson <joel@compiler.org> napsal:
On Fri, Aug 29, 2025, at 10:30, Pavel Stehule wrote: > pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@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?
CREATE OR REPLACE FUNCTION foo() RETURNS int NOT NULL AS $$ SELECT 10 $$ LANGUAGE ...
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?
I am not sure in this case - I think so this syntax is maybe proprietary - so it is not defined in standard, I cannot remember for ANSI/SQL syntax now.
any limit related to "top level" is PostgreSQL related
