Skip to content

Commit a464d77

Browse files
tglsfdctglsfdc
committed
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.
1 parent dfd0919 commit a464d77

File tree

1 file changed

+33
-33
lines changed

1 file changed

+33
-33
lines changed

‎src/bin/psql/describe.c

Lines changed: 33 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1372,8 +1372,8 @@ describeOneTableDetails(const char *schemaname,
13721372
appendPQExpBufferStr(&buf, ",\n NULL AS indexdef");
13731373
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
13741374
appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1375-
" '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM "
1376-
" pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1375+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
1376+
" pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
13771377
else
13781378
appendPQExpBufferStr(&buf, ",\n NULL AS attfdwoptions");
13791379
if (verbose)
@@ -1723,7 +1723,7 @@ describeOneTableDetails(const char *schemaname,
17231723
"\n a.attnum=d.refobjsubid)"
17241724
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
17251725
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1726-
"\n AND d.objid=%s"
1726+
"\n AND d.objid='%s'"
17271727
"\n AND d.deptype='a'",
17281728
oid);
17291729

@@ -2242,13 +2242,13 @@ describeOneTableDetails(const char *schemaname,
22422242
/* Footer information about foreign table */
22432243
printfPQExpBuffer(&buf,
22442244
"SELECT s.srvname,\n"
2245-
" array_to_string(ARRAY(SELECT "
2246-
" quote_ident(option_name) || ' ' || "
2247-
" quote_literal(option_value) FROM "
2248-
" pg_options_to_table(ftoptions)), ', ') "
2245+
" pg_catalog.array_to_string(ARRAY(\n"
2246+
" SELECT pg_catalog.quote_ident(option_name)"
2247+
" || ' ' || pg_catalog.quote_literal(option_value)\n"
2248+
" FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
22492249
"FROM pg_catalog.pg_foreign_table f,\n"
22502250
" pg_catalog.pg_foreign_server s\n"
2251-
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2251+
"WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
22522252
oid);
22532253
result = PSQLexec(buf.data, false);
22542254
if (!result)
@@ -2668,16 +2668,16 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
26682668

26692669
printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
26702670
"pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
2671-
"FROM pg_db_role_setting AS s\n"
2672-
"LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
2673-
"LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
2671+
"FROM pg_catalog.pg_db_role_setting s\n"
2672+
"LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
2673+
"LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n",
26742674
gettext_noop("Role"),
26752675
gettext_noop("Database"),
26762676
gettext_noop("Settings"));
26772677
havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
2678-
NULL, "pg_roles.rolname", NULL, NULL);
2678+
NULL, "r.rolname", NULL, NULL);
26792679
processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
2680-
NULL, "pg_database.datname", NULL, NULL);
2680+
NULL, "d.datname", NULL, NULL);
26812681
appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
26822682
}
26832683
else
@@ -2906,13 +2906,13 @@ listLanguages(const char *pattern, bool verbose, bool showSystem)
29062906
{
29072907
appendPQExpBuffer(&buf,
29082908
",\n NOT l.lanispl AS \"%s\",\n"
2909-
" l.lanplcallfoid::regprocedure AS \"%s\",\n"
2910-
" l.lanvalidator::regprocedure AS \"%s\",\n ",
2909+
" l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
2910+
" l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n ",
29112911
gettext_noop("Internal Language"),
29122912
gettext_noop("Call Handler"),
29132913
gettext_noop("Validator"));
29142914
if (pset.sversion >= 90000)
2915-
appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n ",
2915+
appendPQExpBuffer(&buf, "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
29162916
gettext_noop("Inline Handler"));
29172917
printACLColumn(&buf, "l.lanacl");
29182918
}
@@ -4037,10 +4037,10 @@ listForeignDataWrappers(const char *pattern, bool verbose)
40374037
printACLColumn(&buf, "fdwacl");
40384038
appendPQExpBuffer(&buf,
40394039
",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4040-
" '(' || array_to_string(ARRAY(SELECT "
4041-
" quote_ident(option_name) || ' ' || "
4042-
" quote_literal(option_value) FROM "
4043-
" pg_options_to_table(fdwoptions)), ', ') || ')' "
4040+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4041+
" pg_catalog.quote_ident(option_name) || ' ' || "
4042+
" pg_catalog.quote_literal(option_value) FROM "
4043+
" pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' "
40444044
" END AS \"%s\"",
40454045
gettext_noop("FDW Options"));
40464046

@@ -4118,10 +4118,10 @@ listForeignServers(const char *pattern, bool verbose)
41184118
" s.srvtype AS \"%s\",\n"
41194119
" s.srvversion AS \"%s\",\n"
41204120
" CASE WHEN srvoptions IS NULL THEN '' ELSE "
4121-
" '(' || array_to_string(ARRAY(SELECT "
4122-
" quote_ident(option_name) || ' ' || "
4123-
" quote_literal(option_value) FROM "
4124-
" pg_options_to_table(srvoptions)), ', ') || ')' "
4121+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4122+
" pg_catalog.quote_ident(option_name) || ' ' || "
4123+
" pg_catalog.quote_literal(option_value) FROM "
4124+
" pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' "
41254125
" END AS \"%s\",\n"
41264126
" d.description AS \"%s\"",
41274127
gettext_noop("Type"),
@@ -4136,7 +4136,7 @@ listForeignServers(const char *pattern, bool verbose)
41364136

41374137
if (verbose)
41384138
appendPQExpBufferStr(&buf,
4139-
"LEFT JOIN pg_description d\n "
4139+
"LEFT JOIN pg_catalog.pg_description d\n "
41404140
"ON d.classoid = s.tableoid AND d.objoid = s.oid "
41414141
"AND d.objsubid = 0\n");
41424142

@@ -4192,10 +4192,10 @@ listUserMappings(const char *pattern, bool verbose)
41924192
if (verbose)
41934193
appendPQExpBuffer(&buf,
41944194
",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4195-
" '(' || array_to_string(ARRAY(SELECT "
4196-
" quote_ident(option_name) || ' ' || "
4197-
" quote_literal(option_value) FROM "
4198-
" pg_options_to_table(umoptions)), ', ') || ')' "
4195+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4196+
" pg_catalog.quote_ident(option_name) || ' ' || "
4197+
" pg_catalog.quote_literal(option_value) FROM "
4198+
" pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' "
41994199
" END AS \"%s\"",
42004200
gettext_noop("FDW Options"));
42014201

@@ -4255,10 +4255,10 @@ listForeignTables(const char *pattern, bool verbose)
42554255
if (verbose)
42564256
appendPQExpBuffer(&buf,
42574257
",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4258-
" '(' || array_to_string(ARRAY(SELECT "
4259-
" quote_ident(option_name) || ' ' || "
4260-
" quote_literal(option_value) FROM "
4261-
" pg_options_to_table(ftoptions)), ', ') || ')' "
4258+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4259+
" pg_catalog.quote_ident(option_name) || ' ' || "
4260+
" pg_catalog.quote_literal(option_value) FROM "
4261+
" pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' "
42624262
" END AS \"%s\",\n"
42634263
" d.description AS \"%s\"",
42644264
gettext_noop("FDW Options"),

0 commit comments

Comments
 (0)