[kogus]

I'm not sure what database platform they used, but in SQL Server, functions cannot have side-effects.

https://learn.microsoft.com/en-us/sql/relational-databases/u...

[chasil]

Dear SQL Server user, welcome to the world of SQL/PSM.

https://en.wikipedia.org/wiki/SQL/PSM

Within this ADA-esque world, packages, procedures, and functions may initiate DML.

Assuming these objects are in the default "definer rights" context, the DML runs with the full privilege of the owner of the code (this can be adjusted to "invoker rights" with a pragma).

Perhaps this is why Microsoft ignores it (as Sybase did before it).

[kogus]

I am not an expert on SQL/PSM, but I have worked in an Oracle shop before, and used PL/SQL extensively. In SQL Server, the equivalent is T-SQL. T-SQL procedures can do pretty much anything (assuming it is executed by a user with sufficient privileges), including creating and altering tables, creating and executing other procedures, running dynamic sql, as well as ordinary CRUD style operations. The "no side effect" limitation applies specifically to SQL functions.

[Tostino]

In Postgres they absolutely can. They are all just happening inside the same transaction scope unlike stored procedures.

[flysand7]

Speaking of postgres, you don't even need a function, you can just use RETURNING clause of a modifying query to provide data source for the select:

    select *
    from (
        delete
        from users
        returning id
    )