CYBERTEC Logo

Tech preview: PostgreSQL 11 - CREATE PROCEDURE

04.2018 / Category: / Tags:

Many people have have been asking for this feature for years and PostgreSQL 11 will finally have it: I am of course talking about CREATE PROCEDURE. Traditionally PostgreSQL has provided all the means to write functions (which were often simply called “stored procedures”). However, in a function you cannot really run transactions - all you can do is to use exceptions, which are basically savepoints. Inside a function, you cannot just commit a transaction or open a new one. CREATE PROCEDURE will change all that and provide you with the means to run transactions in procedural code.

Using CREATE PROCEDURE in PostgreSQL

CREATE PROCEDURE will allow you to write procedures just like in most other modern databases. The syntax is quite simple and definitely not hard to use:

As you can see there are a couple of similarities to CREATE FUNCTION so things should be really easy for most end users.

The next example shows a simple procedure:

The first thing to notice here is that there is a COMMIT inside the procedure. In classical PostgreSQL functions this is not possible for a simple reason. Consider the following code:

What would happen if some function call simply commits? Total chaos would be the consequence. Therefore, real transactions are only possible inside a “procedure”, which is never called the way a function is executed. Also: Note that there is more than just one transaction going on inside our procedure. Because of this, a procedure is more of a “batch job”.

The following example shows, how to call the procedure I have just implemented:

The first two tables where committed - the third table has not been created because of the rollback inside the procedure.

To me CREATE PROCEDURE is definitely one of the most desirable features of PostgreSQL 11.0. The upcoming release will be great and many people will surely welcome CREATE PROCEDURE the way I do.

0 0 votes
Article Rating
Subscribe
Notify of
guest
5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dillip Sahoo
Dillip Sahoo
3 years ago

Can somebody pls tell how to call a stored procedure from NetCobol. we are able to make call to Function but not stored procedure.

Damini Raut
Damini Raut
5 years ago

can somebody please tell me how to call one procedure within another stored procedure in PostgreSQL 11.1....

laurenz
laurenz
5 years ago
Reply to  Damini Raut

Just use the CALL SQL statement.

지현명
지현명
5 years ago

good ^^

Adam Brusselback
Adam Brusselback
6 years ago

This is great, and i'm glad Postgres is finally getting actual procedures. Functions have served me very well, but there are a few places I know procedures would have been able to better serve the need. Will be fun to play around with soon.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram