我需要根据tbl1中不同类型列的不同值动态创建一个表。请让我知道是否可以使用下面的光标和函数。
创建 tbl1 带列 id , type 以及 value .
创建 tbl2 带列 id , gender .
函数使用游标将值检索到最终表中(创建临时表 dual 用于检查正在传递的值。）

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;