Skip to content

Commit 00cf718

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 2efbfb9 commit 00cf718

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
@@ -1328,8 +1328,8 @@ describeOneTableDetails(const char *schemaname,
13281328
appendPQExpBuffer(&buf, ",\n NULL AS indexdef");
13291329
if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
13301330
appendPQExpBuffer(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1331-
" '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM "
1332-
" pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1331+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
1332+
" pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
13331333
else
13341334
appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions");
13351335
if (verbose)
@@ -1669,7 +1669,7 @@ describeOneTableDetails(const char *schemaname,
16691669
"\n a.attnum=d.refobjsubid)"
16701670
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
16711671
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1672-
"\n AND d.objid=%s"
1672+
"\n AND d.objid='%s'"
16731673
"\n AND d.deptype='a'",
16741674
oid);
16751675

@@ -2163,13 +2163,13 @@ describeOneTableDetails(const char *schemaname,
21632163
/* Footer information about foreign table */
21642164
printfPQExpBuffer(&buf,
21652165
"SELECT s.srvname,\n"
2166-
" array_to_string(ARRAY(SELECT "
2167-
" quote_ident(option_name) || ' ' || "
2168-
" quote_literal(option_value) FROM "
2169-
" pg_options_to_table(ftoptions)), ', ') "
2166+
" pg_catalog.array_to_string(ARRAY(\n"
2167+
" SELECT pg_catalog.quote_ident(option_name)"
2168+
" || ' ' || pg_catalog.quote_literal(option_value)\n"
2169+
" FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
21702170
"FROM pg_catalog.pg_foreign_table f,\n"
21712171
" pg_catalog.pg_foreign_server s\n"
2172-
"WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2172+
"WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
21732173
oid);
21742174
result = PSQLexec(buf.data, false);
21752175
if (!result)
@@ -2575,16 +2575,16 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
25752575

25762576
printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
25772577
"pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
2578-
"FROM pg_db_role_setting AS s\n"
2579-
"LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
2580-
"LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
2578+
"FROM pg_catalog.pg_db_role_setting s\n"
2579+
"LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
2580+
"LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n",
25812581
gettext_noop("Role"),
25822582
gettext_noop("Database"),
25832583
gettext_noop("Settings"));
25842584
havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
2585-
NULL, "pg_roles.rolname", NULL, NULL);
2585+
NULL, "r.rolname", NULL, NULL);
25862586
processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
2587-
NULL, "pg_database.datname", NULL, NULL);
2587+
NULL, "d.datname", NULL, NULL);
25882588
appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
25892589
}
25902590
else
@@ -2812,13 +2812,13 @@ listLanguages(const char *pattern, bool verbose, bool showSystem)
28122812
{
28132813
appendPQExpBuffer(&buf,
28142814
",\n NOT l.lanispl AS \"%s\",\n"
2815-
" l.lanplcallfoid::regprocedure AS \"%s\",\n"
2816-
" l.lanvalidator::regprocedure AS \"%s\",\n ",
2815+
" l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
2816+
" l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n ",
28172817
gettext_noop("Internal Language"),
28182818
gettext_noop("Call Handler"),
28192819
gettext_noop("Validator"));
28202820
if (pset.sversion >= 90000)
2821-
appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n ",
2821+
appendPQExpBuffer(&buf, "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
28222822
gettext_noop("Inline Handler"));
28232823
printACLColumn(&buf, "l.lanacl");
28242824
}
@@ -3937,10 +3937,10 @@ listForeignDataWrappers(const char *pattern, bool verbose)
39373937
printACLColumn(&buf, "fdwacl");
39383938
appendPQExpBuffer(&buf,
39393939
",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
3940-
" '(' || array_to_string(ARRAY(SELECT "
3941-
" quote_ident(option_name) || ' ' || "
3942-
" quote_literal(option_value) FROM "
3943-
" pg_options_to_table(fdwoptions)), ', ') || ')' "
3940+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
3941+
" pg_catalog.quote_ident(option_name) || ' ' || "
3942+
" pg_catalog.quote_literal(option_value) FROM "
3943+
" pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' "
39443944
" END AS \"%s\"",
39453945
gettext_noop("FDW Options"));
39463946

@@ -4018,10 +4018,10 @@ listForeignServers(const char *pattern, bool verbose)
40184018
" s.srvtype AS \"%s\",\n"
40194019
" s.srvversion AS \"%s\",\n"
40204020
" CASE WHEN srvoptions IS NULL THEN '' ELSE "
4021-
" '(' || array_to_string(ARRAY(SELECT "
4022-
" quote_ident(option_name) || ' ' || "
4023-
" quote_literal(option_value) FROM "
4024-
" pg_options_to_table(srvoptions)), ', ') || ')' "
4021+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4022+
" pg_catalog.quote_ident(option_name) || ' ' || "
4023+
" pg_catalog.quote_literal(option_value) FROM "
4024+
" pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' "
40254025
" END AS \"%s\",\n"
40264026
" d.description AS \"%s\"",
40274027
gettext_noop("Type"),
@@ -4036,7 +4036,7 @@ listForeignServers(const char *pattern, bool verbose)
40364036

40374037
if (verbose)
40384038
appendPQExpBuffer(&buf,
4039-
"LEFT JOIN pg_description d\n "
4039+
"LEFT JOIN pg_catalog.pg_description d\n "
40404040
"ON d.classoid = s.tableoid AND d.objoid = s.oid "
40414041
"AND d.objsubid = 0\n");
40424042

@@ -4092,10 +4092,10 @@ listUserMappings(const char *pattern, bool verbose)
40924092
if (verbose)
40934093
appendPQExpBuffer(&buf,
40944094
",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4095-
" '(' || array_to_string(ARRAY(SELECT "
4096-
" quote_ident(option_name) || ' ' || "
4097-
" quote_literal(option_value) FROM "
4098-
" pg_options_to_table(umoptions)), ', ') || ')' "
4095+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4096+
" pg_catalog.quote_ident(option_name) || ' ' || "
4097+
" pg_catalog.quote_literal(option_value) FROM "
4098+
" pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' "
40994099
" END AS \"%s\"",
41004100
gettext_noop("FDW Options"));
41014101

@@ -4155,10 +4155,10 @@ listForeignTables(const char *pattern, bool verbose)
41554155
if (verbose)
41564156
appendPQExpBuffer(&buf,
41574157
",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4158-
" '(' || array_to_string(ARRAY(SELECT "
4159-
" quote_ident(option_name) || ' ' || "
4160-
" quote_literal(option_value) FROM "
4161-
" pg_options_to_table(ftoptions)), ', ') || ')' "
4158+
" '(' || pg_catalog.array_to_string(ARRAY(SELECT "
4159+
" pg_catalog.quote_ident(option_name) || ' ' || "
4160+
" pg_catalog.quote_literal(option_value) FROM "
4161+
" pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' "
41624162
" END AS \"%s\",\n"
41634163
" d.description AS \"%s\"",
41644164
gettext_noop("FDW Options"),

0 commit comments

Comments
 (0)