Sql functions and stored procedure

Sql just like any other programming language provides functions and stored procedures. In this post, we will explore the functions and stored procedures in PostgreSQL. In this post, the following points will be highlighted:
— what functions and stored procedures are
— the difference between functions and stored procedures
— creating functions and stored procedures
— applying function and stored procedures in the real examples

Functions and stored procedures in Sql as in any programming language provide concepts of reusability and flexibility. Functions and stored procedures are group of code or statements stored in database and can be used over and over again. Instead of writing the same lines of code or statements, it is more comfortable to group all the statements and save, so that it is possible to use them many times. Concerning flexibility, whenever there is a change in the logic of queries, it is possible to pass new parameter to functions and stored procedures

There are a few differences between functions and stored procedures in PostgreSQL. They are shown in the table below.

Differences between functions and stored procedures

Let’s consider creating a function in PostgreSQL. The following gist illustrates how to create a function. A statement — create [or replace] function function_name — creates, or replaces a function if exists, with a given name and parameters, returns return_type — the data type that a function returns, language plpgsql — indicates procedural extension of PostgreSQL, $$ { function body } $$ — everything inside dollar quoted signs is a function body, declare — shows how variables are declared or initialized, block of code begin — end — contains all the logic of a function, begin — starts the queries and end — indicates the end of a function.

Function creation syntax

The following block of code example shows creation of a simple function that returns the cost of a most expensive purchase of a given user. Important to note:
Declare itemCost integer — we declare a local variable
SELECT max(cost) INTO itemCost — how we initialize itemCost
Return itemCost — returns function value

Creating a simple function

Creating a stored procedure, as shown in the block of code below, is nearly the same as creating a function with a minor difference — it does not have a return statement. The rest is identical.

Procedure creating syntax

Below given block of code shows creating a procedure — transfer(), that takes three parameters and executes commands. Right after the name of the procedure, arguments with corresponding data types are passed — sourceAccountId, destinationAccountId, amount. The procedure subtracts the passed amount from one account and adds it to another account. It has no return statements, since procedures do return values.

Creating a simple procedure

Finally, let’s apply all of this in real examples. To run all the code that will be shown below, you will need PostgreSQL (version 13.2) to be installed on your local machine or run PostgreSQL using docker container. To run PostgreSQL using docker container, execute the file — docker-compose.yaml given below.

Running PostgreSql in docker container

Let’s create user and purchases tables and populate them. For simplicity, a users-table has three columns — id, name and profession; a purchases table has four columns — id, name, cost and user_id.

After executing the statements, we can check our database

Selecting all the users
List of user made purchases

Let’s say that we want to query the user name and their most expensive purchases. It is possible to write some complex queries that will return the desired output, however, we can use our defined function above.
First, we need to execute the statement to create our function.

Creating a simple function

To call our function — execute the following command:

Calling defined function

To take full advantage of the functions, we can use them in many cases, for example, in inside select statements. With functions the statements are much shorter and precise, it reduces boiler plate code and adds more transparency to the statements.

Selecting user names and their most expensive purchases

As we discussed before, stored procedures are bit different from functions. Functions allow executing only Select operation, while stored procedures allow Insert, Update, Delete operations. Stored procedures come extremely handy while dealing with cases when inserting, updating or deleting are necessary.

Let’s consider the bank operation — transferring. When some bank operation is executed, the money is transferred from one account to another. To implement this stored procedure — transfer(), let’s create accounts table and populate.

Creating accounts table and populating

After executing the statements above, we will get the following output.

Selecting user accounts

To call a stored procedure, we use — call procedure_name(). Let’s transfer 500 from account.id = 3 to account.id = 4 and check the results.

Transferring 500 from account.id=3 to account.id=4

In this post, we have discussed functions and stored procedures in PostgreSQL. I hope it was useful.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store