1SELECT e.aid, e.actors, a.act_names, e.benefactors, b.ben_names
2FROM eg_assoc e
3, LATERAL (
4 SELECT ARRAY( SELECT name
5 FROM generate_subscripts(e.actors, 1) i
6 JOIN eg_person p ON p.id = e.actors[i]
7 ORDER BY i)
8 ) a(act_names)
9, LATERAL (
10 SELECT ARRAY( SELECT name
11 FROM generate_subscripts(e.benefactors, 1) i
12 JOIN eg_person p ON p.id = e.benefactors[i]
13 ORDER BY i)
14 ) b(ben_names);
15
1SELECT id, i.title AS item_title, t.tag_array
2FROM items i
3JOIN ( -- or LEFT JOIN ?
4 SELECT it.item_id AS id, array_agg(t.title) AS tag_array
5 FROM items_tags it
6 JOIN tags t ON t.id = it.tag_id
7 GROUP BY it.item_id
8 ) t USING (id);
9