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

Answer 1

Consider this (improved and fixed) test case, tested with PostgreSQL 9.1.4:

CREATE SCHEMA x;
SET search_path = x, pg_temp;

CREATE TABLE people (person_id integer primary key, name text);
INSERT INTO people (person_id, name) VALUES
 (1, 'name1')
,(2, 'name2');

CREATE 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')
,(3, 2, 'role name2!')
,(4, 2, 'another role2');

CREATE TYPE role AS (
  role_id   int
 ,role_name text
);

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

Function:

CREATE OR REPLACE FUNCTION get_people()
    RETURNS person[]  LANGUAGE sql AS
$func$
SELECT ARRAY (
   SELECT (p.person_id, p.name
          ,array_agg((r.role_id, r.role_name)::role))::person
   FROM    people p
   JOIN    roles r USING (person_id)
   GROUP   BY p.person_id
   ORDER   BY p.person_id
   )
$func$;

Call:

SELECT get_people();

Clean up:

DROP SCHEMA x CASCADE;

Core features are:

• A much simplified function that only wraps a plain SQL query.
• Your key mistake was that you took role_name text from table roles and treated it as type role which is isn't.

I'll let the code speak for itself. There is just too much to explain and I don't have any more time now.

This is very advanced stuff and I am not sure you really need to return this nested type. Maybe there is a simpler way, like a SET of not nested complex type?