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

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

In PL/pgSQL, creating a function that returns multiple fields from different tables as a single record can be achieved through the RECORD type. Here's how:

RECORD Type

Declare a RECORD type that defines the fields you want to return. For example:

CREATE TYPE person AS (
  name text,
  age integer,
  city text
);

Querying Different Tables

In your PL/pgSQL function, use queries to retrieve fields from different tables. For instance, if you have tables users, addresses, and cities, you can write:

SELECT u.name, u.age, a.city
FROM users AS u
JOIN addresses AS a ON u.address_id = a.id
WHERE u.user_id = @user_id;

Storing Query Results

Store the query results in a record variable. The RECORD type allows you to access fields by column name:

DECLARE result person;
SELECT u.name, u.age, a.city INTO result;

Returning the Record

Finally, return the result record:

RETURN result;

Handling Irregular Results

If you may retrieve an inconsistent number of rows from different tables, use the coalesce() function to provide fallback values:

SELECT
  coalesce(u.name, 'Unknown'),
  coalesce(u.age, 0),
  coalesce(a.city, 'N/A')
INTO result;

Example with Subquery

If the data you need to return is stored as separate rows in a single table, use a subquery:

CREATE FUNCTION get_two_users_from_school(schoolid int)
  RETURNS my_type AS $$
DECLARE
  result my_type;
BEGIN
  -- Select first two users from school
  SELECT id, name
  FROM user
  WHERE school_id = schoolid
  ORDER BY id
  LIMIT 2
  INTO result;
RETURN result;
END
$$ language plpgsql;

This approach allows you to "flatten" the rows into a single record structure.

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