SUMMARY: This article covers how to create user-defined functions using PL/pgSQL procedural language in PostgreSQL. It introduces user-defined functions and gives examples of their use in different scenarios:
1. PL/pgSQL
2. User-defined functions and procedures
3. CREATE FUNCTION statement syntax
4. Examples of user-defined functions
In this post, We are going to see how to develop user defined function in PostgreSQL stored procedures.
PL/pgSQL
In PostgreSQL, there are various procedural languages such as PL/pgSQL, TCL, Python, and Perl that are referred to as stored procedures. PL/pgSQL is the procedural language that comes by default with PostgreSQL.
We will use the PL/pgSQL procedure language here. It allows us to perform more complex tasks, such as easy computation, as compared to SQL, and also makes use of loops, functions, procedures, and much more.
User-defined functions and procedures
User-defined functions and procedures are simply blocks of SQL statements that perform some task. Once the function is compiled the code can be reused over and over again.
Places in a SQL expression where user-defined functions can be used:
- SELECT statement list:
select col1, col2, 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 (col1,col2,function_name());
- SET clause of an UPDATE query:
Update table_name set n=function_name();
A drawback of user-defined functions is that the user cannot run a transaction in a function—i.e., the user cannot perform COMMIT or ROLLBACK.
Example
Create a function using a COMMIT statement in the body:
postgres=# create or replace function func2(n int)
returns void
as
$$
begin
commit;
end;
$$ language 'plpgsql';
CREATE FUNCTION
Execute the function using a SELECT statement. This should should throw an error:
postgres=# select func2(9);
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function func2(integer) line 1 at COMMIT
postgres=#
User-defined functions are similar to procedures. The difference is that functions always returns a value, whereas procedures do not return a value; in procedures we can also perform COMMIT and ROLLBACK commands and new transactions.
Example
Create a procedure:
postgres=# create procedure pro()
language plpgsql
As
$$
begin
create table my_table(n int);
commit;
insert into my_table values (9);
commit;
delete from my_table;
rollback;
End;
$$;
CREATE PROCEDURE
Execute the procedure using a CALL statement:
postgres=# call pro();
CALL
postgres=# select * from my_table;
n
---
9
(1 row)
Following the steps in the procedure, the table has been created and one record inserted, but the DELETE transaction has been rolled back due to the ROLLBACK command.
PostgreSQL started supporting procedures with version 11. The CALL command is used to invoke a procedure.
Please refer to the PostgreSQL documentation for more information on procedures:
https://www.postgresql.org/docs/11/sql-createprocedure.html.
Please refer to the PostgreSQL documentation for more information on functions:
https://www.postgresql.org/docs/11/sql-createfunction.html.
CREATE FUNCTION statement syntax
These are the parts of the CREATE FUNCTION statement:
1. CREATE FUNCTION fun_name(p1 type, p2 type)
2. RETURNS type AS $$
3. BEGIN
4. <<piece of code>>
5. END;
6. $$ LANGUAGE language_name;
A brief explanation for each step:
1. ’fun_name’ is the user-defined function name, and p1 and p2 are the parameters.
2. Return type of the function—e.g., int, text.
3. Begin signals that the block has started.
4. User code—e.g., DML statements.
5. End keyword signals the end of the block.
6. Specify the procedural language—in our case, plpgsql.
Please refer to the PostgreSQL documentation for more information on CREATE FUNCTION: https://www.postgresql.org/docs/11/sql-createfunction.html.
Examples of user-defined functions
Create a table:
Create table test(n int ,n1 date);
Insert a few records:
insert into test values (1,'2019-11-21’);
insert into test values (2,'2019-11-22');
insert into test values (3,'2019-11-23');
insert into test values (4,'2019-11-24');
insert into test values (5,'2019-11-25');
Check the data:
postgres=# select * from test;
n | n1
---+------------
1 | 2019-11-21
2 | 2019-11-22
3 | 2019-11-23
4 | 2019-11-24
5 | 2019-11-25
(5 rows)
In PostgreSQL, we have three argument modes: IN, OUT, and INOUT. If we don’t specify argument mode, then it will take IN by default. Argument modes OUT and INOUT cannot be used with the RETURNS TABLE notation.
IN -send values to function,OUT - get values from function and INOUT - does both.
Example 1: Create a user-defined function using default IN argument mode
In this example, if no argument mode is provided while specifying the parameter name and type, then it will take IN by default.
Pass a value to a function and then insert that value into table “test”:
postgres=# Create or replace function fun1(n int) returns int
as
$$
Begin
Insert into test values (n,'2019-11-26');
Return 1;
End;
$$
Language 'plpgsql';
CREATE FUNCTION
postgres=#
Call the function from a SELECT query:
postgres=# select fun1(10);
fun1
------
1
(1 row)
Check whether data has been inserted into the table:
postgres=# select * from test;
n | n1
----+------------
1 | 2019-11-21
2 | 2019-11-22
3 | 2019-11-23
4 | 2019-11-24
5 | 2019-11-25
10 | 2019-11-26 <--row has been inserted
(6 rows)
Example 2: Create a user-defined function using OUT argument mode.
In this example the argument mode is OUT.
Take a value from a function and then insert that value into table “test”:
postgres=# Create or replace function fun2(n1 out int)
returns int
as
$$
begin
n1:=11;
end;
$$
language 'plpgsql';
CREATE FUNCTION
postgres=# insert into test values (fun2(),'2019-11-27');
INSERT 0 1
postgres=# select * from test;
n | n1
----+------------
1 | 2019-11-21
2 | 2019-11-22
3 | 2019-11-23
4 | 2019-11-24
5 | 2019-11-25
10 | 2019-11-26
11 | 2019-11-27 <--row has been inserted
(7 rows)
Example 3: Create a user-defined function using INOUT argument mode
In this example, argument mode is INOUT. As mentioned earlier, the INOUT argument mode will work for both IN and OUT—it will send the value to a function and also get the value from a function.
Send and take a value from a function and then inserting that value into table “test”:
postgres=# Create or replace function fun3(n inout int)
returns int
as
$$
begin
n:=n+1;
end;
$$
language 'plpgsql';
CREATE FUNCTION
postgres=# insert into test values (fun3(11),'2019-11-27');
INSERT 0 1
postgres=# select * from test;
n | n1
----+------------
1 | 2019-11-21
2 | 2019-11-22
3 | 2019-11-23
4 | 2019-11-24
5 | 2019-11-25
10 | 2019-11-26
11 | 2019-11-27
12 | 2019-11-27 <--row has been inserted
(8 rows)
postgres=#
Example 4: Calling a procedure from a user-defined function
Create a procedure, where we are performing a DELETE operation on table “test”:
postgres=#create or replace procedure pro1()
as
$$
begin
delete from test;
end;
$$ language 'plpgsql' ;
CREATE PROCEDURE
Create function and invoke the procedure in it:
postgres=# Create or replace function fun1(n int) returns int
As
$$
Begin
call pro1();
Return 1;
End;
$$
Language 'plpgsql';
CREATE FUNCTION
Execute the function using a SELECT statement:
postgres=# select fun1(1);
fun1
------
1
(1 row)
Verify the data has been deleted and removed:
postgres=# select * from test;
n | n1
---+----
(0 rows)
postgres=#
Hope it helps!
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...