Syntax
The syntax of the PostgreSQL function is given below.
RETURNS return_type AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END;
LANGUAGE plpgsql;
- CREATE keyword is used to create a new user-defined function, and REPLACE keyword is used to modify the existing function.
- The name of the function is defined by the function_name, and the function can take one or more arguments. Three types of arguments can be used in the function. These are IN, OUT, and INOUT. The default argument of the function is IN argument. It is used to pass the value into the function, and it can’t be changed inside the function. The OUT argument is used to return the value from the function. The INOUT argument can be used to pass value to the function and return value from the function. It can be reassigned inside the function like the OUT argument.
- The data type of the variable that will be returned by the function is mentioned after the RETURNS keyword.
- The body of the function will start with the DECLARE keyword, where the necessary variables will be declared. Next, the required SQL statements will be defined inside the BEGIN and END blocks.
- The name of the procedural language is defined after the LANGUAGE keyword in which the function will be executed.
Pre-requisites
A. Install the PostgreSQL
You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:
$ sudo systemctl start postgresql.service
Run the following command to login to PostgreSQL with root privilege.
B. You can create a database named “testdb” by running the following SQL statement.
If you want to create a table into the testdb database, then you have to type “\c” to change the database. But in this tutorial, I didn’t change the database and the table, and all functions were created in the default database named postgres.
C. Create a table named products with four fields and insert four records into the table to test the user-defined functions that will be created later in this tutorial.
id SERIAL PRIMARY KEY,
name VARCHAR (30),
type VARCHAR (20),
price MONEY);
D. Insert four records into the table.
VALUES ('Samsung A40','Mobile', 300),
('A4','Mouse', 20),
('Sony 42”','TV', 1000),
('Samsung 3TB','HDD', 600);
Different Examples of PostgreSQL User-Defined Functions
The uses of user-defined functions with argument and without argument in PostgreSQL have been shown below by using multiple examples.
Example-1: User-Defined Function Without Argument and Return Value
Run the following SQL statement to create a function named function1() without any argument, and the function will return nothing. The function will update the record of the products table where the id value is 3 after execution.
RETURNS void AS
$$
BEGIN
--Update the price value of the third record
UPDATE products SET Price = 1500 WHERE Id = 3;
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to read all records of the products table before executing function1().
The following output shows the inserted records of the products table.
Run the following SQL statement to execute the function.
The following output shows that one row is affected by calling the function function1(). The value of the price field of the table will be updated where the id value is 3.
Run the following SQL statement to read all records of the products table after executing function1().
The following output shows that the price value of the products table has been updated where the id value is 3, and it is 1500 now.
Example-2: User-Defined Function With IN Argument
The default argument of the function is IN argument. Run the following SQL statement to create a function named function2() with two arguments, and the function will return nothing. The function will insert a new record to the products table with the argument values.
RETURNS void AS
$$
BEGIN
--Insert a new record
INSERT INTO products(Name, Type, Price)
VALUES(name, type, price);
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to execute the function three IN argument values. These are “iPhone”, “Mobile”, and 2000.
The following output shows that one row is affected by calling the function function2(), and a new record will be inserted into the table.
Run the following SQL statement to read all records of the products table after executing function2().
The following output shows that a new record has been inserted into the products table.
Example-3: User-Defined Function With OUT Argument
The OUT argument is used to return the value from the function. Run the following SQL statement to create a function named function3() with one OUT argument of MONEY type, and the function will return data of MONEY type. The function will return the assigned value of the OUT argument.
RETURNS MONEY AS
$$
BEGIN
--Assign a value to the argument
price:=1000;
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to delete records from the products table based on the value returned by function3().
The following output shows that three rows are affected by calling the function function3(). The returned value of the function is 1000, which matches with the three records of the products table where the id values are 1, 2, and 4.
Run the following SQL statement to read all records of the products table after executing function3(). The output shows that two records exist in the table where the price values are more than 1000.
The following output shows that 3 records have been deleted from the products table.
Example-4: User-Defined Function With INOUT Argument
The INOUT argument is used in the function to pass values into the function and return values from the function. Run the following SQL statement to create a function named calculate() with an INOUT argument of FLOAT type and an IN argument of integer type. The function will return data of FLOAT type. The function will be executed with the sales amount, which will be a float value, and the discount, which will be an integer. The sales amount after the discount will be calculated based on the IN argument values. Next, the calculated sales amount will be returned from the function by using the OUT argument.
sales_amount inout FLOAT, discount INT)
RETURNS FLOAT AS
$$
BEGIN
--Calculate the discount amount
sales_amount:=sales_amount - (sales_amount*discount/100);
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following statement to execute the function with 10000 as the sales amount and 5 as the discount amount
The 5% of 10000 is 500 and 10000-500 = 9500. The following output shows sales amount after a 5% discount.
Example-5: User-Defined Function With the Conditional Statement
Run the following SQL statement to create a function named search_product() with an argument, and the function will return the data of varchar type. The way of declaring the function variable and using the conditional statement has been shown in this function. The function variable named product_name has been defined with the Name type of the products table. The value of IN argument, tp, will be matched with the value of the type field of the products table. If no match is found in the table, then an error will be raised with the message, “Product type does not exist”. If any match is found, then the value of the Name field of that product type will be stored in the function variable, and the variable will be returned from the function.
RETURNS VARCHAR AS
$$
DECLARE
product_name products.Name%type;
BEGIN
-- Search product name based on the type
SELECT Name into product_name
FROM products
WHERE products.Type = tp;
IF not FOUND, then
raise 'Product type does not exist.';
END IF;
RETURN product_name;
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to read all records from the products table.
Run the following SQL statement to execute the function search_product() with the product type “Mobile”.
Run the following SQL statement to execute the function search_product() with the product type “AC”.
The following output shows that the products table contains two records of type values, “TV” and “Mobile”. The output of the first execution of the function has returned “iPhon” because the product type “Mobile” exists in the table. The output of the second execution of the function has returned an error message because the product type “AC” does not exist in the table.
Example-6: User-Defined Function With Aggregate Function
Any aggregate function supported by SQL can be used in the PostgreSQL function. Run the following SQL statement to create a function named count_product() without any argument, and the function will return the data of integer type. The count() aggregate function has been used in the function that is used to count the number of rows based on the specific condition. Here, a function variable named total_product has been declared in the function to store the return value of the count() function. The count() function has been used in the SELECT query to count those records where the product name starts with the word “Sony”.
RETURNS INT AS
$$
DECLARE
total_product INT;
BEGIN
--Count the total number of the particular product
SELECT count(*) into total_product FROM products
WHERE name LIKE 'Sony%';
RETURN total_product;
END;
$$
LANGUAGE plpgsql;
The following output will appear if the function is created successfully in the postgres database.
Run the following command to execute the function, count_product().
The following output shows that the count() function returns the value 1 because there is only one record that matches the value, “Sony”.
Conclusion
The purpose of using PostgreSQL functions and different ways of using PostgreSQL functions with IN, OUT, and INOUT arguments have been described in this tutorial by creating multiple PostgreSQL functions. The IN argument is used to take value into the function. The OUT argument is used to return the value from the function. The INOUT argument is used to take input value into the function and return output from the function. The function can be created without any argument also. I hope the user will be able to create and use the PostgreSQL function properly after reading this tutorial.
