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.
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.
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 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.
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.
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.
docker-compose up //execute in the same directory as the docker-composeLet’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
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.
To call our function — execute the following command:
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.
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.
After executing the statements above, we will get the following output.
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.
In this post, we have discussed functions and stored procedures in PostgreSQL. I hope it was useful.
