I'm newby to PG/GP.
I need write function returning a full Greenplum table definition (like in dbeaver DDL tab). In my system functions get_table_def, catalog.pg_gettabledef does not exist, or unavailable to me.
I don't know why, maybe insufficient user rights.
I already find a function, which returns setof text with table structure definition:
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;
Now I want to write another similar function, which returns setof text based on select with 3 rows dataset (other part DDL of Greenplum table which consist partitions info):
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;
But it returns 3 rows with null values, instead of setof text.
Where did I go wrong?
