Clean up SQL emitted by psql/describe.c.

Fix assorted places that had not bothered with the convention of
prefixing catalog and function names with "pg_catalog.".  That
could possibly result in query failure when running with a nondefault
search_path.  Also fix two places that weren't quoting OID literals.
I think the latter hasn't mattered much since about 7.3, but it's still
a bad idea to be doing it in 99 places and not in 2 others.

Also remove a useless EXISTS sub-select that someone had stuck into
describeOneTableDetails' queries for child tables.  We just got the OID
out of pg_class, so I hardly see how checking that it exists in pg_class
was doing anything helpful.

In passing, try to improve the emitted formatting of a couple of
these queries, though I didn't work really hard on that.  And merge
unnecessarily duplicative coding in some other places.

Much of this was new in HEAD, but some was quite old; back-patch
as appropriate.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 5867d8abb7a3947dca5c04a4efbe28bb34e37ae9..b2b0518c3bd95235f625243dfcfb3e1c78b9f73e 100644 (file)
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1302,8 +1302,8 @@ describeOneTableDetails(const char *schemaname,
        appendPQExpBuffer(&buf, ",\n  NULL AS indexdef");
    if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
        appendPQExpBuffer(&buf, ",\n  CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
-                         "  '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) ||  ' ' || quote_literal(option_value)  FROM "
-                         "  pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
+                         "  '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) ||  ' ' || pg_catalog.quote_literal(option_value)  FROM "
+                         "  pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
    else
        appendPQExpBuffer(&buf, ",\n  NULL AS attfdwoptions");
    if (verbose)
@@ -1667,7 +1667,7 @@ describeOneTableDetails(const char *schemaname,
                          "\n a.attnum=d.refobjsubid)"
               "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
             "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
-                         "\n AND d.objid=%s"
+                         "\n AND d.objid='%s'"
                          "\n AND d.deptype='a'",
                          oid);
 
@@ -2120,13 +2120,13 @@ describeOneTableDetails(const char *schemaname,
            /* Footer information about foreign table */
            printfPQExpBuffer(&buf,
                              "SELECT s.srvname,\n"
-                             "       array_to_string(ARRAY(SELECT "
-                             "       quote_ident(option_name) ||  ' ' || "
-                             "       quote_literal(option_value)  FROM "
-                           "       pg_options_to_table(ftoptions)),  ', ') "
+                             "  pg_catalog.array_to_string(ARRAY(\n"
+                             "    SELECT pg_catalog.quote_ident(option_name)"
+                             " || ' ' || pg_catalog.quote_literal(option_value)\n"
+                             "    FROM pg_catalog.pg_options_to_table(ftoptions)),  ', ')\n"
                              "FROM pg_catalog.pg_foreign_table f,\n"
                              "     pg_catalog.pg_foreign_server s\n"
-                             "WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
+                             "WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
                              oid);
            result = PSQLexec(buf.data, false);
            if (!result)
@@ -2532,13 +2532,13 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
 
        printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
                "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
-                         "FROM pg_db_role_setting AS s\n"
-                  "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
-                         "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n");
+                         "FROM pg_catalog.pg_db_role_setting s\n"
+                  "LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
+                         "LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n");
        havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
-                                      NULL, "pg_roles.rolname", NULL, NULL);
+                                      NULL, "r.rolname", NULL, NULL);
        processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
-                             NULL, "pg_database.datname", NULL, NULL);
+                             NULL, "d.datname", NULL, NULL);
        appendPQExpBufferStr(&buf, "ORDER BY role, database;");
    }
    else
@@ -2753,13 +2753,13 @@ listLanguages(const char *pattern, bool verbose, bool showSystem)
    {
        appendPQExpBuffer(&buf,
                          ",\n       NOT l.lanispl AS \"%s\",\n"
-                         "       l.lanplcallfoid::regprocedure AS \"%s\",\n"
-                  "       l.lanvalidator::regprocedure AS \"%s\",\n       ",
+                         "       l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
+                         "       l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n       ",
                          gettext_noop("Internal Language"),
                          gettext_noop("Call Handler"),
                          gettext_noop("Validator"));
        if (pset.sversion >= 90000)
-           appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n       ",
+           appendPQExpBuffer(&buf, "l.laninline::pg_catalog.regprocedure AS \"%s\",\n       ",
                              gettext_noop("Inline Handler"));
        printACLColumn(&buf, "l.lanacl");
    }
@@ -3795,10 +3795,10 @@ listForeignDataWrappers(const char *pattern, bool verbose)
        printACLColumn(&buf, "fdwacl");
        appendPQExpBuffer(&buf,
                          ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
-                         "  '(' || array_to_string(ARRAY(SELECT "
-                         "  quote_ident(option_name) ||  ' ' || "
-                         "  quote_literal(option_value)  FROM "
-                         "  pg_options_to_table(fdwoptions)),  ', ') || ')' "
+                         "  '(' || pg_catalog.array_to_string(ARRAY(SELECT "
+                         "  pg_catalog.quote_ident(option_name) ||  ' ' || "
+                         "  pg_catalog.quote_literal(option_value)  FROM "
+                         "  pg_catalog.pg_options_to_table(fdwoptions)),  ', ') || ')' "
                          "  END AS \"%s\"",
                          gettext_noop("FDW Options"));
 
@@ -3873,10 +3873,10 @@ listForeignServers(const char *pattern, bool verbose)
                          "  s.srvtype AS \"%s\",\n"
                          "  s.srvversion AS \"%s\",\n"
                          "  CASE WHEN srvoptions IS NULL THEN '' ELSE "
-                         "  '(' || array_to_string(ARRAY(SELECT "
-                         "  quote_ident(option_name) ||  ' ' || "
-                         "  quote_literal(option_value)  FROM "
-                         "  pg_options_to_table(srvoptions)),  ', ') || ')' "
+                         "  '(' || pg_catalog.array_to_string(ARRAY(SELECT "
+                         "  pg_catalog.quote_ident(option_name) ||  ' ' || "
+                         "  pg_catalog.quote_literal(option_value)  FROM "
+                         "  pg_catalog.pg_options_to_table(srvoptions)),  ', ') || ')' "
                          "  END AS \"%s\",\n"
                          "  d.description AS \"%s\"",
                          gettext_noop("Type"),
@@ -3891,7 +3891,7 @@ listForeignServers(const char *pattern, bool verbose)
 
    if (verbose)
        appendPQExpBuffer(&buf,
-                         "LEFT JOIN pg_description d\n       "
+                         "LEFT JOIN pg_catalog.pg_description d\n       "
                          "ON d.classoid = s.tableoid AND d.objoid = s.oid "
                          "AND d.objsubid = 0\n");
 
@@ -3944,10 +3944,10 @@ listUserMappings(const char *pattern, bool verbose)
    if (verbose)
        appendPQExpBuffer(&buf,
                          ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
-                         "  '(' || array_to_string(ARRAY(SELECT "
-                         "  quote_ident(option_name) ||  ' ' || "
-                         "  quote_literal(option_value)  FROM "
-                         "  pg_options_to_table(umoptions)),  ', ') || ')' "
+                         "  '(' || pg_catalog.array_to_string(ARRAY(SELECT "
+                         "  pg_catalog.quote_ident(option_name) ||  ' ' || "
+                         "  pg_catalog.quote_literal(option_value)  FROM "
+                         "  pg_catalog.pg_options_to_table(umoptions)),  ', ') || ')' "
                          "  END AS \"%s\"",
                          gettext_noop("FDW Options"));
 
@@ -4004,10 +4004,10 @@ listForeignTables(const char *pattern, bool verbose)
    if (verbose)
        appendPQExpBuffer(&buf,
                          ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
-                         "  '(' || array_to_string(ARRAY(SELECT "
-                         "  quote_ident(option_name) ||  ' ' || "
-                         "  quote_literal(option_value)  FROM "
-                         "  pg_options_to_table(ftoptions)),  ', ') || ')' "
+                         "  '(' || pg_catalog.array_to_string(ARRAY(SELECT "
+                         "  pg_catalog.quote_ident(option_name) ||  ' ' || "
+                         "  pg_catalog.quote_literal(option_value)  FROM "
+                         "  pg_catalog.pg_options_to_table(ftoptions)),  ', ') || ')' "
                          "  END AS \"%s\",\n"
                          "  d.description AS \"%s\"",
                          gettext_noop("FDW Options"),