+39 0574 159 3000
PostgreSQL 11 – Server-side Procedures (Part 1)
Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL!
(Ok, well not exactly now but we will have the ability once PostgreSQL 11 comes out)
A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle.
The commit from last week adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). It also includes support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution.
While this commit simply adds structure that is built upon existing functionality, it lays down the foundation on which we will be building the real meat to be made available in PostgreSQL 11. Next steps include the implementation of:
- Transaction control – allowing us to COMMIT and ROLLBACK inside procedures
- Returning multiple result sets
Part 2 here: https://www.2ndquadrant.com/postgresql-11-server-side-procedures-part-2/
Ok, I feel a little dumbfounded here.
A procedure is basically a function with no return value – a function where the return value simply is ignored.
function blah1(var x): aType
return aType
procedure blah2(var x): aType
return
blah1(“x”)
blah2(“x”)
no difference.
Also – next step: return multiple result sets? So… then you’ve taken the procedure that you made from a retvar-less function and made it a function again? Uh…
And… we can commit and rollback inside functions, right?
I really fail to see how it can be so complicated to substitute “create procedure blah( varlist )” with “create function blah( varlist ): void” – simply allow a return *nothing* inside a function with undefined return value and call it a day?
No, we can’t commit and rollback inside functions – they operate within a transaction. I’m guessing you’re not a Postgres user?
Procedures will allow us to operate across transactions, one of the things that is new and cool.
autonomous transaction possible?
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
This might help: https://wiki.postgresql.org/wiki/Autonomous_subtransactions
Functions:
*) can be called inside a query (select func() from foo)
*) generally return a result
*) must return a single set
*) are scoped to a transaction
Procedures:
*) can not be called inside a query
*) typically don’t return results except for maybe error code
*) can return multiple result sets (although FWICT the postgres implementation does not allow for this yet)
*) can flush the transaction (essentially a COMMIT; followed by a BEGIN;) within the proceure
Being able to flush the transaction is by far the most important part; it allows for various kinds of things that are dangerous or impossible with functions (for example, a routine that never terminates).
Does this mean that this will unlike pl/pgsql where are basically extensions. Will the new one be part of core engine. What syntax would it follow? Like pl/pgsql or something new.
thanks.
If you are asking about the procedure syntax, yes it will be part of the core engine. Please see the blog to understand the new keywords and syntax inserted as part of this commit.
astounding feature!
What about CTE?
Are you referring to Common Table Expressions? If yes, then that’s a different topic unrelated to Procedures.
Looks good, will you have to extend the protocol to handle multiple return sets? IIRC, it currently can’t handle multiple return sets.
Yes, that’s part of the roadmap.