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 :
- Open Source.
- Highly Expandable.
- PostgreSQL supports geographic objects so you can use it for location- based services and geographic information systems.
- It permits you the freedom to use, modify, and implement it as per your business needs.
- It supports JSON data.
Disadvantages :
- Many open-source applications don’t support PostgreSQL.
- It’s usually tough to migrate data from other Database servers like- MSSQL, MYSQL, etc.
- 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 :-
CREATE [OR REPLACE] FUNCTION Function_Name([ [argument mode] [argument name] argument type [ { DEFAULT | = } default_expr ] [, ...] ])
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
BEGIN
<function_body>
[...]
RETURN { variable_name | value }
END;
$$ LANGUAGE language_name;
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.
CREATE OR REPLACE FUNCTION public.totalrecords()
RETURNS integer
LANGUAGE 'plpgsql'
AS $BODY$
declare
total integer;
begin
select count(*) into total from public."User";
return total;
end;
$BODY$;
In the above example I am using “plpgsql” language, It may be different as per requirement.
Let’s call this function: –
SELECT public.totalrecords()
When the above query is executed, the result would be −
totalrecords
--------------
6
(1 row)
Places in a SQL expression where user-defined functions can be used:
- SELECT statement list:
Select column1, column2 Function_Name() From TABLE_NAME;
- WHERE clause condition:
Select * From TABLE_NAME Where col_name=function_name();
- VALUES clause of an INSERT statement:
Insert Into TABLE_NAME Values (column1, column2, Function_Name ());
- SET clause of an UPDATE query:
Update TABLE_NAME Set n= Function_Name ();
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 :-
CREATE OR REPLACE PROCEDURE Procedure_Name ([ [argument mode] [argument name] argument type [ { DEFAULT = } default_expr ] [, ...] ])
LANGUAGE language_name
AS
$$
DECLARE
--- Variables
BEGIN
--- SQL statement 1 (logic)
.
.
--- SQL statement N (logic)
END
$$
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.
CREATE OR REPLACE PROCEDURE AddEmployees(EId INOUT integer, Name varchar(100), Age integer, Address varchar(100), Salary integer)
LANGUAGE plpgsql AS
$$ BEGIN
INSERT INTO public."Employee"(id, name, age, city, salary) Values(EId, Name, Age, Address, Salary)
RETURNING id into EId;
END $$
Once it is executed successfully. Let’s call the stored procedure “AddEmployees”.
To call stored procedures we have a Call Keyword.
CALL public.AddEmployees(7, 'RK', 22, 'Vapi', '20000')
Now let’s Retrieve data from table.
Select * from public."Employee"
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.
You may also like
Unlocking the Power of .NET Core Development: Cutting-Edge Strategies and Tools
As the software development industry adapts, Core has become...
How can you use ML Sentiment Analysis in ASP.NET Core?
We are going to talk about ML Sentiment Analysis which is able to conveniently explore the aspects o...
How to work Loop Execution with Benchmark using .NET
What is Benchmarking? It is used for measuring timing complexity for various algorithms and part of ...
