Return multiple fields as a record in PostgreSQL with PL/pgSQL

sql

best-practices

performance

functions

byAnton Shumikhin·Jan 26, 2025

To return multiple fields as a composite record in PostgreSQL, use the ROW constructor within a PL/pgSQL function. Check this simplified example:

CREATE FUNCTION get_details(id INT) RETURNS RECORD AS $$
BEGIN
  RETURN QUERY SELECT u.id, ROW(u.name, u.email) FROM users u WHERE u.id = id;
END;
$$ LANGUAGE plpgsql;

To call it, do:

SELECT * FROM get_details(1) AS (user_id INT, personal_info RECORD);

In this case, personal_info is a record carrying the user's name and email.

Choosing the appropriate return type

Here's a way to decide when you might use different return types:

Opt to use RECORD when you need flexibility in column structures.
Use RETURN QUERY if you want to return the results of a SELECT query straight up.
Go for RETURNS TABLE when dealing with a fixed number of columns consistently.
Consider defining a custom composite type for any structures you frequently return in the records.

Choose wisely! Making the right return type decision enables robust and maintainable PL/pgSQL functions.

Key best practices for returning records

In practice, consider the following for more effective use of records:

Unambiguously name your record fields. Avoid conflicts by qualifying columns with table aliases.
Ensure data type consistency in polymorphic structures by casting RECORD elements.
Always test your functions thoroughly to make sure they return the expected results.
Don't forget to use exception management and proper error handling in your functions.

Stick to these practices and you'll surely end up with reliable and error-free functions.

Performance considerations and tips

When dealing with multiple tables, performance can be severely affected:

Be smart and index columns commonly used in WHERE clauses for faster queries.
Reduce function execution time by minimizing the use of joins or subqueries.
Consider RETURNS TABLE or SETOF for large datasets, as these can be more performant.

Optimizing your function calls ensures they are as efficient as possible.

Diving deeper into the treasure map

Let's understand the subtle nuances of returning records:

Custom types for your most frequent journeys

If you consistently return a common data structure, it can be more efficient to create a custom composite type.

-- Define a composite type
CREATE TYPE user_details AS (name TEXT, email TEXT);

-- Use the custom type in function
CREATE FUNCTION get_user_details(userid INT) RETURNS user_details AS $$
BEGIN
  SELECT name, email INTO user_details -- Your treasures
  FROM users
  WHERE id = userid; -- The treasure chest
  -- Who knew dealing with user details could be this fun?
  RETURN user_details; -- Return your treasure map, map maker!
  -- Remember, your code is your story. Make it enjoyable!
END;
$$ LANGUAGE plpgsql;

Dynamic functions for diverse adventures

Polymorphic functions that cater to different return types become invaluable when dealing with varying structures.

Error handling: Unwanted, yet valuable treasures

Errors are the unwanted treasures. But, they can provide valuable insights if handled correctly.

-- Define a function that also provides operation status
CREATE FUNCTION process_data(arg1 type, OUT result RECORD, OUT status TEXT) AS $$
BEGIN
  -- Logic to process data, unlock the(!) forbidden treasures  
  status := 'Success'; -- You got the treasure, mate. Yay!
EXCEPTION
  WHEN others THEN
    status := 'Error: ' || SQLERRM; -- Oops! The treasure chest was a Pandora box. Better luck next time!
END;
$$ LANGUAGE plpgsql;