21st Mar 2022

Introduction of PostgreSQL Function and Stored Procedure

.NET

Written By, Pradeep Kumar Yadav

Overview

PostgreSQL database is more flexible than others due to the fact it could create and store the information inside the shape of relations, procedures, etc. It is a customizable database, as we are able to modify the storage containers according to our requirements. The information is controlled with the aid of using the schemas and catalogs. PostgreSQL supports lots of languages, due to that, we are able to execute queries in any programming language either in the shell or on the pgAdmin side.

Like different database management systems, we use functions to perform operations on the records. These functions are user-defined. We additionally use a different function to manipulate storage capacity and performance as well. It’s acknowledged as “STORED PROCEDURES”.

Here, we can see each Function and Stored Procedure one by one.

What is PostgreSQL?

It’s an object-relational database management system (ORDBMS), developed at the University of California at Berkeley Computer Science Department by Michael Stonebraker.

Its source code is available under PostgreSQL license, a liberal open-source license. Anyone with the proper abilities is free to use, modify, and distribute PostgreSQL in any form.

It helps a big part of the SQL standard and offers many modern features:

      • Complex Queries
      • Foreign Keys
      • Triggers
      • Transactional Integrity
      • Updatable Views
      • Multi-Version Concurrency Control

PostgreSQL can be expand by the user in many ways by adding few new

      • Data Types
      • Operators
      • Functions
      • Index Methods
      • Aggregate Functions
      • Procedural Language

Advantages and Disadvantages of PostgreSQL

Advantages

  1.  Open Source.
  2.  Highly Expandable.
  3.  PostgreSQL supports geographic objects so you can use it for location- based services and geographic information systems.
  4.  It permits you the freedom to use, modify, and implement it as per your  business needs.
  5.  It supports JSON data.

Disadvantages

  1.  Many open-source applications don’t support PostgreSQL.
  2.  It’s usually tough to migrate data from other Database servers like- MSSQL, MYSQL, etc.
  3.  It is slower as compared to MySQL.

Function with PostgreSQL PL/PgSQL

User-defined functions are blocks of SQL statements that perform some user-defined tasks. Once the function is compiled the code can be reused over and over again as per requirement.

A limitation of user-defined functions is that the user cannot run a transaction in a function — i.e., the user cannot execute ROLLBACK or COMMIT.

 Syntax :-

Where :-

  • Function_Name” is the name of the function.
    [OR REPLACE] option permits modifying an existing function if it already exists.
  • argument mode” is the IN, OUT, INOUT, or VARIADIC mode of an argument. The default is IN.
  • argument name” is the name of an argument that we will pass as a parameter.
  • argument type” is data type(s) of arguments.
  • The “RETURN” clause specifies the type of data you want to return from the function. The return_datatype may be a base, composite, or domain type, or can reference the type of a table column.
  • function-body is the area where we write the executable part.
  • AS keyword is used for creating a standalone function.
  • language_name” is the name of the language that the function is implemented in. Here, it Can be PL/PgSQL, SQL, C, internal, or the name of a user-defined procedural language.

I am going to use this Employee table schema to perform a function where I will return a total number of records.

In the above example I am using “plpgsql” language, It may be different as per requirement.

Let’s call this function: –

When the above query is executed, the result would be −

Places in a SQL expression where user-defined functions can be used:

      • SELECT statement list:

      • WHERE clause condition:

      • VALUES clause of an INSERT statement:

      • SET clause of an UPDATE query:

Stored Procedure with PostgreSQL PL/PgSQL

Stored Procedure is simply blocks of SQL statements that perform some tasks. Once we compiled the procedure it can be reused over and over again. Stored procedures can execute transactions.

Procedure returns only the INOUT parameters. By using procedures, application performance is increased. We can write and execute more than one SQL statement inside a single procedure.

Syntax :-

Where :-

  • Procedure-Name” defines the name of the user-defined procedure.
  • [OR REPLACE]” option permits modifying an existing procedure if it already exists.
  • argument mode” is the mode of an argument: IN, OUT, INOUT, or VARIADIC and it returns only INOUT.
  • argument name” is the name of an argument that we will pass as a parameter.
  • argument type” is data type(s) of arguments.
  • language_name” is the name of the language that the function is implemented in. Here, it Can be PL/PgSQL, SQL, C, internal, or the name of a user-defined procedural language.

Example: –

I am going to use this Employee table schema to perform a stored procedure and will insert a new row.

Once it is executed successfully. Let’s call the stored procedure “AddEmployees”.

To call stored procedures we have a Call Keyword.

Now let’s Retrieve data from table.

Here We can see one new record added.

Conclusion

Here, we saw both stored procedure and function syntax and examples.

Function is a good choice if you want to execute an SQL statement that returns a single value result or table formatted result. But, if you want to start a transaction, commit or rollback with multiple SQL statements, then the procedure is the best choice.

Written By,

.NET Developer at Yudiz Solutions Pvt. Ltd