我是PG/GP的纽比。我需要write函数返回完整的Greenplum表定义（如dbeaver DDL选项卡）。在我的系统函数get_table_def中，catalog.pg_gettabledef不存在，或对我不可用。我不知道为什么，也许是用户权限不够。
我已经找到了一个函数，它返回setof text和表结构定义：

CREATE OR REPLACE FUNCTION describe_table(p_schema_name varchar, p_table_name varchar)
    RETURNS SETOF text
    LANGUAGE plpgsql
    VOLATILE
AS $$
    
DECLARE
    v_table_ddl   text;
    column_record record;
    table_rec record;
    constraint_rec record;
    firstrec boolean;
BEGIN
    FOR table_rec IN
        SELECT c.relname, c.oid FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE relkind = 'r'
                AND n.nspname = p_schema_name
                AND relname~ ('^('||p_table_name||')$')
          ORDER BY c.relname
    LOOP
        FOR column_record IN
            SELECT
                b.nspname as schema_name,
                b.relname as table_name,
                a.attname as column_name,
                pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
                CASE WHEN
                    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                     FROM pg_catalog.pg_attrdef d
                     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                    'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                                  FROM pg_catalog.pg_attrdef d
                                  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
                ELSE
                    ''
                END as column_default_value,
                CASE WHEN a.attnotnull = true THEN
                    'NOT NULL'
                ELSE
                    'NULL'
                END as column_not_null,
                a.attnum as attnum,
                e.max_attnum as max_attnum
            FROM
                pg_catalog.pg_attribute a
                INNER JOIN
                 (SELECT c.oid,
                    n.nspname,
                    c.relname
                  FROM pg_catalog.pg_class c
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.oid = table_rec.oid
                  ORDER BY 2, 3) b
                ON a.attrelid = b.oid
                INNER JOIN
                 (SELECT
                      a.attrelid,
                      max(a.attnum) as max_attnum
                  FROM pg_catalog.pg_attribute a
                  WHERE a.attnum > 0
                    AND NOT a.attisdropped
                  GROUP BY a.attrelid) e
                ON a.attrelid=e.attrelid
            WHERE a.attnum > 0
              AND NOT a.attisdropped
            ORDER BY a.attnum
        LOOP
            IF column_record.attnum = 1 THEN
                v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
            ELSE
                v_table_ddl:=v_table_ddl||',';
            END IF;

            IF column_record.attnum <= column_record.max_attnum THEN
                v_table_ddl:=v_table_ddl||chr(10)||
                         '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
            END IF;
        END LOOP;

        firstrec := TRUE;
        FOR constraint_rec IN
            SELECT conname, pg_get_constraintdef(c.oid) as constrainddef
                FROM pg_constraint c
                    WHERE conrelid=(
                        SELECT attrelid FROM pg_attribute
                        WHERE attrelid = (
                            SELECT oid FROM pg_class WHERE relname = table_rec.relname
                                AND relnamespace = (SELECT ns.oid FROM pg_namespace ns WHERE ns.nspname = p_schema_name)
                        ) AND attname='tableoid'
                    )
        LOOP
            v_table_ddl:=v_table_ddl||','||chr(10);
            v_table_ddl:=v_table_ddl||'CONSTRAINT '||constraint_rec.conname;
            v_table_ddl:=v_table_ddl||chr(10)||'    '||constraint_rec.constrainddef;
            firstrec := FALSE;
        END LOOP;
        v_table_ddl:=v_table_ddl||');';
        RETURN NEXT v_table_ddl;
    END LOOP;
END;

$$
EXECUTE ON ANY;

字符串
现在我想编写另一个类似的函数，它基于select返回setof text，其中包含3行数据集（Greenplum表的其他部分DDL，其中包含分区信息）：

CREATE OR REPLACE FUNCTION kasudra_dds.describe_table1(p_schema_name varchar, p_table_name varchar)
    RETURNS SETOF text
    LANGUAGE plpgsql
    VOLATILE
AS $$
    
DECLARE
    v_table_ddl   text;
    column_record record;
    table_rec record;
    constraint_rec record;
    firstrec boolean;
BEGIN
    FOR table_rec IN
        select
            n.nspname as schemaname,
            c.relname as tablename,
            a.attname as columnname,
            p.parlevel as partitionlevel,
            p.i + 1 as position_in_partition_key
        from
            pg_namespace n,
            pg_class c,
            pg_attribute a,
            (
            select
                p.parrelid,
                p.parlevel,
                p.paratts[i.i] as attnum,
                i.i
            from
                pg_partition p,
                generate_series(0,
                (
                select
                    max(array_upper(pg_partition.paratts, 1)) as max
                from
                    pg_partition)) i(i)
            where
                p.paratts[i.i] is not null) p
        where
            p.parrelid = c.oid      and 
            c.relnamespace = n.oid  and 
            p.attnum = a.attnum     and 
            a.attrelid = c.oid      and 
            n.nspname = $1          and 
            c.relname = $2
        order by
            p.parlevel
    LOOP
        v_table_ddl:=v_table_ddl||'PARTITION BY LIST ('||table_rec.columnname||')'||chr(10);
        RETURN NEXT v_table_ddl;
    END LOOP;
END;

$$
EXECUTE ON ANY;

型
但它返回3行null值，而不是setof text。
firsttrouble
我哪里做错了？