Returning a nested composite type from a PL / pgSQL function

Question

I am trying to return nested data of this format from PostgreSQL to PHP associative arrays.

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

This seems to be possible using composite types . This answer describes how to return a composite type from a function, but it does not deal with an array of compound types. I have problems 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[] ); 

The function My get_people() well understood, but there are runtime errors. Now I get the error message: 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 response to a question by Erwin Brandsteter at the end of his answer:
Yes, I can return the SETOF composite type. I found that SETs are easier to handle arrays because SELECT queries return SET. The reason I would prefer to return a nested array is because I think that representing nested data as a set of strings is a bit uncomfortable. Here is 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, but person 2 does not. I use this structure for another PL / pgSQL function. I wrote a fragile PHP function that converts dozens of records like this into nested arrays.

This view works fine, but I'm worried about adding additional nested fields to this structure. What if each person also has a group of jobs? Statuses? etc. My conversion function needs to get complicated. Presenting data will also be difficult. If a person has n roles, m tasks and o statuses, this person fills in the lines max(n, m, o) , person_id , person_name and any other data that they uselessly duplicate in additional lines. I'm not worried about performance at all, but I want to do it in the easiest way. Of course .. maybe this is the easiest way!

Hope this helps illustrate why I prefer to handle nested arrays directly in PostgreSQL. And, of course, I would like to hear any of your suggestions.

And for those who are dealing with PostgreSQL composite types with PHP, I found this library very useful for parsing the output of PostgreSQL array_agg () in PHP: https://github.com/nehxby/db_type . In addition, 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[] ); 

Functions:

 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(); 

Cleaning:

 DROP SCHEMA x CASCADE; 

Main functions:

• A very simplified function that only wraps a simple SQL query.
• Your key mistake was that you took role_name text from the roles table and treated it as a role type that is not.

I will let the code speak for itself. Too much to explain, and now I have no more time.

This is very advanced stuff, and I'm not sure if you really need to return this nested type. Maybe there is a simpler way, for example, SET is not a nested complex type?