Dynamically create a table with row values as columns

Question

I need to create a table dynamically based on different values comes from distinct values of type column from tbl1. Please let me know if it is possible using cursor and function below.

Creating tbl1 with a columns id, type and value.
Creating tbl2 with a columns id ,gender.
Function retrieving values into final table using cursors. (creating temp table dual for checking what values are being passing.)

create table tbl1 (
    id int not null,
    type varchar not null,
    value varchar
);

create table tbl2 (
    id int not null,
    gender varchar not null
);

commit;

insert into tbl1 values (1,'name','A'),(2,'name','B'),(1,'age','10'),(3,'name','C');
insert into tbl2 values (1,'M'),(2,'F');

commit;

--the below crosstab didn't work
SELECT id
     , COALESCE(name, max(name) OVER w)
     , COALESCE(age, max(age) OVER w)
FROM   crosstab(
   'SELECT id::text || row_number() OVER (PARTITION BY id, type ORDER BY value) * -1 AS ext_id
         , id, type, value
    FROM   tbl1
    ORDER  BY ext_id, type, value'
   ,$$VALUES ('name'::text), ('age') $$
   ) AS ct (xid text, id int, name text, age int)
WINDOW w AS (PARTITION BY id);

-- FUNCTION: SELECT public.Finaltblfunc1()

-- DROP FUNCTION public.Finaltblfunc1();

CREATE OR REPLACE FUNCTION public.Finaltblfunc1()
    RETURNS setof refcursor 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
DECLARE

/* Declare variables. */
    P_id NUMERIC(10,0);
    P_name VARCHAR(20);
    P_age VARCHAR(3);
    P_gender VARCHAR(1);
    v_leng INTEGER;
    v_leng1 INTEGER;
    v_j bigint;
  v_k VARCHAR(10);

/* Declare cursors. */
    sourcerefcur1 CURSOR FOR SELECT t1.id,
                     (CASE WHEN t1.type = 'name' THEN t1.value ELSE '' END) AS name,
                     (CASE WHEN t1.type = 'age' THEN t1.value ELSE '' END) AS age,
                     t2.gender
                     FROM tbl1 t1 full outer join tbl2 t2 on t1.id = t2.id;
    temprefcur1 CURSOR FOR SELECT distinct t1.type FROM tbl1 t1;
    --targetrefcur2 REFCURSOR;

/* Declare SQL string variables. */
  SQL_STR1 VARCHAR(200):= 'SELECT count(distinct table_name) 
    FROM information_schema.tables
    WHERE table_schema = ''public'' and table_name = ''finaltable''';

/* Declare error handling variables. */
    err_num TEXT;
    err_msg TEXT;

BEGIN
    /* tables exists or not */
    EXECUTE SQL_STR1 INTO v_j;
    RAISE INFO 'Finaltable check:%',v_j;

    IF (v_j = 0) THEN
            --Creating a Final Table
         create table finaltable (
         id NUMERIC(10,0),
         name varchar(50),
         age varchar(3),
         gender varchar(1)
         );
    ELSE
     --do nothing
    END IF;

    v_leng := 0;
    --open the cursor temprefcur1
    OPEN temprefcur1;

    loop
        --fetch next from temprefcur1 into respective parameters;
        fetch next from temprefcur1 into v_k;

        -- exit when no more row to fetch
        EXIT WHEN NOT FOUND;

        v_leng = v_leng +1;     
        raise notice 'v_k:%',v_k;
        raise notice 'v_leng:%',v_leng;     

    end loop;

    return next temprefcur1;

     -- Close the cursor
     CLOSE temprefcur1;

    v_leng1 := 0;
    --open the cursor sourcerefcur1
    OPEN sourcerefcur1;

    loop
         --fetch next from sourcerefcur1 into respective parameters;
         fetch next from sourcerefcur1 into P_id,P_name,P_age,P_gender;

         -- exit when no more row to fetch
         EXIT WHEN NOT FOUND;

         v_leng1 = v_leng1 +1;
         RAISE INFO 'P_id: %',P_id; --, E'\n';
         RAISE INFO 'P_name: %',P_name; --, E'\n';
         RAISE INFO 'P_age: %',P_age; --, E'\n';         
         RAISE INFO 'P_gender: %',P_gender; --, E'\n';       
         RAISE INFO 'length: %',v_leng1; --, E'\n';

         raise notice 'step insert';
         insert into finaltable values (P_id,P_name,P_age,P_gender);
         insert into dual values (P_id),(P_name),(P_age),(P_gender);
         insert into dual values (v_leng1);
         raise notice 'after step insert';

    end loop;

    return next sourcerefcur1;

    --close sourcerefcur1
    close sourcerefcur1;

EXCEPTION
  WHEN OTHERS THEN
    err_num := SQLSTATE;
    err_msg := SUBSTR(SQLERRM,1,100);
    RAISE INFO 'Error: % %', err_num, err_msg;
END;
$BODY$;

ALTER FUNCTION public.Finaltblfunc1()
    OWNER TO postgres;

Answer 1

You may have been (hugely) over-complicating things. This should basically do it all:

CREATE TABLE IF NOT EXISTS finaltable (
  id     bigint
, name   text
, age    int
, gender text
);


INSERT INTO finaltable(id, name, age, gender)
SELECT *
FROM   crosstab(
 $$SELECT id, type    , value  FROM tbl1
   UNION ALL
   SELECT id, 'gender', gender FROM tbl2
   ORDER  BY id$$
,$$VALUES ('name'), ('age'), ('gender')$$
   ) AS ct (id int, name text, age int, gender text);

Result:

id | name |  age | gender
-: | :--- | ---: | :-----
 1 | A    |   10 | M     
 2 | B    | null | F     
 3 | C    | null | null  

db<>fiddle here

Not sure what the added COALESCE was supposed to achieve. I stripped it.

Basics:

• PostgreSQL Crosstab Query

Aside: age as table column is subject to bitrot. Store birthdays instead (or similar).