3 ms·

I think the new part is "allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."

by jrs95 8y ago

protomyth 8y ago
That'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 ago
  Absolutely! 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 ago
  Just 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 ago
    I 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 ago
Stored 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.)
- cryptonector 8y ago
  Stored procedures can return values. See https://www.postgresql.org/docs/devel/static/sql-call.html https://www.postgresql.org/docs/devel/static/sql-call.html

I think the new part is "allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."

by jrs95 8y ago

I think the new part is "allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure."

That'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 ago
Absolutely! 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 ago
Just 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 ago
  I 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 ago

Stored 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.)

cryptonector 8y ago
Stored procedures can return values. See https://www.postgresql.org/docs/devel/static/sql-call.html https://www.postgresql.org/docs/devel/static/sql-call.html