How to return a table in PostgreSQL function

In this PostgreSQL tutorial, I will demonstrate how to return table from function in PostgreSQL function. You will understand the syntax of the function, and you will also create a simple function to know how you can define your function with different parameters.

How to return a table in PostgreSQL function

Sometimes, you need to write the same logic, queries, and calculations repeatedly to retrieve the required information from the database, and repeating the same task is not only inefficient but also time-consuming. PostgreSQL has a solution for this, and that is called functions.

A function in PostgreSQL is a named entity that contains a set of logic, calculations, and queries for specific tasks. The function accepts an input value and, based on the given input value, it can produce different results using the same logic or queries that are defined within it.

The function in PostgreSQL can be called multiple times with different input values. With the help of functions, PostgreSQL enables code reusability.

The syntax for creating a function in PostgreSQL is provided 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 a 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 parameter that the 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 PL/SQL.

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;

After executing the above query, I got the expected output as per the screenshot below.

In the above picture, the name of the function is multiplication and accepts two parameters, number1 and number2, of type integer. The return type of the function is also an integer, which is specified using the RETURNS keyword.

Then after the BEGIN, the logic for multiplication is implemented using number1 * number2, then after multiplication, it returns the return_valu,e 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, the multiplication function returns the value of 50 when the two numbers 5 and 10 are 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 without needing to write the same logic repeatedly.

The above example is a simple function that returns a single value; however, the primary focus of this tutorial is on how to return a record using a PostgreSQL function. Let’s explore real-world examples of how to return records using the PostgreSQL function.

PostgreSQL function return table example

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 code below.

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, which is output by 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 a SELECT statement.

Now, call the function retrieve_employees_using_department(2) with a department ID of 2, which corresponds to the engineering department.

SELECT retrieve_employees_using_department(2);

After executing the above function with a department ID of 2, it returns the two employees from the engineering department, Santiago and Charles, with salaries of $60,000 and $62,000, 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 a 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