Re: Need help with clarification on stored procedure support in PostGreSQL database

[Brent Wood <Brent.Wood@xxxxxxxxxx>]

They are discussed here,

https://kb.objectrocket.com/postgresql/function-vs-stored-procedure-602

(from this page):

The main difference between function and stored procedure is that user-defined functions do not execute transactions. This means, inside a given function you cannot open a new transaction, neither can you commit or rollback the current transaction. It is important to note that stored procedures are just functions that can support transactions and were introduced in Postgresql 11. PostgreSQL functions can be created in a variety of languages including SQL, PL/pgSQL, C, Python.

Perhaps add some detail from here to the official docs?

Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

---

From: Guyren Howe <guyren@xxxxxxxxx>
Sent: Tuesday, March 9, 2021 17:31
To: Tom Lane <tgl@xxxxxxxxxxxxx>; raf <raf@xxxxxxx>
Cc: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; Bysani, Ram <rbysani@xxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Need help with clarification on stored procedure support in PostGreSQL database

 

This seems like an important consideration. I’ve spent 10 minutes searching the documentation for PG 11 and can’t find where it is documented. Perhaps it should be made more prominent?

On Mar 8, 2021, 16:53 -0800, raf <raf@xxxxxxx>, wrote:

On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

raf <raf@xxxxxxx> writes:

Is there any important different between a stored procedure and
a stored function that returns void? I've been using functions
for everything but calling them procedures in my head (even though
most of them do return a value or result set).

A procedure can commit or rollback a transaction (and then start
a new one).

regards, tom lane

thanks.

cheers,
raf

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz Facebook LinkedIn Twitter Instagram

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems