Возврат вложенного составного типа из функции PL/pgSQL

Я пытаюсь вернуть вложенные данные этого формата из PostgreSQL в ассоциативные массивы PHP.

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

Кажется, что это возможно с использованием составных типов . В этом ответе описывается, как вернуть составной тип из функции, но он не касается массива составных типов. У меня проблемы с массивами.

Вот мои таблицы и типы:

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[]
);

Моя функция get_people()анализируется нормально, но есть ошибки времени выполнения. Прямо сейчас я получаю сообщение об ошибке: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;

ОБНОВЛЕНИЕв ответ на вопрос Эрвина Брандштеттера в конце его ответа:
Да, я мог бы вернуть SETOF составной тип. Я обнаружил, что с наборами данных работать проще, чем с массивами, потому что запросы SELECT возвращают наборы данных. Причина, по которой я предпочитаю возвращать вложенный массив, заключается в том, что я считаю представление вложенных данных в виде набора строк немного неудобным. Вот пример:

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

В этом примере у человека 1 есть 2 роли, а у человека 2 нет ни одной. Я использую подобную структуру для еще одной из моих функций PL/pgSQL.Я написал хрупкую PHP-функцию, которая преобразует подобные наборы записей во вложенные массивы.

Это представление работает нормально, но я беспокоюсь о добавлении дополнительных вложенных полей в эту структуру. Что, если у каждого человека также есть группа работ? Статусы? и т. д. Моя функция преобразования должна стать более сложной. Представление данных также будет сложным. Если у человека n ролей, m должностей и o статусов, этот человек заполняет max(n, m, o)строк с person_id, person_nameи любые другие данные, которые они бесполезно продублировали в дополнительных строках. Меня совсем не беспокоит производительность, но я хочу сделать это максимально простым способом. Конечно.. может быть, это самый простой способ!

Я надеюсь, что это поможет проиллюстрировать, почему я предпочитаю работать непосредственно с вложенными массивами в PostgreSQL. И, конечно, я хотел бы услышать любые ваши предложения.

И для всех, кто имеет дело с составными типами PostgreSQL с PHP, я нашел эту библиотеку действительно полезной для разбора массива PostgreSQL _agg ()вывода в PHP:https://github.com/nehxby/db_type. Также этот проект выглядит интересно:https://github.com/chanmix51/Pomm