Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rr4ak-00FXal-E4 for pgsql-general@arkaria.postgresql.org; Sun, 31 Mar 2024 23:27:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rr4ai-00DiP5-Mh for pgsql-general@arkaria.postgresql.org; Sun, 31 Mar 2024 23:27:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rr4ai-00DiOx-3X for pgsql-general@lists.postgresql.org; Sun, 31 Mar 2024 23:27:56 +0000 Received: from mail-pj1-x1029.google.com ([2607:f8b0:4864:20::1029]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rr4ad-007W1h-Bx for pgsql-general@lists.postgresql.org; Sun, 31 Mar 2024 23:27:54 +0000 Received: by mail-pj1-x1029.google.com with SMTP id 98e67ed59e1d1-2a1fdcc3659so2544217a91.2 for ; Sun, 31 Mar 2024 16:27:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711927671; x=1712532471; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Ef3Ji3nXHgxQtCZmGjQ8nWtTLdtyAo2T79L3ppluErk=; b=jC5cOu/Hpt11fg7BUP29utXYz28wMQu18FBnxuPGw3xzwL2pRgrZT5pf0Nqqs6fYJX JKCAhaqmawduqscWZXMl7wg6Q3Z1NoEKW0UO4Ws9rv7HgZNcTdJxx57gUtjckZehZMHt /Ru7KNM2LM3ozG+x+9xjQ8wvvFhmY1oUPJRip3CZnOiqkN0MgJM+K38Tw3FCxPl0SLi4 XJ1QX3EjA8nk+yDnUhcPuNW3JLDlkE7P3lba6iJOOHd7Kj9PDrhCFpzVUnnntLwBsA/4 wafD/d5bMIzTCBf+yeFjwHZIcNsKRr9OGXQWyvaM7xHf00n8Wb/oP7cVLMp79oD6O7TO X/vw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711927671; x=1712532471; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Ef3Ji3nXHgxQtCZmGjQ8nWtTLdtyAo2T79L3ppluErk=; b=BDTLjd0ROvDwfQ0Jmv8DE7+oaZ9wx/VHPI9VA07QIVshm0Iezq8XrSdOswQsqiPQ/x yzNF4m+7MCMIuT4Qzaf6BHiiOV6zwxmkki72peCB/lVYQf2pXU25XNKV6d66fJgHNws8 h0S1M800v9U7U2ccsD6A7ESpGMmBxetkXn9RoeRWmsGSPPvzga9w5rFOisw+md5WPvrl yIwZzLYWZUX6cgRZejcpXO4EScK0WjY6trPmE+8/hcgvj9ruxDzks5/h1AtHZaCiJ6xL MBvnsn9kpYkp6AMEhzn/akFlbmArmNR3aUKP4YS5Gjg9nLl9aPxLI8KtFHXfyFmL8wCk fMKQ== X-Gm-Message-State: AOJu0Yz8V99lVDfb42VEKkd0lf861fF/DBudPr7DJL53lyFVSWB8A4Cp d702/Ctj68R4o+RHoNgCHEDe/0SBYqHmppGMEQEKHdQyeCOdsa/9L84BNwIGegB4z0a0D06AsQG 2YE2Kg6Fv/Wy9S8Fvc4v8FLau3mzUKPIXE4I= X-Google-Smtp-Source: AGHT+IGF+3fvZ3PJXB1AwIpX6A8QthOz+xsmbpSgadpM1EnbT+B1bpCEQmNn0mU/BT3yBLmz8pBi7tP//4ruxRQ6+Bg= X-Received: by 2002:a17:90a:7e03:b0:299:5b95:cd7d with SMTP id i3-20020a17090a7e0300b002995b95cd7dmr5283418pjl.45.1711927671047; Sun, 31 Mar 2024 16:27:51 -0700 (PDT) MIME-Version: 1.0 References: <044955daeeedaab0cd8a1ba0d779de7b58eed8ba.camel@cybertec.at> In-Reply-To: <044955daeeedaab0cd8a1ba0d779de7b58eed8ba.camel@cybertec.at> From: Hans Schou Date: Mon, 1 Apr 2024 01:26:54 +0200 Message-ID: Subject: Re: Feature request: pg_get_tabledef(text) To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000b6cccc0614fd380d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b6cccc0614fd380d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Nov 22, 2023 at 5:09=E2=80=AFPM Laurenz Albe 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 ) =E2=86=92 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 :=3D '^(' || $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 =3D 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 :=3D 'COMMENT ON TABLE ' || v_schema || '."' || v_table || '" I= S ''' || tmp_text || ''';' || E'\n'; ELSE R_c :=3D ''; END IF; R :=3D '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 =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasdef), a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid =3D a.attcollation AND t.oid =3D a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity, a.attgenerated, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid =3D 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 :=3D R || ','; -- no comma after last column definition END IF; R :=3D R || E'\n' || ' "' || rec.attname || '" ' || rec.format_type; IF rec.attnotnull THEN R :=3D 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 :=3D R_c || 'COMMENT ON COLUMN ' || v_schema || '."' || v_table || '.' || rec.attname || '" IS ''' || tmp_text || ''';' || E'\n'; END IF; END LOOP; -- Columns -- Finalize table R :=3D R || E'\n' || ');' || E'\n'; -- Add COMMENTs IF LENGTH(R_c) > 0 THEN R :=3D 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 =3D i.indrelid AND conindid =3D i.indexrelid AND contype IN ('p','u','x')) WHERE c.oid =3D v_oid AND c.oid =3D i.indrelid AND i.indexrelid =3D= c2.oid ORDER BY i.indisprimary DESC, c2.relname LOOP R :=3D R || rec.indexdef || ';' || E'\n'; END LOOP; -- Index RETURN R; END; $_$; --=20 =F0=9D=95=B3=F0=9D=96=86=F0=9D=96=93=F0=9D=96=98 =F0=9D=95=BE=F0=9D=96=88= =F0=9D=96=8D=F0=9D=96=94=F0=9D=96=9A =E2=98=8F =E2=9E=81=E2=9E=81 =E2=9E=85=E2=9E=83 =E2=9E=87=E2=93=AA =E2=9E= =81=E2=93=AA --000000000000b6cccc0614fd380d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Nov 22, 2023 at 5:09=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

One of the problems is what should be included.
Indexes?=C2=A0 Policies?=C2=A0 Constraints?

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

Maybe including 'CASCADE'?

No unsurmountable questions, but someone w= ould have to come up with a
clear design and implement it.

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



CREATE OR REPLACE FUNCTION pg_get_tab= ledef(TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $_$
-- pg_get_tabl= edef ( text ) =E2=86=92 text
-- Reconstructs the underlying CREATE comma= nd for a table and objects related to a table.
-- (This is a decompiled = reconstruction, not the original text of the command.)
DECLARE
=C2=A0= =C2=A0 R TEXT; -- Return result
=C2=A0 =C2=A0 R_c TEXT; -- Comments res= ult, show after table definition
=C2=A0 =C2=A0 rec RECORD;
=C2=A0 =C2= =A0 tmp_text TEXT;
=C2=A0 =C2=A0 v_oid OID; -- Table object id
=C2=A0= =C2=A0 v_schema TEXT; -- Schema
=C2=A0 =C2=A0 v_table TEXT; -- Table na= me
=C2=A0 =C2=A0 rxrelname TEXT;
BEGIN
=C2=A0 =C2=A0 rxrelname := =3D =C2=A0'^(' || $1 || ')$';
=C2=A0 =C2=A0 -- Get oid a= nd schema
=C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 c.oid, n.n= spname, c.relname
=C2=A0 =C2=A0 INTO
=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_oi= d, v_schema, v_table
=C2=A0 =C2=A0 FROM pg_catalog.pg_class c
=C2=A0 = =C2=A0 LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamespace
= =C2=A0 =C2=A0 WHERE c.relname OPERATOR(pg_catalog.~) rxrelname COLLATE pg_c= atalog.default
=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND pg_catalog.pg_table_is_vi= sible(c.oid);
=C2=A0 =C2=A0 -- If table not found exit
=C2=A0 =C2=A0 = IF NOT FOUND THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- RAISE EXCEPTION 'Ta= ble % not found', $1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 RETURN '-- Tab= le not found: ''' || $1 || '''';
=C2=A0 =C2= =A0 END IF;
=C2=A0 =C2=A0 -- Table comment first, columns comment second= , init variable R_c,
=C2=A0 =C2=A0 SELECT obj_description(v_oid) INTO t= mp_text;
=C2=A0 =C2=A0 IF LENGTH(tmp_text) > 0 THEN
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 R_c :=3D 'COMMENT ON TABLE ' || v_schema || '.&qu= ot;' || v_table || '" IS ''' || tmp_text || '&= #39;';' || E'\n';
=C2=A0 =C2=A0 ELSE
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 R_c :=3D '';
=C2=A0 =C2=A0 END IF;
=C2=A0 =C2= =A0 R :=3D 'CREATE TABLE ' || v_schema || '."' || v_ta= ble || '" (';
=C2=A0 =C2=A0 -- Get columns
=C2=A0 =C2=A0= FOR rec IN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 a.attname,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 pg_catalog.format_type(a.atttypid, a.atttypmod),
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid,= true)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM pg_catalog.p= g_attrdef d
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE d.adre= lid =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasdef),
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.attnotnull,
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_cat= alog.pg_type t
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE c.o= id =3D a.attcollation AND t.oid =3D a.atttypid AND a.attcollation <> = t.typcollation) AS attcollation,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 a.attidentity,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.attgenera= ted,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.attnum
=C2=A0 =C2=A0= =C2=A0 =C2=A0 FROM pg_catalog.pg_attribute a
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 WHERE a.attrelid =3D v_oid AND a.attnum > 0 AND NOT a.attisdropped=C2=A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY a.attnum
=C2=A0 =C2=A0 LOOP
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 --RAISE NOTICE '% % %', rec.attnum, rec= .attname, rec.format_type;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF rec.attnum >= ; 1 THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 R :=3D R || ',= 9;; -- no comma after last column definition
=C2=A0 =C2=A0 =C2=A0 =C2=A0= END IF;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 R :=3D R || E'\n' || ' = =C2=A0 =C2=A0"' || rec.attname || '" ' || rec.format_= type;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF rec.attnotnull THEN
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 R :=3D R || ' NOT NULL';
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 END IF;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Comment on c= olumn
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT col_description( v_oid, rec.att= num) INTO tmp_text;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF LENGTH(tmp_text) >= 0 THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 R_c :=3D R_c || 'C= OMMENT ON COLUMN ' || v_schema || '."' || v_table || '= .' || rec.attname || '" IS ''' || tmp_text || '= ;'';' || E'\n';
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;<= br>=C2=A0 =C2=A0 END LOOP; -- Columns
=C2=A0 =C2=A0 -- Finalize table=C2=A0 =C2=A0 R :=3D R || E'\n' || ');' || E'\n';<= br>=C2=A0 =C2=A0 -- Add COMMENTs
=C2=A0 =C2=A0 IF LENGTH(R_c) > 0 THE= N
=C2=A0 =C2=A0 =C2=A0 =C2=A0 R :=3D R || R_c;
=C2=A0 =C2=A0 END IF;<= br>=C2=A0 =C2=A0 -- Index
=C2=A0 =C2=A0 FOR rec IN
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_catalog.p= g_get_indexdef(i.indexrelid, 0, true) AS indexdef
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_in= dex i
=C2=A0 =C2=A0 =C2=A0 =C2=A0 LEFT JOIN pg_catalog.pg_constraint con= ON (conrelid =3D i.indrelid AND conindid =3D i.indexrelid AND contype IN (= 'p','u','x'))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE = c.oid =3D v_oid AND c.oid =3D i.indrelid AND i.indexrelid =3D c2.oid
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY i.indisprimary DESC, c2.relname
=C2=A0= =C2=A0 LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 R :=3D R || rec.indexdef || = 9;;' || E'\n';
=C2=A0 =C2=A0 END LOOP; -- Index
=C2=A0 = =C2=A0 RETURN R;
END;
$_$;


<= br>--
=F0=9D= =95=B3=F0=9D=96=86=F0=9D=96=93=F0=9D=96=98=C2=A0=F0=9D=95=BE=F0=9D=96=88=F0= =9D=96=8D=F0=9D=96=94=F0=9D=96=9A
=E2=98=8F =E2=9E=81=E2=9E= =81 =E2=9E=85=E2=9E=83 =E2=9E=87=E2=93=AA =E2=9E=81=E2=93=AA
--000000000000b6cccc0614fd380d--