In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM. The manual:
VACUUMcannot be executed inside a transaction block.
Functions are often called "stored procedures", which has always been a misleading term - probably carried over from other RDBMS. With the arrival of SQL procedures (CREATE PROCEDURE) in Postgres 11 that misnomer should be avoided completely.
SQL procedures can begin and end transactions. But the commands mentioned above are not allowed inside any transaction block at all, so those cannot be included in SQL procedures, either (yet).
Multiple result sets are planned for the future, but not implemented, yet.
Related:
- When to use stored procedure / user-defined function?
- Function Performance
PostgreSQL functions cannot start or end transactions. They are stuck with whatever transaction they inherited from their invoking query.
When PostgreSQL gets stored procedures, they will be able to open and close transactions.
See also,
- https://www.postgresql.org/message-id/178d3380-0fae-2982-00d6-c43100bc8748%402ndquadrant.com