How to Return Multiple Table Fields as a Single Record in PostgreSQL using PL/pgSQL?

Returning Multiple Table Fields as a Record in PostgreSQL with PL/pgSQL

When working with PL/pgSQL, returning multiple fields from several tables as a record can be achieved by employing the RECORD data type. This allows for the creation of a composite data type that can hold fields with different data types.

Example Code

Consider the following modified example, which addresses the additional complexity of retrieving fields from separate rows in a table:

CREATE TABLE user (id int, school_id int, name varchar(32));

CREATE TYPE my_type AS (
  user1_id   int,
  user1_name varchar(32),
  user2_id   int,
  user2_name varchar(32)
);

CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
  RETURNS my_type AS $$
DECLARE
  result my_type;
  temp_result user;
BEGIN
  SELECT id, name INTO temp_result FROM user WHERE school_id = schoolid ORDER BY id ASC LIMIT 2;
  
  result.user1_id := temp_result[1].id;
  result.user1_name := temp_result[1].name;
  
  IF temp_result[2].id IS NOT NULL THEN
    result.user2_id := temp_result[2].id;
    result.user2_name := temp_result[2].name;
  END IF;
  
  RETURN result;
END
$$ language plpgsql;

In this example, the get_two_users_from_school function returns a my_type record with four fields: user1_id, user1_name, user2_id, and user2_name. It retrieves and organizes the data from the user table, flattening the rows into a single, cohesive record.

Dynamic Record Structure

Note that the returned record is not strictly limited to a specific number of fields. You can return an arbitrary number of fields based on the data retrieved from the database.

Usage

To use the function, you can execute the following query:

SELECT * FROM get_two_users_from_school(123);

This will return a record with user details from the specified school ID.

The above is the detailed content of How to Return Multiple Table Fields as a Single Record in PostgreSQL using PL/pgSQL?. For more information, please follow other related articles on the PHP Chinese website!