Looking for a PL/PGSQL function to return N columns

Question

Run in a PL/pgSQL a function to return N columns and put in a view or something that allow to get the table with the N column updated without human intervention.

Using the below code from:
create dynamic column from a column with multi variables

---sample data 
DROP TABLE IF EXISTS  split_clm;
CREATE TEMP TABLE  split_clm(
  id integer PRIMARY KEY,
  name text,
  hobby text, 
  value int
);
INSERT INTO  split_clm (id, name, hobby,value) VALUES
(1, 'Rene', 'Python, Monkey_Bars','5'),
(2, 'CJ', 'Trading, Python','25'),
(3, 'Herlinda', 'Fashion','15'),
(4, 'DJ', 'Consutling, Sales','35'),
(5, 'Martha', 'Social_Media, Teaching','45'),
(6, 'Doug', 'Leadership, Management','55'),
(7, 'Mathew', 'Finance, Emp_Engagement','65'),
(8, 'Mayers', 'Sleeping, Coding, Crossfit','75'),
(9, 'Mike', 'YouTube, Athletics','85'),
(10, 'Peter', 'Eat, Sleep, Python','95'),
(11, 'Thomas', 'Read, Trading, Sales','105');
/****query****/
--1NF <done>
DROP TABLE IF EXISTS  split_clm_Nor;
CREATE TEMP TABLE     split_clm_Nor  AS 
  SELECT id, name, unnest(string_to_array(hobby, ', ')) AS Ivalues , value
  FROM  split_clm
  ORDER BY id;
-- 
 --Select * from    split_clm_Nor limit 6; ---
 ---ver 2.0
DROP TABLE IF EXISTS  split_clm_Nor2;
  CREATE TEMP TABLE      split_clm_Nor2  AS
  SELECT id, name, lower(unnest(string_to_array(hobby, ', '))) AS Ivalues , value,count(1)  as "Case_Volume"
  FROM  split_clm
  GROUP BY 1,2,3,4
  ORDER BY id
;
Select * from    split_clm_Nor2  ; 
 ---------------------------
 DROP TABLE IF EXISTS   tmpTblTyp2 CASCADE ; 
DO LANGUAGE plpgsql $$ 
DECLARE v_sqlstring VARCHAR  = ''; 
BEGIN 
v_sqlstring := CONCAT( 'CREATE  TABLE  tmpTblTyp2 AS SELECT '   
                      ,(SELECT  STRING_AGG( CONCAT('NULL::int AS ' , ivalues )::TEXT , ' ,' ORDER BY ivalues )::TEXT
                        FROM (SELECT DISTINCT ivalues  FROM  split_clm_Nor2) a
                        )
                      ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 
 --------------------------------------------
DROP TABLE IF EXISTS  tmpMoJson ;
CREATE TEMP TABLE     tmpMoJson  AS
--CREATE TEMP TABLE tmpMoJson AS
      SELECT 
         name AS name
         ,(json_build_array( mivalues )) AS js_mivalues_arr
         ,json_populate_recordset ( NULL::tmpTblTyp2 /** use temp table as a record type!!*/
                                   , json_build_array( mivalues )  /** builds row-type column that can be expanded with (jprs).* */
                                  ) jprs  
      FROM ( SELECT name
                   ,json_object_agg(ivalues,value) AS mivalues 
             FROM split_clm_Nor2
             GROUP BY 1
             ORDER BY 1
            ) a
;
Select * from    tmpMoJson  ; 
 SELECT  
  name
,(ROW((jprs).*):: tmpTblTyp2).* -- explode the composite type row
--, js_mivalues_arr /** optional **/
FROM  tmpMoJson ;

Expecting get that table continue update

name	athletics	coding	consutling	crossfit	eat	emp_engagement	fashion	finance	leadership	management	monkey_bars	python	read	sales	sleep	sleeping	social_media	teaching	trading	youtube
CJ	null	null	null	null	null	null	null	null	null	null	null	25	null	null	null	null	null	null	25	null
DJ	null	null	35	null	null	null	null	null	null	null	null	null	null	35	null	null	null	null	null	null
Doug	null	null	null	null	null	null	null	null	55	55	null	null	null	null	null	null	null	null	null	null
Herlinda	null	null	null	null	null	null	15	null	null	null	null	null	null	null	null	null	null	null	null	null
Martha	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	45	45	null	null
Mathew	null	null	null	null	null	65	null	65	null	null	null	null	null	null	null	null	null	null	null	null
Mayers	null	75	null	75	null	null	null	null	null	null	null	null	null	null	null	75	null	null	null	null
Mike	85	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	85
Peter	null	null	null	null	95	null	null	null	null	null	null	95	null	null	95	null	null	null	null	null
Rene	null	null	null	null	null	null	null	null	null	null	5	5	null	null	null	null	null	null	null	null
Thomas	null	null	null	null	null	null	null	null	null	null	null	null	105	105	null	null	null	null	105	null

Answer 1

You can't create a function that returns a dynamic number of columns without hard coding the list of columns at some place (either when calling the function or when defining the function).

The best (only?) option I see, is to create a view that is based on the content of the table. You can then call that view each time the table changes to create a new view with the updated list of columns.

create function create_clm_view()
 returns void
as
$body$
declare
  l_sql text;
  l_columns text;
  l_hobby_name text;
begin
  l_sql := 'select name, ';

  -- build the list of columns based on the distinct hobbies
  select string_agg(format('%L = any(hobbies) as %I', x.hobby_name, x.hobby_name), ', ' order by hobby_name)
    into l_columns
  from (
    select distinct t.hobby_name
    from split_clm s
      cross join unnest(regexp_split_to_array(s.hobby, '\s*,\s*')) as t(hobby_name)
  ) x;

  -- build the final SELECT of the view with the generated columns
  l_sql := l_sql || l_columns ||
  $sql$ from ( select name, regexp_split_to_array(hobby, '\s*,\s*') as hobbies from split_clm ) t $sql$ ;
  
  -- recreate the view
  execute 'drop view if exists clm_view cascade';
  execute 'create view clm_view as '||l_sql;    
end;
$body$ 
language plpgsql;

This will build a view that looks something like this:

select name, 
       'Athletics' = any(hobbies) as "Ahtletics",
       'Coding' = any(hobbies) as "Coding",  
       .....
from ( select name, regexp_split_to_array(hobby, '\s*,\s*') as hobbies from split_clm ) t;

Then run:

select create_normalized_view();

and to see the content:

select *
from clm_view;

Depending on how often the table is updated, you could call that function from a trigger or a cron job that updates the view in regular intervals.

Online example