Re: Assert single row returning SQL-standard functions

pá 29. 8. 2025 v 11:51 odesílatel Joel Jacobson <joel(at)compiler(dot)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(at)compiler(dot)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
>