Thread: Feature request: pg_get_tabledef(text)

Feature request: pg_get_tabledef(text)

From
Hans Schou
Date:
Hi

Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful with a pg_get_tabledef() to get a full description of how a table is defined.

Currently the table definition can be extracted with the command:

  pg_dump -d foo --schema-only --table=bar | egrep '^[^-]'

The psql command '\d bar' gives some of the same information but it is not in a format where it can be used to create a table.

Extra:
With the pg_get_tabledef() function in place it is very close to be possible to implement pg_dump() within the system. So instead of running:
  pg_dump -d foo
one could just run:
  psql -d foo -c 'SELECT pg_dump()'

The function could also be called from within a programming language like Java/PHP.

pg_dump has a lot of options where some of them could be parameters to the pg_dump() function. If using a cloud or other webservice this will be an easy way to make an extra backup.

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

Re: Feature request: pg_get_tabledef(text)

From
Ron Johnson
Date:
On Wed, Nov 22, 2023 at 10:41 AM Hans Schou <hans.schou@gmail.com> wrote:
Hi

Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful with a pg_get_tabledef() to get a full description of how a table is defined.
 
Because there's already pg_get_viewdef(), pg_get_functiondef(), pg_get_constraintdef(), pg_indexes.indexdef and ways to get all GRANT and REVOKE privs, any pg_get_tabledef() would just be the CREATE TABLE statement without PRIMARY KEY or FOREIGN KEY clauses.

Re: Feature request: pg_get_tabledef(text)

From
Laurenz Albe
Date:
On Wed, 2023-11-22 at 16:41 +0100, Hans Schou wrote:
> Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful with a pg_get_tabledef() to get a full
descriptionof how a table is defined. 

This has been requested before:
https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com

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

Another problem is that while a function or view definition is a single
SQL statement, a table definition could consist of more than a single
statement, depending on the answer to the previous question.

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

Yours,
Laurenz Albe



Re: Feature request: pg_get_tabledef(text)

From
Erik Wienhold
Date:
On 2023-11-22 16:41 +0100, Hans Schou wrote:
> Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful
> with a pg_get_tabledef() to get a full description of how a table is
> defined.

There's already a discussion on that topic[1].  But I don't know about
the current state of development.

[1] https://www.postgresql.org/message-id/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com

-- 
Erik



Re: Feature request: pg_get_tabledef(text)

From
Ron Johnson
Date:
On Wed, Nov 22, 2023 at 11:09 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2023-11-22 at 16:41 +0100, Hans Schou wrote:
> Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful with a pg_get_tabledef() to get a full description of how a table is defined.

This has been requested before:
https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com

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

Another problem is that while a function or view definition is a single
SQL statement, a table definition could consist of more than a single
statement, depending on the answer to the previous question.

No unsurmountable questions, but someone would have to come up with a
clear design and implement it.
 
Because you can already get all the other DDL relevant to creating a table (like indices, primary and foreign keys, grants), ISTM that just a plain CREATE TABLE with column DEFAULT clauses is what pg_get_tabledef() should create.

A comprehensive "recreate everything related to a table" function should be left as an exercise for the DBA.

Re: Feature request: pg_get_tabledef(text)

From
Hans Schou
Date:
On Wed, Nov 22, 2023 at 5:09 PM Laurenz Albe <laurenz.albe@cybertec.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.


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;
$_$;



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