How to make a PostgreSQL function return a recordset with a variable number of columns?

Question

PostgreSQL

Good afternoon! The database has a crossview view that returns data in the following form:
org_id name value
1 index 656569
2 index 656569
1 opf_org_full MUP
2 opf_org_full FSUE
1 name_org_full Test1
2 name_org_full Test2
Where org_id - organization id, name - organization attribute name, value - attribute value .
I need to get data like this:
org_id index opf_org_full name_org_full
1 656569 MUP Test1
2 656022 FSUE Test2
To do this, I wrote a function in Postgres:

CREATE FUNCTION pivotcode(tablename anyelement, rowc character varying, colc character varying, cellc character varying, celldatatype character varying) RETURNS SETOF anyelement
    LANGUAGE plpgsql
    AS $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
    sql1 varchar;
    sql2 varchar;
begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||pg_typeof(tablename)||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'select * from crosstab (
 ''select '||rowc||','||colc||','||cellc||' from '||pg_typeof(tablename)||' order by 1,2'',
 ''select distinct '||colc||' from '||pg_typeof(tablename)||' order by 1''
 )
 as newtable (
 '||rowc||' int,'||columnlist||'
 );';

 
RETURN QUERY EXECUTE dynsql2;

end
$$;

And I'm trying to execute it like this:

select * from pivotcode(NULL::crossview,'org_id','name','value','text');

I get the error:
ERROR: structure of query does not match function result type
DETAIL: Returned type text does not match expected type character varying(1024) in column 2.
CONTEXT: PL/pgSQL function "pivotcode" line 20 at RETURN QUERY
What is the return type information to be provided in this case?

Answer 1

The problem is not in what pivotcode returns, but in the fact that a non-deterministic table is obtained inside it. And you can only rotate tables whose structure is known in advance to determine the type of future columns / rows

Answer 2

Look towards the hstore extension.