Need help with clarification on stored procedure support in PostGreSQL database

On Sun, Mar 7, 2021 at 11:49 AM Bysani, Ram <rbysani@informatica.com> wrote:

Please provide details / clarify if Stored Procedures are available in
versions 11.x, 12.x, 13.x Please also confirm when it was actually added.

https://www.postgresql.org/docs/

https://www.postgresql.org/docs/current/sql-createprocedure.html

David J.

On 3/7/21 10:48 AM, Bysani, Ram wrote:

PostgreSQL version: 11.11

Operating system:�� Linux 64 bit

Description:

Greetings

We have found posts on several forums that Stored Procedures are not
supported in PostGreSQL databasel.

We looked at the documentation but it is not clear when Stored Procedure
support was added. We are aware that Functions are supported.

Please provide details / clarify if Stored Procedures are available in
versions 11.x, 12.x, 13.x Please also confirm when it was actually added.

https://www.postgresql.org/docs/ <https://www.postgresql.org/docs/&gt;

Thanks for your time

Go to page David Johnson posted:

https://www.postgresql.org/docs/current/sql-createprocedure.html

and look at 'Supported Versions:' at top of page.

Regards

Ram Bysani

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, Mar 07, 2021 at 11:01:07AM -0800, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 3/7/21 10:48 AM, Bysani, Ram wrote:

PostgreSQL version: 11.11

Operating system:�� Linux 64 bit

Description:

Greetings

We have found posts on several forums that Stored Procedures are not
supported in PostGreSQL databasel.

We looked at the documentation but it is not clear when Stored Procedure
support was added. We are aware that Functions are supported.

Please provide details / clarify if Stored Procedures are available in
versions 11.x, 12.x, 13.x Please also confirm when it was actually
added.

https://www.postgresql.org/docs/ <https://www.postgresql.org/docs/&gt;

Thanks for your time

Go to page David Johnson posted:

https://www.postgresql.org/docs/current/sql-createprocedure.html

and look at 'Supported Versions:' at top of page.

Regards
Ram Bysani

--
Adrian Klaver
adrian.klaver@aklaver.com

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

cheers,
raf

raf <raf@raf.org> 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

On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

raf <raf@raf.org> 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

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?

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@gmail.com>
Sent: Tuesday, March 9, 2021 17:31
To: Tom Lane <tgl@sss.pgh.pa.us>; raf <raf@raf.org>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Bysani, Ram <rbysani@informatica.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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@raf.org>, wrote:
On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

raf <raf@raf.org> 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

[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png][https://niwa.co.nz/sites/niwa.co.nz/files/ETNZ_Official-supplier-logo-75h.png]&lt;https://www.niwa.co.nz&gt;
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<https://www.niwa.co.nz&gt; Facebook<https://www.facebook.com/nzniwa&gt; LinkedIn<https://www.linkedin.com/company/niwa&gt; Twitter<https://twitter.com/niwa_nz&gt; Instagram<https://www.instagram.com/niwa_science&gt;
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

Guyren Howe <guyren@gmail.com> writes:

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?

https://www.postgresql.org/docs/current/xproc.html

regards, tom lane

I think that’s burying the lede a bit. Still, unless the “Extending SQL Section” acquired an Overview section, I’m not sure where else you’d put it.

On Mon, Mar 8, 2021 at 9:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Guyren Howe <guyren@gmail.com> writes:

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?

https://www.postgresql.org/docs/current/xproc.html

CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into 37.4

Even if this may not be sufficient it seems reasonable to at least remain
consistent. I suspect most people start at CREATE, not "Server
Programming".

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Mar 8, 2021 at 9:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Guyren Howe <guyren@gmail.com> writes:

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?

https://www.postgresql.org/docs/current/xproc.html

CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into 37.4
Even if this may not be sufficient it seems reasonable to at least remain
consistent. I suspect most people start at CREATE, not "Server
Programming".

Yeah, fair point, and it also seems like we ought to give those links
more prominence. In the attached proposed patch, I put them into the
introductory section of the reference pages. I also failed to resist
the temptation to do some wordsmithing in 38.4 ...

regards, tom lane

On Tue, Mar 9, 2021 at 1:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Mar 8, 2021 at 9:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Guyren Howe <guyren@gmail.com> writes:

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?

https://www.postgresql.org/docs/current/xproc.html

CREATE FUNCTION links into 37.3 but CREATE PROCEDURE doesn't link into

37.4

Even if this may not be sufficient it seems reasonable to at least remain
consistent. I suspect most people start at CREATE, not "Server
Programming".

Yeah, fair point, and it also seems like we ought to give those links
more prominence. In the attached proposed patch, I put them into the
introductory section of the reference pages. I also failed to resist
the temptation to do some wordsmithing in 38.4 ...

Thanks. Some observations.

The omission of the "OUT" parameter mode seems intentional since at present
our procedures do not support OUT mode parameters.

Instead of "The difference" or "One difference" I would suggest: "However,
a procedure does not return a value, so there is no return type
declaration; though a procedure can declare INOUT (but not plain OUT)
parameters."

Relocating the links to the description instead of usage is good. The
additional procedure link after the examples seems redundant, particularly
as the linked to location doesn't actually have more examples. The
"...further information on writing" doesn't really match up with reality
either. It is more "...further information on incorporating
functions/procedures into applications."

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

The omission of the "OUT" parameter mode seems intentional since at present
our procedures do not support OUT mode parameters.

Um, I just created one. I think this *used* to be true, and this bit of
the docs didn't get fixed. If I back-patch this, I'll have to research
when it changed.

Instead of "The difference" or "One difference" I would suggest: "However,
a procedure does not return a value, so there is no return type
declaration; though a procedure can declare INOUT (but not plain OUT)
parameters."

Not sure if that's an improvement.

Relocating the links to the description instead of usage is good. The
additional procedure link after the examples seems redundant, particularly
as the linked to location doesn't actually have more examples.

I was modeling that on the existing pattern in create_function.sgml,
which has similar verbiage in the EXAMPLES section. But I suppose
we could drop that if we have a link in the description section.

regards, tom lane

On Tue, Mar 9, 2021 at 3:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

The omission of the "OUT" parameter mode seems intentional since at

present

our procedures do not support OUT mode parameters.

Um, I just created one. I think this *used* to be true, and this bit of
the docs didn't get fixed. If I back-patch this, I'll have to research
when it changed.

Five months ago it seems.

https://github.com/postgres/postgres/commit/2453ea142233ae57af452019c3b9a443dad1cdd0

The patch and email thread for that commit make me pause, though I cannot
put into words why.

Instead of "The difference" or "One difference" I would suggest:

"However,

a procedure does not return a value, so there is no return type
declaration; though a procedure can declare INOUT (but not plain OUT)
parameters."

Not sure if that's an improvement.

The "however" part is probably a wash; I just dislike seeing a count
started and not having an ending and thus being left in a state of "what
didn't they include that's important".

The part about commenting about OUT/INOUT parameters still working even
though there is no return provide complete coverage of the
differences/similarities between functions and procedures with respect to
passing back data to the caller.

Relocating the links to the description instead of usage is good. The
additional procedure link after the examples seems redundant,

particularly

as the linked to location doesn't actually have more examples.

I was modeling that on the existing pattern in create_function.sgml,
which has similar verbiage in the EXAMPLES section. But I suppose
we could drop that if we have a link in the description section.

Yeah, it was an existing deficiency, but being a bit more invasive seems
warranted, and as you say it is be located partly because it is much higher
level content being pointed to (hence description, not usage/examples).

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Mar 9, 2021 at 3:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Instead of "The difference" or "One difference" I would suggest:
"However, a procedure does not return a value, so there is no return type
declaration; though a procedure can declare INOUT (but not plain OUT)
parameters."

Not sure if that's an improvement.

The "however" part is probably a wash; I just dislike seeing a count
started and not having an ending and thus being left in a state of "what
didn't they include that's important".

The rest of the paragraph is what's important enough to include here.

I very strongly dislike the existing "The difference ..." wording,
because it implies that that's the only difference, which is immediately
belied by the rest. "However" isn't a lot better. Would it be better
to turn the para into a bulleted list, which we could introduce with
"The key differences are:" ?

regards, tom lane

On Tue, Mar 9, 2021 at 4:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I very strongly dislike the existing "The difference ..." wording,
because it implies that that's the only difference, which is immediately
belied by the rest.

Agreed!

Would it be better
to turn the para into a bulleted list, which we could introduce with
"The key differences are:" ?

Indeed, reworking the rest of the paragraph around that introduction would
be much better.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Mar 9, 2021 at 4:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Would it be better
to turn the para into a bulleted list, which we could introduce with
"The key differences are:" ?

Indeed, reworking the rest of the paragraph around that introduction would
be much better.

v2 attached.

regards, tom lane

On Tue, Mar 9, 2021 at 5:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Mar 9, 2021 at 4:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Would it be better
to turn the para into a bulleted list, which we could introduce with
"The key differences are:" ?

Indeed, reworking the rest of the paragraph around that introduction

would

be much better.

v2 attached.

Thank you. That reads much better, aside from maybe a couple of missing
commas.

-       Certain function attributes such as strictness don't apply to
+      Certain function attributes, such as strictness, don't apply to

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Mar 9, 2021 at 5:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

v2 attached.

Thank you. That reads much better, aside from maybe a couple of missing
commas.

-       Certain function attributes such as strictness don't apply to
+      Certain function attributes, such as strictness, don't apply to

I see your point, but that ends up with way too many commas in the
sentence. After puzzling a bit, I broke it into two sentences.

Done like that, thanks for the discussion!

regards, tom lane