diff options
Diffstat (limited to 'src/bin/psql/describe.c')
| -rw-r--r-- | src/bin/psql/describe.c | 156 |
1 files changed, 118 insertions, 38 deletions
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index fd8ebee8cd3..bb9c6d6bebe 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1479,6 +1479,7 @@ describeOneTableDetails(const char *schemaname, bool rowsecurity; bool forcerowsecurity; bool hasoids; + bool ispartition; Oid tablespace; char *reloptions; char *reloftype; @@ -1502,7 +1503,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " - "false AS relhasoids, %s, c.reltablespace, " + "false AS relhasoids, c.relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident, am.amname\n" "FROM pg_catalog.pg_class c\n " @@ -1515,12 +1516,29 @@ describeOneTableDetails(const char *schemaname, : "''"), oid); } + else if (pset.sversion >= 100000) + { + printfPQExpBuffer(&buf, + "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " + "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " + "c.relhasoids, c.relispartition, %s, c.reltablespace, " + "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " + "c.relpersistence, c.relreplident\n" + "FROM pg_catalog.pg_class c\n " + "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" + "WHERE c.oid = '%s';", + (verbose ? + "pg_catalog.array_to_string(c.reloptions || " + "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" + : "''"), + oid); + } else if (pset.sversion >= 90500) { printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " - "c.relhasoids, %s, c.reltablespace, " + "c.relhasoids, false as relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident\n" "FROM pg_catalog.pg_class c\n " @@ -1537,7 +1555,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false as relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence, c.relreplident\n" "FROM pg_catalog.pg_class c\n " @@ -1554,7 +1572,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false as relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " "c.relpersistence\n" "FROM pg_catalog.pg_class c\n " @@ -1571,7 +1589,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace, " + "false as relispartition, %s, c.reltablespace, " "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n" "FROM pg_catalog.pg_class c\n " "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" @@ -1587,7 +1605,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " "c.relhastriggers, false, false, c.relhasoids, " - "%s, c.reltablespace\n" + "false as relispartition, %s, c.reltablespace\n" "FROM pg_catalog.pg_class c\n " "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" "WHERE c.oid = '%s';", @@ -1602,7 +1620,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "%s, reltablespace\n" + "false as relispartition, %s, reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", (verbose ? "pg_catalog.array_to_string(reloptions, E', ')" : "''"), @@ -1613,7 +1631,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', reltablespace\n" + "false as relispartition, '', reltablespace\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1622,7 +1640,7 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT relchecks, relkind, relhasindex, relhasrules, " "reltriggers <> 0, false, false, relhasoids, " - "'', ''\n" + "false as relispartition, '', ''\n" "FROM pg_catalog.pg_class WHERE oid = '%s';", oid); } @@ -1647,20 +1665,21 @@ describeOneTableDetails(const char *schemaname, tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0; tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0; tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0; + tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0; tableinfo.reloptions = (pset.sversion >= 80200) ? - pg_strdup(PQgetvalue(res, 0, 8)) : NULL; + pg_strdup(PQgetvalue(res, 0, 9)) : NULL; tableinfo.tablespace = (pset.sversion >= 80000) ? - atooid(PQgetvalue(res, 0, 9)) : 0; + atooid(PQgetvalue(res, 0, 10)) : 0; tableinfo.reloftype = (pset.sversion >= 90000 && - strcmp(PQgetvalue(res, 0, 10), "") != 0) ? - pg_strdup(PQgetvalue(res, 0, 10)) : NULL; + strcmp(PQgetvalue(res, 0, 11), "") != 0) ? + pg_strdup(PQgetvalue(res, 0, 11)) : NULL; tableinfo.relpersistence = (pset.sversion >= 90100) ? - *(PQgetvalue(res, 0, 11)) : 0; + *(PQgetvalue(res, 0, 12)) : 0; tableinfo.relreplident = (pset.sversion >= 90400) ? - *(PQgetvalue(res, 0, 12)) : 'd'; + *(PQgetvalue(res, 0, 13)) : 'd'; if (pset.sversion >= 120000) - tableinfo.relam = PQgetisnull(res, 0, 13) ? - (char *) NULL : pg_strdup(PQgetvalue(res, 0, 13)); + tableinfo.relam = PQgetisnull(res, 0, 14) ? + (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); else tableinfo.relam = NULL; PQclear(res); @@ -2394,12 +2413,36 @@ describeOneTableDetails(const char *schemaname, if (tableinfo.hastriggers || tableinfo.relkind == RELKIND_PARTITIONED_TABLE) { - printfPQExpBuffer(&buf, - "SELECT conname,\n" - " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n" - "FROM pg_catalog.pg_constraint r\n" - "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;", - oid); + if (pset.sversion >= 120000 && + (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) + { + /* + * Put the constraints defined in this table first, followed + * by the constraints defined in ancestor partitioned tables. + */ + printfPQExpBuffer(&buf, + "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n" + " conname,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n" + " conrelid::pg_catalog.regclass AS ontable\n" + " FROM pg_catalog.pg_constraint,\n" + " pg_catalog.pg_partition_ancestors('%s')\n" + " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" + "ORDER BY sametable DESC, conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT true as sametable, conname,\n" + " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n" + " conrelid::pg_catalog.regclass AS ontable\n" + "FROM pg_catalog.pg_constraint r\n" + "WHERE r.conrelid = '%s' AND r.contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2408,13 +2451,28 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_sametable = PQfnumber(result, "sametable"), + i_conname = PQfnumber(result, "conname"), + i_condef = PQfnumber(result, "condef"), + i_ontable = PQfnumber(result, "ontable"); + printTableAddFooter(&cont, _("Foreign-key constraints:")); for (i = 0; i < tuples; i++) { - /* untranslated constraint name and def */ - printfPQExpBuffer(&buf, " \"%s\" %s", - PQgetvalue(result, i, 0), - PQgetvalue(result, i, 1)); + /* + * Print untranslated constraint name and definition. Use + * a "TABLE tab" prefix when the constraint is defined in + * a parent partitioned table. + */ + if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0) + printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); + else + printfPQExpBuffer(&buf, " \"%s\" %s", + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } @@ -2422,15 +2480,33 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print incoming foreign-key references (none if no triggers) */ - if (tableinfo.hastriggers) + /* print incoming foreign-key references */ + if (tableinfo.hastriggers || + tableinfo.relkind == RELKIND_PARTITIONED_TABLE) { - printfPQExpBuffer(&buf, - "SELECT conname, conrelid::pg_catalog.regclass,\n" - " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n" - "FROM pg_catalog.pg_constraint c\n" - "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;", - oid); + if (pset.sversion >= 120000) + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint c\n" + " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n" + " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n" + " AND contype = 'f' AND conparentid = 0\n" + "ORDER BY conname;", + oid, oid); + } + else + { + printfPQExpBuffer(&buf, + "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" + " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" + " FROM pg_catalog.pg_constraint\n" + " WHERE confrelid = %s AND contype = 'f'\n" + "ORDER BY conname;", + oid); + } + result = PSQLexec(buf.data); if (!result) goto error_return; @@ -2439,13 +2515,17 @@ describeOneTableDetails(const char *schemaname, if (tuples > 0) { + int i_conname = PQfnumber(result, "conname"), + i_ontable = PQfnumber(result, "ontable"), + i_condef = PQfnumber(result, "condef"); + printTableAddFooter(&cont, _("Referenced by:")); for (i = 0; i < tuples; i++) { printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", - PQgetvalue(result, i, 1), - PQgetvalue(result, i, 0), - PQgetvalue(result, i, 2)); + PQgetvalue(result, i, i_ontable), + PQgetvalue(result, i, i_conname), + PQgetvalue(result, i, i_condef)); printTableAddFooter(&cont, buf.data); } |