Returning a nested composite type from a PL/pgSQL function

Question

I'm trying to return nested data of this format from PostgreSQL into PHP associative arrays.

[
  'person_id': 1,
  'name': 'My Name',
  'roles': [
      [ 'role_id': 1, 'role_name': 'Name' ],
      [ 'role_id': 2, 'role_name': 'Another role name' ]
  ]
]

It seems like it could be possible using composite types. This answer describes how to return a composite type from a function, but it doesn't deal with an array of composite types. I'm having some trouble with arrays.

Here are my tables and types:

CREATE TEMP TABLE people (person_id integer, name text);
INSERT INTO "people" ("person_id", "name") VALUES
    (1, 'name!');

CREATE TEMP TABLE roles (role_id integer, person_id integer, role_name text);
INSERT INTO "roles" ("role_id", "person_id", "role_name") VALUES
    (1, 1,  'role name!'),
    (2, 1,  'another role');

CREATE TYPE role AS (
    "role_name" text
);

CREATE TYPE person AS (
    "person_id" int,
    "name" text,
    "roles" role[]
);

My get_people() function parses fine, but there are runtime errors. Right now I'm getting the error: array value must start with "{" or dimension information

CREATE OR REPLACE FUNCTION get_people()
    RETURNS person[] AS $$
DECLARE myroles role[];
DECLARE myperson people%ROWTYPE;
DECLARE result person[];
BEGIN
    FOR myperson IN
        SELECT *
        FROM "people"
    LOOP
        SELECT "role_name" INTO myroles
        FROM "roles"
        WHERE "person_id" = myperson.person_id;

        result := array_append(
            result,
            (myperson.person_id, myperson.name, myroles::role[])::person
        );
    END LOOP;

    RETURN result;
END; $$ LANGUAGE plpgsql;

UPDATE in reply to Erwin Brandstetter's question at the end of his answer:
Yeah, I could return a SETOF a composite type. I've found SETs are easier to deal with than arrays, because SELECT queries return SETs. The reason I'd rather return a nested array is because I think representing nested data as a set of rows is a little awkward. Here's an example:

 person_id | person_name | role_name | role_id
-----------+-------------+-----------+-----------
         1 | Dilby       | Some role |    1978
         1 | Dilby       | Role 2    |       2
         2 | Dobie       | NULL      |    NULL

In this example, person 1 has 2 roles, and person 2 has none. I'm using a structure like this for another one of my PL/pgSQL functions. I wrote a brittle PHP function that converts record sets like this into nested arrays.

This representation works fine, but I'm worried about adding more nested fields to this structure. What if each person also has a group of jobs? Statuses? etc. My conversion function will have to become more complicated. The representation of the data will be complicated as well. If a person has n roles, m jobs, and o statuses, that person fills max(n, m, o) rows, with person_id, person_name, and whatever other data they have uselessly duplicated in the extra rows. I'm not at all worried about performance, but I want to do this the simplest way possible. Of course.. maybe this is the simplest way!

I hope this helps to illustrate why I'd rather deal directly with nested arrays in PostgreSQL. And of course I'd love to hear any suggestions you have.

And for anyone dealing with PostgreSQL composite types with PHP, I've found this library to be really useful for parsing PostgreSQL's array_agg() output in PHP: https://github.com/nehxby/db_type. Also, this project looks interesting: https://github.com/chanmix51/Pomm

Returning a nested composite type from a PL/pgSQL function

1 Answers