Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table employees(age int, name text, scores int[]);
insert into employees values
  (25, 'Name1', ARRAY [10, 20, 30])
 ,(48, 'Name2', ARRAY [40, 50 , 60, 70])
returning *;

CREATE TABLE




age
name
scores


25
Name1
{10,20,30}

48
Name2
{40,50,60,70}



INSERT 0 2


select scores[1], scores[2], scores[3] 
  from employees where age > 40 and age < 50;


scores
scores
scores


40
50
60



SELECT 1


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


age
name
scores
score
score_number


25
Name1
{10,20,30}
10
1

25
Name1
{10,20,30}
20
2

25
Name1
{10,20,30}
30
3

48
Name2
{40,50,60,70}
40
1

48
Name2
{40,50,60,70}
50
2

48
Name2
{40,50,60,70}
60
3

48
Name2
{40,50,60,70}
70
4



SELECT 7


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

  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;
  return 'output_f_employee_scores_into_columns';
end $f$;

CREATE FUNCTION


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


f_employee_scores_into_columns


output_f_employee_scores_into_columns



SELECT 1




age
name
score_1
score_2
score_3
score_4


48
Name2
40
50
60
70



SELECT 1


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


f_employee_scores_into_columns


output_f_employee_scores_into_columns



SELECT 1




age
name
score_1
score_2
score_3


25
Name1
10
20
30



SELECT 1

age	name	scores	score	score_number
25	Name1	{10,20,30}	10	1
25	Name1	{10,20,30}	20	2
25	Name1	{10,20,30}	30	3
48	Name2	{40,50,60,70}	40	1
48	Name2	{40,50,60,70}	50	2
48	Name2	{40,50,60,70}	60	3
48	Name2	{40,50,60,70}	70	4