In this PostgreSQL tutorial, I will show you how to return a record using the PostgreSQL function. You will understand the syntax of the function and also you will create one simple function to understand how you can define your own function with different parameters.
Additionally, I will show how you can implement the function in the real world by creating a function that will return records.
How to Return Record using PostgreSQL Function
Sometimes you need to write the same logic, queries and calculations again and again to get the required information from the database, and writing the same thing is not good and is time-consuming. PostgreSQL has a solution for this, and that is called functions.
A function is named in PostgreSQL that contains a set of logic, calculations and queries for particular tasks. The function accepts an input value and based on the given input value, the function can give different results using the same logic or queries which is defined in it.
The function in PostgreSQL can be called multiple times with different input values. With the help of function, PostgreSQL provides the reusability of the code.
The syntax to create a function PostgreSQL is given below.
CREATE OR REPLACE FUNCTION function_name( parameter_name parameter_type )
RETURNS return_type AS $$
-- Function logic and queries here
$$ LANGUAGE language_name;
- CREATE OR REPLACE FUNCTION: This is the command to create a new function and replace the existing one if the function with the same name exists.
- function_name: It is the name of the new function and should be meaningful and represent the purpose of the function.
- parameter_name: This is the name of the parameter that you will use in your logic.
- parameter_type: This is the type of the parameter that function will accept as input.
- Returns return_type: Here the return_type represents the data type that the function is going to return and it can be any data type such as integer, varchar, table type etc.
- $$: It denotes the starting and ending point of the function body.
- Begin: This denotes the beginning of the function code block where all the logic related to the function is written.
- RETURN return_value: Here the keyword RETURN tells what the function will return and the return_value is the actual value the function will return.
- End: This denotes the end of the function body.
- Language language_name: It represents the language of the logic and the most common is the Pg\PLSQL.
Let me show you a simple example of how to create a function in PostgreSQL so open your query editor or command prompt and type the following code to create a function ‘multiplication’.
CREATE OR REPLACE FUNCTION multiplication(number1 integer, number2 integer)
RETURNS integer AS $$
RETURN number1 * number2;
$$ LANGUAGE plpgsql;
In the above picture, the name of the function is multiplication and accepts the two-parameter number1 and number2 of type integer, and the return type of the function is an integer which is specified using RETURNS integer.
Then after the BEGIN, the logic for multiplication is implemented using number1 * number2, then after multiplication, it returns the return_value which is the result of multiplication using the RETURN.
Now call the function with two values 5 and 10 using the following command.
SELECT multiplication(5, 10);
As you can see function multiplication return the value of 50 when the two number 5 and 10 is multiplied. You can call the multiplication function with different input values such as ‘SELECT multiplication(10*2)’ which will yield the value 20.
This is how you can reuse the function for different input values, and don’t need to write the same logic again and again.
The above example is a simple function that returns a single value but the main focus of this tutorial is how to return the record using PostgreSQL function. So Let’s see with real-world examples of how to return records using the PostgreSQL function.
Returning Record using PostgreSQL Function
Suppose you are a backend developer in an IT company and you have a task to create a function that should return the employee’s name and salary based on the given department ID. You have access to the tables of departments, employees and salaries which are shown below.
Now create a retrieve_employees_using_department to get the employee’s name and salary based on the given ID using the below code.
CREATE OR REPLACE FUNCTION retrieve_employees_using_department(department_id integer)
RETURNS TABLE(employee_name text, salary numeric)
SELECT e.name, s.salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE e.dept_id = department_id;
$$ LANGUAGE plpgsql;
Look at line 2 where the return type of the function is TABLE(employee_name text, salary numeric) which means the function will return a table (set of rows) containing records, and the returning table (set of rows) record will contain two values of employee name of type text and salary of type numeric.
Then at line 5, the returning value of the function within BEGIN and END is QUERY as output to the function. So when you want the function to return records use the function return type as Table() and the function body return value as QUERY with SELECT statement.
Now call the function retrieve_employees_using_department(2) with a department ID equal to 2 which is the engineering department.
After execution of the above function with department ID equal to 2, it returns the two employees from the engineering department Santiago and Charles with salary 60000 and 62000 respectively.
This is how you can define your own function in PostgreSQL to perform specific tasks.
In this PostgreSQL tutorial, you have learned how to create a function in PostgreSQL that returns a record as output. You also learned about the syntax of creating a function and created one simple function that returns the multiplication of two values.
You may also read:
- Create a stored procedure in PostgreSQL
- PL/pgSQL Block Structure in PostgreSQL
- PostgreSQL Add Foreign Key If Not Exist
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.