Postgres: разделение элементов массива на столбцы

Вопрос в том, почему вы хотите, чтобы каждый элемент массива находился в отдельном столбце? Вы можете получить каждую оценку в отдельной строке, а не в столбце, где score_number указывает, из какой позиции в массиве она взята. Демо на db<>fiddle:

select * from employees
  cross join unnest(scores) with ordinality as u(score,score_number);

В противном случае для этого вам понадобится динамическое выполнение SQL, и вам придется использовать его косвенно (двухэтапные вызовы). PostgreSQL должен иметь возможность определять количество, имена и типы выходных столбцов в начале запроса. Вы не можете иметь функцию или оператор, который мог бы возвращать разные типы и количества полей под разными именами в разное время, полностью динамически.

Однако у вас может быть процедура, которая динамически создает запрос с помощью format(), затем выполняет и сбрасывает его результат в таблицу, из которой вы затем можете прочитать на втором шаге.
Это обходит ограничение, поскольку вы постоянно возвращаете текст с именем таблицы, в которую сбрасывается реальный результат. Побочные эффекты процедуры не ограничены так, как ее прямой результат.

create function f_employee_scores_into_columns(employee_name text)returns text
language plpgsql as $f$
declare rec record;
        first_score_number int;
        last_score_number int;
        query_body text;
begin
  --check how many elements there are in the target array
  select array_lower(scores,1)
        ,array_upper(scores,1)
  from employees
  where employees.name=employee_name
  limit 1
  into first_score_number
      ,last_score_number;
  --construct a column list, placing each element in separate column
  select string_agg(format('scores[%1$s] as "score_%1$s"',score_number)
                    ,',' order by score_number)
  from generate_series(first_score_number,last_score_number,1)score_number
  into query_body;
  --inject the constructed column list into the final query
  --then execute it, writing the result to a table
  execute format('drop table if exists output_f_employee_scores_into_columns;
                  create table output_f_employee_scores_into_columns as
                  select age,name,%s 
                  from employees 
                  where employees.name=$1',query_body) using employee_name;
    
  --you would want to randomise the output table name and make it temp
  --right now all calls in all sessions would overwrite each other's outputs
  return 'output_f_employee_scores_into_columns';
end $f$;

--2-step call
select f_employee_scores_into_columns('Name2');
select * from output_f_employee_scores_into_columns;

--2-step call
select f_employee_scores_into_columns('Name1');
select * from output_f_employee_scores_into_columns;

Вы также можете просто перенести это на внешний интерфейс, где это обычно тривиально.