Postgres function return multiple rows

by

PostgreSQL Function Returning Multiple Rows – Explanation

When writing functions in PostgreSQL, it is possible to create functions that return multiple rows as the result. This can be achieved by using the RETURNS TABLE clause in the function definition.

Here’s an example of a PostgreSQL function that returns multiple rows:


CREATE OR REPLACE FUNCTION get_employee_details()
  RETURNS TABLE (employee_id INT, employee_name VARCHAR, department_name VARCHAR)
AS $$
BEGIN
  RETURN QUERY
    SELECT e.id, e.name, d.name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
END;
$$ LANGUAGE plpgsql;

In the example above, we define a function called get_employee_details that returns a table with three columns: employee_id, employee_name, and department_name.

The function body starts with the RETURN QUERY statement, which allows us to execute a query and return its result. In this case, we are performing a join between the employees and departments tables to retrieve the employee ID, name, and corresponding department name.

To call this function and retrieve the result, you can use the following query:


SELECT * FROM get_employee_details();

This will execute the function and return all the rows from the result set.

It’s important to note that when calling a function that returns multiple rows, you need to use it in the FROM clause of the outer query. This is because the function itself is treated as a table that can be queried.

For example, you can use the function in a join operation:


SELECT d.name, e.employee_name
FROM departments d
JOIN get_employee_details() e ON d.name = e.department_name;

This query joins the departments table with the result of the get_employee_details function on the matching department names.

In summary, by using the RETURNS TABLE clause in a PostgreSQL function, you can easily return multiple rows as the result. These functions can be useful in situations where you need to encapsulate complex queries or calculations and return the results as a table-like structure.

Leave a comment