Horton

Thread

Commits

  1. Doc: improve introductory information about procedures.

  2. Support for OUT parameters in procedures

  1. Need help with clarification on stored procedure support in PostGreSQL database

    Bysani, Ram <rbysani@informatica.com> — 2021-03-07T18:48:59Z

    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/



Thanks for your time





Regards

Ram Bysani

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-07T18:52:13Z

    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.

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2021-03-07T19:01:07Z

    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/>
> 
> 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

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

    raf <raf@raf.org> — 2021-03-08T22:22:28Z

    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/>
> > 
> > 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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-08T23:31:56Z

    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

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

    raf <raf@raf.org> — 2021-03-09T00:53:00Z

    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

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

    guyren@relevantlogic.com <guyren@gmail.com> — 2021-03-09T04:31:53Z

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

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

    Brent Wood <brent.wood@niwa.co.nz> — 2021-03-09T04:39:53Z

    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]<https://www.niwa.co.nz>
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> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science>
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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-09T04:41:20Z

    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

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

    guyren@relevantlogic.com <guyren@gmail.com> — 2021-03-09T04:47:18Z

    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 Mar 8, 2021, 20:41 -0800, 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
>
> regards, tom lane

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-09T04:47:43Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-09T20:57:28Z

    "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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-09T22:16:11Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-09T22:21:49Z

    "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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-09T23:16:45Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-09T23:50:43Z

    "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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-09T23:54:10Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-10T00:18:21Z

    "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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-10T15:53:35Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-10T16:35:36Z

    "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