pá 29. 8. 2025 v 11:51 odesílatel Joel Jacobson <j...@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 <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? > ANSI/SQL syntax is (pipelined DML) SELECT oldtbl.empno FROM OLD TABLE (DELETE FROM emp WHERE deptno = 2) AS oldtbl; SELECT newtbl.empno FROM NEW TABLE (UPDATE emp SET salary = 0 WHERE deptno = 2) AS newtbl; > > /Joel >
