SQL - Como criar uma expressão SQL que retorna uma lista de valores concatenados de uma subquery?

RT.FAQ-8252

No PostgreSQL É possível criar um item UNBOUND do tipo SQL_EXPRESSION contendo uma expressão que forma uma lista de valores agregados resultantes de uma sub-query.
Exemplo:

select array_to_string(array_agg(c.cidade),', ')
from cidades c
where c.estado_id = base.id;

(considerando a entidade base seja ESTADOS)

Para "explodir" o array, segue o exemplo abaixo:

select unnest(a.arr) from
(
   select string_to_array(check_list,chr(13)) as arr
   from tipos_pedidos
   where check_list is not null limit 1
) a ;

O atributo check_list é um texto com valores separados pelo caracter "enter" (chr(13)).
O resultado do select acima é a lista de todos os valores um abaixo do outro.

Outro exemplo:

select array_to_string(array_agg(linha),'; ') 
from (
   select '[ ] ' || unnest(a.arr) as linha 
   from (
       select string_to_array('TESTE'||chr(10)||'TESTE2'||chr(10)||'TESTE3',chr(10)) as arr
   ) a
) sub;

Transformar colunas em linhas:

select key, unnest(arr1) as coluna, unnest(arr2) as valor
from (
    select key
    , array['planejado', 'efetivo']  as arr1
    , array[planejado, efetivo] as arr2
    from (
        select 'abc' as key, 1 as planejado, 2 as efetivo 
        union all
        select 'def' as key, 1 as planejado, 2 as efetivo 
        union all
        select 'ghi' as key, 1 as planejado, 2 as efetivo 
    ) q
) q