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.
(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.