4 ms·
> "PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure." Did PostgreS
by pwaivers 8y ago
> "PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."
Did PostgreSQL not have stored procs before?
- gmfawcett 8y agoYes it did. The "embedded transactions" is the new feature.
- j16sdiz 8y agoNo, it didn't. It had functions, not procedure. You can emulate (most of) procedure using function that return void. See also https://blog.2ndquadrant.com/postgresql-11-server-side-procedures-part-1/ https://blog.2ndquadrant.com/postgresql-11-server-side-proce...
- riku_iki 8y ago> most of Curious, what are those procedures which can't be emulated as functions. Maybe you have a chance to describe some example?
- cryptonector 8y agoThere is only one thing that procedures can do that functions can't: nest transactions inside the transaction that calls it.
- gmfawcett 8y agoYes, that's true, although slightly pedantic. I'm sure there are procedural cases that PG functions can't emulate, although I don't think I've ever encountered one in practice. Having said that, the addition of transaction support is a great step forward.
- cryptonector 8y agoOnly one: the ability to BEGIN/ROLLBACK/COMMIT in the body of the function.
- saltcured 8y agoAnd, this is just adding new syntactic support for SQL standards compliance, right? I don't think it's really a new control-flow if you ignore syntactic differences. I think you can get the equivalent behavior out of BEGIN/EXCEPT blocks in plpgsql functions. I think this behaves as if you used SAVEPOINT and ROLLBACK TO SAVEPOINT which have been directly supported in postgresql but not in functions.
- jrs95 8y agoI think the new part is "allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."
- protomyth 8y agoThat's a rather nice feature that Transact-SQL (both Sybase and Microsoft) has had for a long time. It should make it easier for some folks to convert over.
- dcherman 8y agoAbsolutely! I come from a historically Microsoft shop, and the lack of this was by far the biggest barrier when initially learning PostgresSQL since I was still in the mindset of designing stuff as I would in Sql Server.
- endorphone 8y agoJust as a point of clarity on that, SQL Server only allows one encapsulating transaction per session. You can have begins and so on within a stored procedure, but really that is little more than a "just in case" defense (e.g. just in case the caller didn't already do this), as you can only commit or rollback the outer transaction. If you want to have atomic transactions within a transaction you have to do hacks like make a distributed call back to yourself, in that new session creating another transaction. It's an element of SQL Server that confuses people to this day.
- protomyth 8y agoI was more on the Sybase side and dealt with an environment where the calling program didn't initiate a transaction so it worked quite well for me. We were kind of touchy about having a transaction open longer than it needed to be.
- dragonwriter 8y agoStored procedures are technically new with 11, but I think the only functional (rather than syntactic) difference between the new support for stored procs and stored functions which happen to not return anything is the transaction control for stored procs. (And stored functions returning nothing are often referred to informally as stored procedures in postgres discussions.)
- fjcp 8y agoI'm probably wrong, but I think Stored Procedures are being introduced in 11. From the announcement: "SQL Stored Procedures PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command." It doesn't make sense to teach people how to create procedures if they already have them before. The docs[0] also don't have a section for CREATE PROCEDURE before 11[1]. It is a great tool anyway, better late than never. [0] https://www.postgresql.org/docs/10/static/sql-commands.html https://www.postgresql.org/docs/10/static/sql-commands.html [1] https://www.postgresql.org/docs/11/static/sql-commands.html https://www.postgresql.org/docs/11/static/sql-commands.html
- gmfawcett 8y agoYou're correct, "CREATE PROCEDURE" didn't exist until PG 11. But "CREATE FUNCTION" has existed for ages, and it covers most of the common cases for server-side logic.
- colanderman 8y agoStored functions, not procedures. The difference is explained here: https://www.postgresql.org/docs/11/static/xproc.html https://www.postgresql.org/docs/11/static/xproc.html Basically, procedures do not return values, and can have nested transactions. (Today, functions are used to accomplish much of what seems more appropriate for a procedure.)
- cryptonector 8y agoThey do return values: https://www.postgresql.org/docs/devel/static/sql-call.html https://www.postgresql.org/docs/devel/static/sql-call.html CALL executes a procedure. If the procedure has output arguments, then a result row will be returned. and from https://www.postgresql.org/docs/devel/static/sql-createprocedure.html https://www.postgresql.org/docs/devel/static/sql-createproce... argmode The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. (OUT arguments are currently not supported for procedures. Use INOUT instead.)
- colanderman 8y agoNot in the sense of a function; you can't call a procedure from within an expression. Interesting that they can produce output rows though.
- cryptonector 8y agoYes, that's true.