Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit50d2426

Browse files
committed
Clean up SQL emitted by psql/describe.c.
Fix assorted places that had not bothered with the convention ofprefixing catalog and function names with "pg_catalog.". Thatcould possibly result in query failure when running with a nondefaultsearch_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 stilla 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 intodescribeOneTableDetails' queries for child tables. We just got the OIDout of pg_class, so I hardly see how checking that it exists in pg_classwas doing anything helpful.In passing, try to improve the emitted formatting of a couple ofthese queries, though I didn't work really hard on that. And mergeunnecessarily duplicative coding in some other places.Much of this was new in HEAD, but some was quite old; back-patchas appropriate.
1 parent5e3254f commit50d2426

File tree

1 file changed

+75
-91
lines changed

1 file changed

+75
-91
lines changed

‎src/bin/psql/describe.c‎

Lines changed: 75 additions & 91 deletions
Original file line numberDiff line numberDiff line change
@@ -1594,17 +1594,17 @@ describeOneTableDetails(const char *schemaname,
15941594
else
15951595
appendPQExpBufferStr(&buf,"\n NULL AS attcollation");
15961596
if (pset.sversion >=100000)
1597-
appendPQExpBufferStr(&buf,", a.attidentity");
1597+
appendPQExpBufferStr(&buf,",\n a.attidentity");
15981598
else
1599-
appendPQExpBufferStr(&buf,",''::\"char\" AS attidentity");
1599+
appendPQExpBufferStr(&buf,",\n''::pg_catalog.char AS attidentity");
16001600
if (tableinfo.relkind==RELKIND_INDEX)
16011601
appendPQExpBufferStr(&buf,",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
16021602
else
16031603
appendPQExpBufferStr(&buf,",\n NULL AS indexdef");
16041604
if (tableinfo.relkind==RELKIND_FOREIGN_TABLE&&pset.sversion >=90200)
16051605
appendPQExpBufferStr(&buf,",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1606-
" '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) ||' ' || quote_literal(option_value) FROM "
1607-
" pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1606+
" '(' ||pg_catalog.array_to_string(ARRAY(SELECTpg_catalog.quote_ident(option_name) || ' ' ||pg_catalog.quote_literal(option_value) FROM "
1607+
"pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
16081608
else
16091609
appendPQExpBufferStr(&buf,",\n NULL AS attfdwoptions");
16101610
if (verbose)
@@ -1854,30 +1854,24 @@ describeOneTableDetails(const char *schemaname,
18541854
/* Make footers */
18551855
if (pset.sversion >=100000)
18561856
{
1857-
/* Get the partition information*/
1857+
/* Get the partition information */
18581858
PGresult*result;
18591859
char*parent_name;
18601860
char*partdef;
18611861
char*partconstraintdef=NULL;
18621862

1863+
printfPQExpBuffer(&buf,
1864+
"SELECT inhparent::pg_catalog.regclass,\n"
1865+
" pg_catalog.pg_get_expr(c.relpartbound, inhrelid)");
18631866
/* If verbose, also request the partition constraint definition */
18641867
if (verbose)
1865-
printfPQExpBuffer(&buf,
1866-
"SELECT inhparent::pg_catalog.regclass,"
1867-
"pg_get_expr(c.relpartbound, inhrelid),"
1868-
"pg_get_partition_constraintdef(inhrelid)"
1869-
" FROM pg_catalog.pg_class c"
1870-
" JOIN pg_catalog.pg_inherits"
1871-
" ON c.oid = inhrelid"
1872-
" WHERE c.oid = '%s' AND c.relispartition;",oid);
1873-
else
1874-
printfPQExpBuffer(&buf,
1875-
"SELECT inhparent::pg_catalog.regclass,"
1876-
"pg_get_expr(c.relpartbound, inhrelid)"
1877-
" FROM pg_catalog.pg_class c"
1878-
" JOIN pg_catalog.pg_inherits"
1879-
" ON c.oid = inhrelid"
1880-
" WHERE c.oid = '%s' AND c.relispartition;",oid);
1868+
appendPQExpBuffer(&buf,
1869+
",\n pg_catalog.pg_get_partition_constraintdef(inhrelid)");
1870+
appendPQExpBuffer(&buf,
1871+
"\nFROM pg_catalog.pg_class c"
1872+
" JOIN pg_catalog.pg_inherits i"
1873+
" ON c.oid = inhrelid"
1874+
"\nWHERE c.oid = '%s' AND c.relispartition;",oid);
18811875
result=PSQLexec(buf.data);
18821876
if (!result)
18831877
gotoerror_return;
@@ -2041,7 +2035,7 @@ describeOneTableDetails(const char *schemaname,
20412035
"\n a.attnum=d.refobjsubid)"
20422036
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
20432037
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
2044-
"\n AND d.objid=%s"
2038+
"\n AND d.objid='%s'"
20452039
"\n AND d.deptype IN ('a', 'i')",
20462040
oid);
20472041

@@ -2285,36 +2279,26 @@ describeOneTableDetails(const char *schemaname,
22852279
/* print any row-level policies */
22862280
if (pset.sversion >=90500)
22872281
{
2282+
printfPQExpBuffer(&buf,"SELECT pol.polname,");
22882283
if (pset.sversion >=100000)
2289-
printfPQExpBuffer(&buf,
2290-
"SELECT pol.polname, pol.polpermissive,\n"
2291-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2292-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2293-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2294-
"CASE pol.polcmd\n"
2295-
"WHEN 'r' THEN 'SELECT'\n"
2296-
"WHEN 'a' THEN 'INSERT'\n"
2297-
"WHEN 'w' THEN 'UPDATE'\n"
2298-
"WHEN 'd' THEN 'DELETE'\n"
2299-
"END AS cmd\n"
2300-
"FROM pg_catalog.pg_policy pol\n"
2301-
"WHERE pol.polrelid = '%s' ORDER BY 1;",
2302-
oid);
2284+
appendPQExpBuffer(&buf,
2285+
" pol.polpermissive,\n");
23032286
else
2304-
printfPQExpBuffer(&buf,
2305-
"SELECT pol.polname, 't' as polpermissive,\n"
2306-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2307-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2308-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2309-
"CASE pol.polcmd\n"
2310-
"WHEN 'r' THEN 'SELECT'\n"
2311-
"WHEN 'a' THEN 'INSERT'\n"
2312-
"WHEN 'w' THEN 'UPDATE'\n"
2313-
"WHEN 'd' THEN 'DELETE'\n"
2314-
"END AS cmd\n"
2315-
"FROM pg_catalog.pg_policy pol\n"
2316-
"WHERE pol.polrelid = '%s' ORDER BY 1;",
2317-
oid);
2287+
appendPQExpBuffer(&buf,
2288+
" 't' as polpermissive,\n");
2289+
appendPQExpBuffer(&buf,
2290+
" CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2291+
" pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2292+
" pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2293+
" CASE pol.polcmd\n"
2294+
" WHEN 'r' THEN 'SELECT'\n"
2295+
" WHEN 'a' THEN 'INSERT'\n"
2296+
" WHEN 'w' THEN 'UPDATE'\n"
2297+
" WHEN 'd' THEN 'DELETE'\n"
2298+
" END AS cmd\n"
2299+
"FROM pg_catalog.pg_policy pol\n"
2300+
"WHERE pol.polrelid = '%s' ORDER BY 1;",
2301+
oid);
23182302

23192303
result=PSQLexec(buf.data);
23202304
if (!result)
@@ -2543,7 +2527,7 @@ describeOneTableDetails(const char *schemaname,
25432527
"UNION ALL\n"
25442528
"SELECT pubname\n"
25452529
"FROM pg_catalog.pg_publication p\n"
2546-
"WHERE p.puballtables AND pg_relation_is_publishable('%s')\n"
2530+
"WHERE p.puballtables ANDpg_catalog.pg_relation_is_publishable('%s')\n"
25472531
"ORDER BY 1;",
25482532
oid,oid);
25492533

@@ -2764,13 +2748,13 @@ describeOneTableDetails(const char *schemaname,
27642748
/* Footer information about foreign table */
27652749
printfPQExpBuffer(&buf,
27662750
"SELECT s.srvname,\n"
2767-
"array_to_string(ARRAY(SELECT"
2768-
"quote_ident(option_name) || ' ' ||"
2769-
"quote_literal(option_value) FROM"
2770-
"pg_options_to_table(ftoptions)), ', ')"
2751+
"pg_catalog.array_to_string(ARRAY(\n"
2752+
"SELECT pg_catalog.quote_ident(option_name)"
2753+
"|| ' ' || pg_catalog.quote_literal(option_value)\n"
2754+
"FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
27712755
"FROM pg_catalog.pg_foreign_table f,\n"
27722756
" pg_catalog.pg_foreign_server s\n"
2773-
"WHERE f.ftrelid =%s AND s.oid = f.ftserver;",
2757+
"WHERE f.ftrelid ='%s' AND s.oid = f.ftserver;",
27742758
oid);
27752759
result=PSQLexec(buf.data);
27762760
if (!result)
@@ -2834,22 +2818,22 @@ describeOneTableDetails(const char *schemaname,
28342818
/* print child tables (with additional info if partitions) */
28352819
if (pset.sversion >=100000)
28362820
printfPQExpBuffer(&buf,
2837-
"SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)"
2821+
"SELECT c.oid::pg_catalog.regclass,pg_catalog.pg_get_expr(c.relpartbound, c.oid)"
28382822
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2839-
" WHERE c.oid=i.inhrelid AND"
2840-
" i.inhparent = '%s' AND"
2841-
" EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2842-
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",oid,oid);
2823+
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
2824+
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",oid);
28432825
elseif (pset.sversion >=80300)
28442826
printfPQExpBuffer(&buf,
28452827
"SELECT c.oid::pg_catalog.regclass"
28462828
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2847-
" WHERE c.oid=i.inhrelid AND"
2848-
" i.inhparent = '%s' AND"
2849-
" EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2850-
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",oid,oid);
2829+
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
2830+
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",oid);
28512831
else
2852-
printfPQExpBuffer(&buf,"SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;",oid);
2832+
printfPQExpBuffer(&buf,
2833+
"SELECT c.oid::pg_catalog.regclass"
2834+
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2835+
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
2836+
" ORDER BY c.relname;",oid);
28532837

28542838
result=PSQLexec(buf.data);
28552839
if (!result)
@@ -3234,16 +3218,16 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
32343218

32353219
printfPQExpBuffer(&buf,"SELECT rolname AS \"%s\", datname AS \"%s\",\n"
32363220
"pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3237-
"FROM pg_db_role_setting AS s\n"
3238-
"LEFT JOIN pg_database ONpg_database.oid = setdatabase\n"
3239-
"LEFT JOIN pg_roles ONpg_roles.oid = setrole\n",
3221+
"FROMpg_catalog.pg_db_role_setting s\n"
3222+
"LEFT JOINpg_catalog.pg_databasedONd.oid = setdatabase\n"
3223+
"LEFT JOINpg_catalog.pg_rolesrONr.oid = setrole\n",
32403224
gettext_noop("Role"),
32413225
gettext_noop("Database"),
32423226
gettext_noop("Settings"));
32433227
havewhere=processSQLNamePattern(pset.db,&buf,pattern, false, false,
3244-
NULL,"pg_roles.rolname",NULL,NULL);
3228+
NULL,"r.rolname",NULL,NULL);
32453229
processSQLNamePattern(pset.db,&buf,pattern2,havewhere, false,
3246-
NULL,"pg_database.datname",NULL,NULL);
3230+
NULL,"d.datname",NULL,NULL);
32473231
appendPQExpBufferStr(&buf,"ORDER BY 1, 2;");
32483232
}
32493233
else
@@ -3475,13 +3459,13 @@ listLanguages(const char *pattern, bool verbose, bool showSystem)
34753459
{
34763460
appendPQExpBuffer(&buf,
34773461
",\n NOT l.lanispl AS \"%s\",\n"
3478-
" l.lanplcallfoid::regprocedure AS \"%s\",\n"
3479-
" l.lanvalidator::regprocedure AS \"%s\",\n ",
3462+
" l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
3463+
" l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n ",
34803464
gettext_noop("Internal language"),
34813465
gettext_noop("Call handler"),
34823466
gettext_noop("Validator"));
34833467
if (pset.sversion >=90000)
3484-
appendPQExpBuffer(&buf,"l.laninline::regprocedure AS \"%s\",\n ",
3468+
appendPQExpBuffer(&buf,"l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
34853469
gettext_noop("Inline handler"));
34863470
printACLColumn(&buf,"l.lanacl");
34873471
}
@@ -4611,10 +4595,10 @@ listForeignDataWrappers(const char *pattern, bool verbose)
46114595
printACLColumn(&buf,"fdwacl");
46124596
appendPQExpBuffer(&buf,
46134597
",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4614-
" '(' || array_to_string(ARRAY(SELECT "
4615-
" quote_ident(option_name) || ' ' || "
4616-
" quote_literal(option_value) FROM "
4617-
" pg_options_to_table(fdwoptions)), ', ') || ')' "
4598+
" '(' ||pg_catalog.array_to_string(ARRAY(SELECT "
4599+
"pg_catalog.quote_ident(option_name) || ' ' || "
4600+
"pg_catalog.quote_literal(option_value) FROM "
4601+
"pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' "
46184602
" END AS \"%s\"",
46194603
gettext_noop("FDW options"));
46204604

@@ -4692,10 +4676,10 @@ listForeignServers(const char *pattern, bool verbose)
46924676
" s.srvtype AS \"%s\",\n"
46934677
" s.srvversion AS \"%s\",\n"
46944678
" CASE WHEN srvoptions IS NULL THEN '' ELSE "
4695-
" '(' || array_to_string(ARRAY(SELECT "
4696-
" quote_ident(option_name) || ' ' || "
4697-
" quote_literal(option_value) FROM "
4698-
" pg_options_to_table(srvoptions)), ', ') || ')' "
4679+
" '(' ||pg_catalog.array_to_string(ARRAY(SELECT "
4680+
"pg_catalog.quote_ident(option_name) || ' ' || "
4681+
"pg_catalog.quote_literal(option_value) FROM "
4682+
"pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' "
46994683
" END AS \"%s\",\n"
47004684
" d.description AS \"%s\"",
47014685
gettext_noop("Type"),
@@ -4710,7 +4694,7 @@ listForeignServers(const char *pattern, bool verbose)
47104694

47114695
if (verbose)
47124696
appendPQExpBufferStr(&buf,
4713-
"LEFT JOIN pg_description d\n "
4697+
"LEFT JOINpg_catalog.pg_description d\n "
47144698
"ON d.classoid = s.tableoid AND d.objoid = s.oid "
47154699
"AND d.objsubid = 0\n");
47164700

@@ -4766,10 +4750,10 @@ listUserMappings(const char *pattern, bool verbose)
47664750
if (verbose)
47674751
appendPQExpBuffer(&buf,
47684752
",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4769-
" '(' || array_to_string(ARRAY(SELECT "
4770-
" quote_ident(option_name) || ' ' || "
4771-
" quote_literal(option_value) FROM "
4772-
" pg_options_to_table(umoptions)), ', ') || ')' "
4753+
" '(' ||pg_catalog.array_to_string(ARRAY(SELECT "
4754+
"pg_catalog.quote_ident(option_name) || ' ' || "
4755+
"pg_catalog.quote_literal(option_value) FROM "
4756+
"pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' "
47734757
" END AS \"%s\"",
47744758
gettext_noop("FDW options"));
47754759

@@ -4829,10 +4813,10 @@ listForeignTables(const char *pattern, bool verbose)
48294813
if (verbose)
48304814
appendPQExpBuffer(&buf,
48314815
",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4832-
" '(' || array_to_string(ARRAY(SELECT "
4833-
" quote_ident(option_name) || ' ' || "
4834-
" quote_literal(option_value) FROM "
4835-
" pg_options_to_table(ftoptions)), ', ') || ')' "
4816+
" '(' ||pg_catalog.array_to_string(ARRAY(SELECT "
4817+
"pg_catalog.quote_ident(option_name) || ' ' || "
4818+
"pg_catalog.quote_literal(option_value) FROM "
4819+
"pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' "
48364820
" END AS \"%s\",\n"
48374821
" d.description AS \"%s\"",
48384822
gettext_noop("FDW options"),

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp