3 ms·

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-database

by kogus 1y ago

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/user-defined-functions/user-defined-functions?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/u...

Tostino 1y ago
In Postgres they absolutely can. They are all just happening inside the same transaction scope unlike stored procedures.
- flysand7 1y ago
  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 )
chasil 1y ago
Dear SQL Server user, welcome to the world of SQL/PSM. https://en.wikipedia.org/wiki/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 1y ago
  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.
  - indigo945 1y ago
    In Postgres, the main difference between functions and procedures is that procedures can do transaction management (begin/rollback/commit), whereas functions can't. Other than that, functions are not limited in any way, and can be written in SQL, Postgres's PL/SQL knockoff PL/PgSQL, any plugin language such as Java, or even be called via the C ABI. Obviously, Postgres can do nothing about side effects here, and doesn't attempt to. (PS: Postgres does have a concept of "safe" languages. Safe languages are expected to run programs only with the database permissions of the calling context, and prevent all other IO. However, Postgres does nothing to ensure that they do, that's the language plugin's job. Also, those functions can still perform DML and DDL as long as the calling context has the permissions to do so.) By the way, you can do the same in SQL Server via what Microsoft ambiguously calls an Assembly. Via Assemblies, SQL Server can load procedures, aggregates and, yes, functions, from a .NET DLL file.
    - chasil 1y ago
      Is appears that there are efforts to bring full PSM to Postgres, as I see with PL/pgPSM. https://postgres.cz/wiki/SQL/PSM_Manual https://postgres.cz/wiki/SQL/PSM_Manual
  - chasil 1y ago
    Transact-SQL, as Sybase originally named it, is not standardized. As far as I know, the only implementation outside of Sybase and Microsoft is Amazon's Babelfish. https://aws.amazon.com/rds/aurora/babelfish/ https://aws.amazon.com/rds/aurora/babelfish/ It goes without saying that SQL/PSM is far more pervasive on many more platforms, precisely because it is an ANSI standard.

by kogus 1y ago

Tostino 1y ago

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

flysand7 1y ago
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 )

chasil 1y ago

Dear SQL Server user, welcome to the world of SQL/PSM. https://en.wikipedia.org/wiki/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 1y ago
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.
- indigo945 1y ago
  In Postgres, the main difference between functions and procedures is that procedures can do transaction management (begin/rollback/commit), whereas functions can't. Other than that, functions are not limited in any way, and can be written in SQL, Postgres's PL/SQL knockoff PL/PgSQL, any plugin language such as Java, or even be called via the C ABI. Obviously, Postgres can do nothing about side effects here, and doesn't attempt to. (PS: Postgres does have a concept of "safe" languages. Safe languages are expected to run programs only with the database permissions of the calling context, and prevent all other IO. However, Postgres does nothing to ensure that they do, that's the language plugin's job. Also, those functions can still perform DML and DDL as long as the calling context has the permissions to do so.) By the way, you can do the same in SQL Server via what Microsoft ambiguously calls an Assembly. Via Assemblies, SQL Server can load procedures, aggregates and, yes, functions, from a .NET DLL file.
  - chasil 1y ago
    Is appears that there are efforts to bring full PSM to Postgres, as I see with PL/pgPSM. https://postgres.cz/wiki/SQL/PSM_Manual https://postgres.cz/wiki/SQL/PSM_Manual
- chasil 1y ago
  Transact-SQL, as Sybase originally named it, is not standardized. As far as I know, the only implementation outside of Sybase and Microsoft is Amazon's Babelfish. https://aws.amazon.com/rds/aurora/babelfish/ https://aws.amazon.com/rds/aurora/babelfish/ It goes without saying that SQL/PSM is far more pervasive on many more platforms, precisely because it is an ANSI standard.