4

I understand the distinction between:

  • Scalar functions
  • Set-Returning-Functions (SRF)s
  • Internal functions
  • Window functions
  • Aggregate functions of all sorts
  • User-Implemented functions (which in PostgreSQL can be implement in any language)
  • Etc.

In SQL Server "Stored Procedures" are permitted through EXEC. What does that provide over any other function executed with SELECT that returns NULL?

When PostgreSQL gets Stored Procedures what will they bring me, and what is the formal distinction if any between a function and a stored procedure in the spec?

I read this question but it seems to predate the announcement of the implementation

  • 2
    Is SQL Server relevant in this context? Or put differently: How does the implementation of stored procedures (we call them sprocs) in SQL Server affect the future implementation of "stored procedures" in PostgreSQL? – hot2use Jan 9 '18 at 7:04
  • @hot2use No, not really. I was more wondering what the implementation offers PostgreSQL, and Erwin did a great job answering that, but a comparison between SQL Servers Stored Procs and the upcoming PostgreSQL implementation sounds like a great if anyone has the answer. – Evan Carroll Jan 19 '18 at 1:15
8

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:

VACUUM cannot be executed inside a transaction block.

True stored procedures would not impose these restrictions.

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:

6

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,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.