Re: Feature request: pg_get_tabledef(text)

On Wed, Nov 22, 2023 at 5:09 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

>
> One of the problems is what should be included.
> Indexes? Policies? Constraints?
>

A high limit could be all objects except data.
All the objects which would be deleted by a 'DROP TABLE'.

Maybe including 'CASCADE'?

No unsurmountable questions, but someone would have to come up with a
> clear design and implement it.
>

I gave it a try.
I'm not that skilled in plpgsql so there is probably room for improvement.

https://github.com/chlordk/pg_get_tabledef

For your convenience here is a copy/paste of the function.

CREATE OR REPLACE FUNCTION pg_get_tabledef(TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $_$
-- pg_get_tabledef ( text ) → text
-- Reconstructs the underlying CREATE command for a table and objects
related to a table.
-- (This is a decompiled reconstruction, not the original text of the
command.)
DECLARE
R TEXT; -- Return result
R_c TEXT; -- Comments result, show after table definition
rec RECORD;
tmp_text TEXT;
v_oid OID; -- Table object id
v_schema TEXT; -- Schema
v_table TEXT; -- Table name
rxrelname TEXT;
BEGIN
rxrelname := '^(' || $1 || ')$';
-- Get oid and schema
SELECT
c.oid, n.nspname, c.relname
INTO
v_oid, v_schema, v_table
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) rxrelname COLLATE
pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid);
-- If table not found exit
IF NOT FOUND THEN
-- RAISE EXCEPTION 'Table % not found', $1;
RETURN '-- Table not found: ''' || $1 || '''';
END IF;
-- Table comment first, columns comment second, init variable R_c,
SELECT obj_description(v_oid) INTO tmp_text;
IF LENGTH(tmp_text) > 0 THEN
R_c := 'COMMENT ON TABLE ' || v_schema || '."' || v_table || '" IS
''' || tmp_text || ''';' || E'\n';
ELSE
R_c := '';
END IF;
R := 'CREATE TABLE ' || v_schema || '."' || v_table || '" (';
-- Get columns
FOR rec IN
SELECT
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c,
pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated,
a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = v_oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
--RAISE NOTICE '% % %', rec.attnum, rec.attname, rec.format_type;
IF rec.attnum > 1 THEN
R := R || ','; -- no comma after last column definition
END IF;
R := R || E'\n' || ' "' || rec.attname || '" ' ||
rec.format_type;
IF rec.attnotnull THEN
R := R || ' NOT NULL';
END IF;
-- Comment on column
SELECT col_description( v_oid, rec.attnum) INTO tmp_text;
IF LENGTH(tmp_text) > 0 THEN
R_c := R_c || 'COMMENT ON COLUMN ' || v_schema || '."' ||
v_table || '.' || rec.attname || '" IS ''' || tmp_text || ''';' || E'\n';
END IF;
END LOOP; -- Columns
-- Finalize table
R := R || E'\n' || ');' || E'\n';
-- Add COMMENTs
IF LENGTH(R_c) > 0 THEN
R := R || R_c;
END IF;
-- Index
FOR rec IN
SELECT
pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid
AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = v_oid AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname
LOOP
R := R || rec.indexdef || ';' || E'\n';
END LOOP; -- Index
RETURN R;
END;
$_$;

--
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪