How to Return Record using PostgreSQL Function

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 $$
BEGIN
    -- Function logic and queries here
    RETURN return_value;
END;
$$ 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 $$
BEGIN
    RETURN number1 * number2;
END;
$$ 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)
AS $$
BEGIN
    RETURN QUERY
    SELECT e.name, s.salary
    FROM employees e
    JOIN salaries s ON e.employee_id = s.employee_id
    WHERE e.dept_id = department_id;
END;
$$ 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.

SELECT retrieve_employees_using_department(2);

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.

Conclusion

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