Header And Logo
|
00001 /* 00002 * psql - the PostgreSQL interactive terminal 00003 * 00004 * Support for the various \d ("describe") commands. Note that the current 00005 * expectation is that all functions in this file will succeed when working 00006 * with servers of versions 7.4 and up. It's okay to omit irrelevant 00007 * information for an old server, but not to fail outright. 00008 * 00009 * Copyright (c) 2000-2013, PostgreSQL Global Development Group 00010 * 00011 * src/bin/psql/describe.c 00012 */ 00013 #include "postgres_fe.h" 00014 00015 #include <ctype.h> 00016 00017 #include "catalog/pg_default_acl.h" 00018 00019 #include "common.h" 00020 #include "describe.h" 00021 #include "dumputils.h" 00022 #include "mbprint.h" 00023 #include "print.h" 00024 #include "settings.h" 00025 #include "variables.h" 00026 00027 00028 static bool describeOneTableDetails(const char *schemaname, 00029 const char *relationname, 00030 const char *oid, 00031 bool verbose); 00032 static void add_tablespace_footer(printTableContent *const cont, char relkind, 00033 Oid tablespace, const bool newline); 00034 static void add_role_attribute(PQExpBuffer buf, const char *const str); 00035 static bool listTSParsersVerbose(const char *pattern); 00036 static bool describeOneTSParser(const char *oid, const char *nspname, 00037 const char *prsname); 00038 static bool listTSConfigsVerbose(const char *pattern); 00039 static bool describeOneTSConfig(const char *oid, const char *nspname, 00040 const char *cfgname, 00041 const char *pnspname, const char *prsname); 00042 static void printACLColumn(PQExpBuffer buf, const char *colname); 00043 static bool listOneExtensionContents(const char *extname, const char *oid); 00044 00045 00046 /*---------------- 00047 * Handlers for various slash commands displaying some sort of list 00048 * of things in the database. 00049 * 00050 * Note: try to format the queries to look nice in -E output. 00051 *---------------- 00052 */ 00053 00054 00055 /* \da 00056 * Takes an optional regexp to select particular aggregates 00057 */ 00058 bool 00059 describeAggregates(const char *pattern, bool verbose, bool showSystem) 00060 { 00061 PQExpBufferData buf; 00062 PGresult *res; 00063 printQueryOpt myopt = pset.popt; 00064 00065 initPQExpBuffer(&buf); 00066 00067 printfPQExpBuffer(&buf, 00068 "SELECT n.nspname as \"%s\",\n" 00069 " p.proname AS \"%s\",\n" 00070 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n", 00071 gettext_noop("Schema"), 00072 gettext_noop("Name"), 00073 gettext_noop("Result data type")); 00074 00075 if (pset.sversion >= 80200) 00076 appendPQExpBuffer(&buf, 00077 " CASE WHEN p.pronargs = 0\n" 00078 " THEN CAST('*' AS pg_catalog.text)\n" 00079 " ELSE\n" 00080 " pg_catalog.array_to_string(ARRAY(\n" 00081 " SELECT\n" 00082 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n" 00083 " FROM\n" 00084 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n" 00085 " ), ', ')\n" 00086 " END AS \"%s\",\n", 00087 gettext_noop("Argument data types")); 00088 else 00089 appendPQExpBuffer(&buf, 00090 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n", 00091 gettext_noop("Argument data types")); 00092 00093 appendPQExpBuffer(&buf, 00094 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n" 00095 "FROM pg_catalog.pg_proc p\n" 00096 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n" 00097 "WHERE p.proisagg\n", 00098 gettext_noop("Description")); 00099 00100 if (!showSystem && !pattern) 00101 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00102 " AND n.nspname <> 'information_schema'\n"); 00103 00104 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00105 "n.nspname", "p.proname", NULL, 00106 "pg_catalog.pg_function_is_visible(p.oid)"); 00107 00108 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;"); 00109 00110 res = PSQLexec(buf.data, false); 00111 termPQExpBuffer(&buf); 00112 if (!res) 00113 return false; 00114 00115 myopt.nullPrint = NULL; 00116 myopt.title = _("List of aggregate functions"); 00117 myopt.translate_header = true; 00118 00119 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00120 00121 PQclear(res); 00122 return true; 00123 } 00124 00125 /* \db 00126 * Takes an optional regexp to select particular tablespaces 00127 */ 00128 bool 00129 describeTablespaces(const char *pattern, bool verbose) 00130 { 00131 PQExpBufferData buf; 00132 PGresult *res; 00133 printQueryOpt myopt = pset.popt; 00134 00135 if (pset.sversion < 80000) 00136 { 00137 psql_error("The server (version %d.%d) does not support tablespaces.\n", 00138 pset.sversion / 10000, (pset.sversion / 100) % 100); 00139 return true; 00140 } 00141 00142 initPQExpBuffer(&buf); 00143 00144 if (pset.sversion >= 90200) 00145 printfPQExpBuffer(&buf, 00146 "SELECT spcname AS \"%s\",\n" 00147 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n" 00148 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"", 00149 gettext_noop("Name"), 00150 gettext_noop("Owner"), 00151 gettext_noop("Location")); 00152 else 00153 printfPQExpBuffer(&buf, 00154 "SELECT spcname AS \"%s\",\n" 00155 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n" 00156 " spclocation AS \"%s\"", 00157 gettext_noop("Name"), 00158 gettext_noop("Owner"), 00159 gettext_noop("Location")); 00160 00161 if (verbose) 00162 { 00163 appendPQExpBuffer(&buf, ",\n "); 00164 printACLColumn(&buf, "spcacl"); 00165 } 00166 00167 if (verbose && pset.sversion >= 80200) 00168 appendPQExpBuffer(&buf, 00169 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", 00170 gettext_noop("Description")); 00171 00172 appendPQExpBuffer(&buf, 00173 "\nFROM pg_catalog.pg_tablespace\n"); 00174 00175 processSQLNamePattern(pset.db, &buf, pattern, false, false, 00176 NULL, "spcname", NULL, 00177 NULL); 00178 00179 appendPQExpBuffer(&buf, "ORDER BY 1;"); 00180 00181 res = PSQLexec(buf.data, false); 00182 termPQExpBuffer(&buf); 00183 if (!res) 00184 return false; 00185 00186 myopt.nullPrint = NULL; 00187 myopt.title = _("List of tablespaces"); 00188 myopt.translate_header = true; 00189 00190 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00191 00192 PQclear(res); 00193 return true; 00194 } 00195 00196 00197 /* \df 00198 * Takes an optional regexp to select particular functions. 00199 * 00200 * As with \d, you can specify the kinds of functions you want: 00201 * 00202 * a for aggregates 00203 * n for normal 00204 * t for trigger 00205 * w for window 00206 * 00207 * and you can mix and match these in any order. 00208 */ 00209 bool 00210 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem) 00211 { 00212 bool showAggregate = strchr(functypes, 'a') != NULL; 00213 bool showNormal = strchr(functypes, 'n') != NULL; 00214 bool showTrigger = strchr(functypes, 't') != NULL; 00215 bool showWindow = strchr(functypes, 'w') != NULL; 00216 bool have_where; 00217 PQExpBufferData buf; 00218 PGresult *res; 00219 printQueryOpt myopt = pset.popt; 00220 static const bool translate_columns[] = {false, false, false, false, true, true, false, false, false, false}; 00221 00222 if (strlen(functypes) != strspn(functypes, "antwS+")) 00223 { 00224 psql_error("\\df only takes [antwS+] as options\n"); 00225 return true; 00226 } 00227 00228 if (showWindow && pset.sversion < 80400) 00229 { 00230 psql_error("\\df does not take a \"w\" option with server version %d.%d\n", 00231 pset.sversion / 10000, (pset.sversion / 100) % 100); 00232 return true; 00233 } 00234 00235 if (!showAggregate && !showNormal && !showTrigger && !showWindow) 00236 { 00237 showAggregate = showNormal = showTrigger = true; 00238 if (pset.sversion >= 80400) 00239 showWindow = true; 00240 } 00241 00242 initPQExpBuffer(&buf); 00243 00244 printfPQExpBuffer(&buf, 00245 "SELECT n.nspname as \"%s\",\n" 00246 " p.proname as \"%s\",\n", 00247 gettext_noop("Schema"), 00248 gettext_noop("Name")); 00249 00250 if (pset.sversion >= 80400) 00251 appendPQExpBuffer(&buf, 00252 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n" 00253 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n" 00254 " CASE\n" 00255 " WHEN p.proisagg THEN '%s'\n" 00256 " WHEN p.proiswindow THEN '%s'\n" 00257 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 00258 " ELSE '%s'\n" 00259 " END as \"%s\"", 00260 gettext_noop("Result data type"), 00261 gettext_noop("Argument data types"), 00262 /* translator: "agg" is short for "aggregate" */ 00263 gettext_noop("agg"), 00264 gettext_noop("window"), 00265 gettext_noop("trigger"), 00266 gettext_noop("normal"), 00267 gettext_noop("Type")); 00268 else if (pset.sversion >= 80100) 00269 appendPQExpBuffer(&buf, 00270 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n" 00271 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n" 00272 " CASE WHEN proallargtypes IS NOT NULL THEN\n" 00273 " pg_catalog.array_to_string(ARRAY(\n" 00274 " SELECT\n" 00275 " CASE\n" 00276 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n" 00277 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n" 00278 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n" 00279 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n" 00280 " END ||\n" 00281 " CASE\n" 00282 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n" 00283 " ELSE p.proargnames[s.i] || ' ' \n" 00284 " END ||\n" 00285 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n" 00286 " FROM\n" 00287 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n" 00288 " ), ', ')\n" 00289 " ELSE\n" 00290 " pg_catalog.array_to_string(ARRAY(\n" 00291 " SELECT\n" 00292 " CASE\n" 00293 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n" 00294 " ELSE p.proargnames[s.i+1] || ' '\n" 00295 " END ||\n" 00296 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n" 00297 " FROM\n" 00298 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n" 00299 " ), ', ')\n" 00300 " END AS \"%s\",\n" 00301 " CASE\n" 00302 " WHEN p.proisagg THEN '%s'\n" 00303 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 00304 " ELSE '%s'\n" 00305 " END AS \"%s\"", 00306 gettext_noop("Result data type"), 00307 gettext_noop("Argument data types"), 00308 /* translator: "agg" is short for "aggregate" */ 00309 gettext_noop("agg"), 00310 gettext_noop("trigger"), 00311 gettext_noop("normal"), 00312 gettext_noop("Type")); 00313 else 00314 appendPQExpBuffer(&buf, 00315 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n" 00316 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n" 00317 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n" 00318 " CASE\n" 00319 " WHEN p.proisagg THEN '%s'\n" 00320 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 00321 " ELSE '%s'\n" 00322 " END AS \"%s\"", 00323 gettext_noop("Result data type"), 00324 gettext_noop("Argument data types"), 00325 /* translator: "agg" is short for "aggregate" */ 00326 gettext_noop("agg"), 00327 gettext_noop("trigger"), 00328 gettext_noop("normal"), 00329 gettext_noop("Type")); 00330 00331 if (verbose) 00332 appendPQExpBuffer(&buf, 00333 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"" 00334 ",\n CASE\n" 00335 " WHEN p.provolatile = 'i' THEN '%s'\n" 00336 " WHEN p.provolatile = 's' THEN '%s'\n" 00337 " WHEN p.provolatile = 'v' THEN '%s'\n" 00338 " END as \"%s\"" 00339 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\",\n" 00340 " l.lanname as \"%s\",\n" 00341 " p.prosrc as \"%s\",\n" 00342 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"", 00343 gettext_noop("definer"), 00344 gettext_noop("invoker"), 00345 gettext_noop("Security"), 00346 gettext_noop("immutable"), 00347 gettext_noop("stable"), 00348 gettext_noop("volatile"), 00349 gettext_noop("Volatility"), 00350 gettext_noop("Owner"), 00351 gettext_noop("Language"), 00352 gettext_noop("Source code"), 00353 gettext_noop("Description")); 00354 00355 appendPQExpBuffer(&buf, 00356 "\nFROM pg_catalog.pg_proc p" 00357 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"); 00358 00359 if (verbose) 00360 appendPQExpBuffer(&buf, 00361 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n"); 00362 00363 have_where = false; 00364 00365 /* filter by function type, if requested */ 00366 if (showNormal && showAggregate && showTrigger && showWindow) 00367 /* Do nothing */ ; 00368 else if (showNormal) 00369 { 00370 if (!showAggregate) 00371 { 00372 if (have_where) 00373 appendPQExpBuffer(&buf, " AND "); 00374 else 00375 { 00376 appendPQExpBuffer(&buf, "WHERE "); 00377 have_where = true; 00378 } 00379 appendPQExpBuffer(&buf, "NOT p.proisagg\n"); 00380 } 00381 if (!showTrigger) 00382 { 00383 if (have_where) 00384 appendPQExpBuffer(&buf, " AND "); 00385 else 00386 { 00387 appendPQExpBuffer(&buf, "WHERE "); 00388 have_where = true; 00389 } 00390 appendPQExpBuffer(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n"); 00391 } 00392 if (!showWindow && pset.sversion >= 80400) 00393 { 00394 if (have_where) 00395 appendPQExpBuffer(&buf, " AND "); 00396 else 00397 { 00398 appendPQExpBuffer(&buf, "WHERE "); 00399 have_where = true; 00400 } 00401 appendPQExpBuffer(&buf, "NOT p.proiswindow\n"); 00402 } 00403 } 00404 else 00405 { 00406 bool needs_or = false; 00407 00408 appendPQExpBuffer(&buf, "WHERE (\n "); 00409 have_where = true; 00410 /* Note: at least one of these must be true ... */ 00411 if (showAggregate) 00412 { 00413 appendPQExpBuffer(&buf, "p.proisagg\n"); 00414 needs_or = true; 00415 } 00416 if (showTrigger) 00417 { 00418 if (needs_or) 00419 appendPQExpBuffer(&buf, " OR "); 00420 appendPQExpBuffer(&buf, 00421 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n"); 00422 needs_or = true; 00423 } 00424 if (showWindow) 00425 { 00426 if (needs_or) 00427 appendPQExpBuffer(&buf, " OR "); 00428 appendPQExpBuffer(&buf, "p.proiswindow\n"); 00429 needs_or = true; 00430 } 00431 appendPQExpBuffer(&buf, " )\n"); 00432 } 00433 00434 processSQLNamePattern(pset.db, &buf, pattern, have_where, false, 00435 "n.nspname", "p.proname", NULL, 00436 "pg_catalog.pg_function_is_visible(p.oid)"); 00437 00438 if (!showSystem && !pattern) 00439 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00440 " AND n.nspname <> 'information_schema'\n"); 00441 00442 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;"); 00443 00444 res = PSQLexec(buf.data, false); 00445 termPQExpBuffer(&buf); 00446 if (!res) 00447 return false; 00448 00449 myopt.nullPrint = NULL; 00450 myopt.title = _("List of functions"); 00451 myopt.translate_header = true; 00452 myopt.translate_columns = translate_columns; 00453 00454 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00455 00456 PQclear(res); 00457 return true; 00458 } 00459 00460 00461 00462 /* 00463 * \dT 00464 * describe types 00465 */ 00466 bool 00467 describeTypes(const char *pattern, bool verbose, bool showSystem) 00468 { 00469 PQExpBufferData buf; 00470 PGresult *res; 00471 printQueryOpt myopt = pset.popt; 00472 00473 initPQExpBuffer(&buf); 00474 00475 printfPQExpBuffer(&buf, 00476 "SELECT n.nspname as \"%s\",\n" 00477 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n", 00478 gettext_noop("Schema"), 00479 gettext_noop("Name")); 00480 if (verbose) 00481 appendPQExpBuffer(&buf, 00482 " t.typname AS \"%s\",\n" 00483 " CASE WHEN t.typrelid != 0\n" 00484 " THEN CAST('tuple' AS pg_catalog.text)\n" 00485 " WHEN t.typlen < 0\n" 00486 " THEN CAST('var' AS pg_catalog.text)\n" 00487 " ELSE CAST(t.typlen AS pg_catalog.text)\n" 00488 " END AS \"%s\",\n", 00489 gettext_noop("Internal name"), 00490 gettext_noop("Size")); 00491 if (verbose && pset.sversion >= 80300) 00492 { 00493 appendPQExpBuffer(&buf, 00494 " pg_catalog.array_to_string(\n" 00495 " ARRAY(\n" 00496 " SELECT e.enumlabel\n" 00497 " FROM pg_catalog.pg_enum e\n" 00498 " WHERE e.enumtypid = t.oid\n"); 00499 00500 if (pset.sversion >= 90100) 00501 appendPQExpBuffer(&buf, 00502 " ORDER BY e.enumsortorder\n"); 00503 else 00504 appendPQExpBuffer(&buf, 00505 " ORDER BY e.oid\n"); 00506 00507 appendPQExpBuffer(&buf, 00508 " ),\n" 00509 " E'\\n'\n" 00510 " ) AS \"%s\",\n", 00511 gettext_noop("Elements")); 00512 } 00513 if (verbose && pset.sversion >= 90200) 00514 { 00515 printACLColumn(&buf, "t.typacl"); 00516 appendPQExpBuffer(&buf, ",\n "); 00517 } 00518 00519 appendPQExpBuffer(&buf, 00520 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n", 00521 gettext_noop("Description")); 00522 00523 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n" 00524 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"); 00525 00526 /* 00527 * do not include complex types (typrelid!=0) unless they are standalone 00528 * composite types 00529 */ 00530 appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 "); 00531 appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c " 00532 "WHERE c.oid = t.typrelid))\n"); 00533 00534 /* 00535 * do not include array types (before 8.3 we have to use the assumption 00536 * that their names start with underscore) 00537 */ 00538 if (pset.sversion >= 80300) 00539 appendPQExpBuffer(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n"); 00540 else 00541 appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n"); 00542 00543 if (!showSystem && !pattern) 00544 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00545 " AND n.nspname <> 'information_schema'\n"); 00546 00547 /* Match name pattern against either internal or external name */ 00548 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00549 "n.nspname", "t.typname", 00550 "pg_catalog.format_type(t.oid, NULL)", 00551 "pg_catalog.pg_type_is_visible(t.oid)"); 00552 00553 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 00554 00555 res = PSQLexec(buf.data, false); 00556 termPQExpBuffer(&buf); 00557 if (!res) 00558 return false; 00559 00560 myopt.nullPrint = NULL; 00561 myopt.title = _("List of data types"); 00562 myopt.translate_header = true; 00563 00564 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00565 00566 PQclear(res); 00567 return true; 00568 } 00569 00570 00571 /* \do 00572 */ 00573 bool 00574 describeOperators(const char *pattern, bool showSystem) 00575 { 00576 PQExpBufferData buf; 00577 PGresult *res; 00578 printQueryOpt myopt = pset.popt; 00579 00580 initPQExpBuffer(&buf); 00581 00582 /* 00583 * Note: before Postgres 9.1, we did not assign comments to any built-in 00584 * operators, preferring to let the comment on the underlying function 00585 * suffice. The coalesce() on the obj_description() calls below supports 00586 * this convention by providing a fallback lookup of a comment on the 00587 * operator's function. As of 9.1 there is a policy that every built-in 00588 * operator should have a comment; so the coalesce() is no longer 00589 * necessary so far as built-in operators are concerned. We keep it 00590 * anyway, for now, because (1) third-party modules may still be following 00591 * the old convention, and (2) we'd need to do it anyway when talking to a 00592 * pre-9.1 server. 00593 */ 00594 00595 printfPQExpBuffer(&buf, 00596 "SELECT n.nspname as \"%s\",\n" 00597 " o.oprname AS \"%s\",\n" 00598 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n" 00599 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n" 00600 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n" 00601 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n" 00602 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n" 00603 "FROM pg_catalog.pg_operator o\n" 00604 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n", 00605 gettext_noop("Schema"), 00606 gettext_noop("Name"), 00607 gettext_noop("Left arg type"), 00608 gettext_noop("Right arg type"), 00609 gettext_noop("Result type"), 00610 gettext_noop("Description")); 00611 00612 if (!showSystem && !pattern) 00613 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 00614 " AND n.nspname <> 'information_schema'\n"); 00615 00616 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true, 00617 "n.nspname", "o.oprname", NULL, 00618 "pg_catalog.pg_operator_is_visible(o.oid)"); 00619 00620 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;"); 00621 00622 res = PSQLexec(buf.data, false); 00623 termPQExpBuffer(&buf); 00624 if (!res) 00625 return false; 00626 00627 myopt.nullPrint = NULL; 00628 myopt.title = _("List of operators"); 00629 myopt.translate_header = true; 00630 00631 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00632 00633 PQclear(res); 00634 return true; 00635 } 00636 00637 00638 /* 00639 * listAllDbs 00640 * 00641 * for \l, \list, and -l switch 00642 */ 00643 bool 00644 listAllDbs(const char *pattern, bool verbose) 00645 { 00646 PGresult *res; 00647 PQExpBufferData buf; 00648 printQueryOpt myopt = pset.popt; 00649 00650 initPQExpBuffer(&buf); 00651 00652 printfPQExpBuffer(&buf, 00653 "SELECT d.datname as \"%s\",\n" 00654 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n" 00655 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n", 00656 gettext_noop("Name"), 00657 gettext_noop("Owner"), 00658 gettext_noop("Encoding")); 00659 if (pset.sversion >= 80400) 00660 appendPQExpBuffer(&buf, 00661 " d.datcollate as \"%s\",\n" 00662 " d.datctype as \"%s\",\n", 00663 gettext_noop("Collate"), 00664 gettext_noop("Ctype")); 00665 appendPQExpBuffer(&buf, " "); 00666 printACLColumn(&buf, "d.datacl"); 00667 if (verbose && pset.sversion >= 80200) 00668 appendPQExpBuffer(&buf, 00669 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" 00670 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" 00671 " ELSE 'No Access'\n" 00672 " END as \"%s\"", 00673 gettext_noop("Size")); 00674 if (verbose && pset.sversion >= 80000) 00675 appendPQExpBuffer(&buf, 00676 ",\n t.spcname as \"%s\"", 00677 gettext_noop("Tablespace")); 00678 if (verbose && pset.sversion >= 80200) 00679 appendPQExpBuffer(&buf, 00680 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", 00681 gettext_noop("Description")); 00682 appendPQExpBuffer(&buf, 00683 "\nFROM pg_catalog.pg_database d\n"); 00684 if (verbose && pset.sversion >= 80000) 00685 appendPQExpBuffer(&buf, 00686 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); 00687 00688 if (pattern) 00689 processSQLNamePattern(pset.db, &buf, pattern, false, false, 00690 NULL, "d.datname", NULL, NULL); 00691 00692 appendPQExpBuffer(&buf, "ORDER BY 1;"); 00693 res = PSQLexec(buf.data, false); 00694 termPQExpBuffer(&buf); 00695 if (!res) 00696 return false; 00697 00698 myopt.nullPrint = NULL; 00699 myopt.title = _("List of databases"); 00700 myopt.translate_header = true; 00701 00702 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00703 00704 PQclear(res); 00705 return true; 00706 } 00707 00708 00709 /* 00710 * List Tables' Grant/Revoke Permissions 00711 * \z (now also \dp -- perhaps more mnemonic) 00712 */ 00713 bool 00714 permissionsList(const char *pattern) 00715 { 00716 PQExpBufferData buf; 00717 PGresult *res; 00718 printQueryOpt myopt = pset.popt; 00719 static const bool translate_columns[] = {false, false, true, false, false}; 00720 00721 initPQExpBuffer(&buf); 00722 00723 /* 00724 * we ignore indexes and toast tables since they have no meaningful rights 00725 */ 00726 printfPQExpBuffer(&buf, 00727 "SELECT n.nspname as \"%s\",\n" 00728 " c.relname as \"%s\",\n" 00729 " CASE c.relkind" 00730 " WHEN 'r' THEN '%s'" 00731 " WHEN 'v' THEN '%s'" 00732 " WHEN 'm' THEN '%s'" 00733 " WHEN 'S' THEN '%s'" 00734 " WHEN 'f' THEN '%s'" 00735 " END as \"%s\",\n" 00736 " ", 00737 gettext_noop("Schema"), 00738 gettext_noop("Name"), 00739 gettext_noop("table"), 00740 gettext_noop("view"), 00741 gettext_noop("materialized view"), 00742 gettext_noop("sequence"), 00743 gettext_noop("foreign table"), 00744 gettext_noop("Type")); 00745 00746 printACLColumn(&buf, "c.relacl"); 00747 00748 if (pset.sversion >= 80400) 00749 appendPQExpBuffer(&buf, 00750 ",\n pg_catalog.array_to_string(ARRAY(\n" 00751 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n" 00752 " FROM pg_catalog.pg_attribute a\n" 00753 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n" 00754 " ), E'\\n') AS \"%s\"", 00755 gettext_noop("Column access privileges")); 00756 00757 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n" 00758 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" 00759 "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n"); 00760 00761 /* 00762 * Unless a schema pattern is specified, we suppress system and temp 00763 * tables, since they normally aren't very interesting from a permissions 00764 * point of view. You can see 'em by explicit request though, eg with \z 00765 * pg_catalog.* 00766 */ 00767 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00768 "n.nspname", "c.relname", NULL, 00769 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)"); 00770 00771 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 00772 00773 res = PSQLexec(buf.data, false); 00774 if (!res) 00775 { 00776 termPQExpBuffer(&buf); 00777 return false; 00778 } 00779 00780 myopt.nullPrint = NULL; 00781 printfPQExpBuffer(&buf, _("Access privileges")); 00782 myopt.title = buf.data; 00783 myopt.translate_header = true; 00784 myopt.translate_columns = translate_columns; 00785 00786 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00787 00788 termPQExpBuffer(&buf); 00789 PQclear(res); 00790 return true; 00791 } 00792 00793 00794 /* 00795 * \ddp 00796 * 00797 * List Default ACLs. The pattern can match either schema or role name. 00798 */ 00799 bool 00800 listDefaultACLs(const char *pattern) 00801 { 00802 PQExpBufferData buf; 00803 PGresult *res; 00804 printQueryOpt myopt = pset.popt; 00805 static const bool translate_columns[] = {false, false, true, false}; 00806 00807 if (pset.sversion < 90000) 00808 { 00809 psql_error("The server (version %d.%d) does not support altering default privileges.\n", 00810 pset.sversion / 10000, (pset.sversion / 100) % 100); 00811 return true; 00812 } 00813 00814 initPQExpBuffer(&buf); 00815 00816 printfPQExpBuffer(&buf, 00817 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n" 00818 " n.nspname AS \"%s\",\n" 00819 " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n" 00820 " ", 00821 gettext_noop("Owner"), 00822 gettext_noop("Schema"), 00823 DEFACLOBJ_RELATION, 00824 gettext_noop("table"), 00825 DEFACLOBJ_SEQUENCE, 00826 gettext_noop("sequence"), 00827 DEFACLOBJ_FUNCTION, 00828 gettext_noop("function"), 00829 DEFACLOBJ_TYPE, 00830 gettext_noop("type"), 00831 gettext_noop("Type")); 00832 00833 printACLColumn(&buf, "d.defaclacl"); 00834 00835 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_default_acl d\n" 00836 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n"); 00837 00838 processSQLNamePattern(pset.db, &buf, pattern, false, false, 00839 NULL, 00840 "n.nspname", 00841 "pg_catalog.pg_get_userbyid(d.defaclrole)", 00842 NULL); 00843 00844 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;"); 00845 00846 res = PSQLexec(buf.data, false); 00847 if (!res) 00848 { 00849 termPQExpBuffer(&buf); 00850 return false; 00851 } 00852 00853 myopt.nullPrint = NULL; 00854 printfPQExpBuffer(&buf, _("Default access privileges")); 00855 myopt.title = buf.data; 00856 myopt.translate_header = true; 00857 myopt.translate_columns = translate_columns; 00858 00859 printQuery(res, &myopt, pset.queryFout, pset.logfile); 00860 00861 termPQExpBuffer(&buf); 00862 PQclear(res); 00863 return true; 00864 } 00865 00866 00867 /* 00868 * Get object comments 00869 * 00870 * \dd [foo] 00871 * 00872 * Note: This command only lists comments for object types which do not have 00873 * their comments displayed by their own backslash commands. The following 00874 * types of objects will be displayed: constraint, operator class, 00875 * operator family, rule, and trigger. 00876 * 00877 */ 00878 bool 00879 objectDescription(const char *pattern, bool showSystem) 00880 { 00881 PQExpBufferData buf; 00882 PGresult *res; 00883 printQueryOpt myopt = pset.popt; 00884 static const bool translate_columns[] = {false, false, true, false}; 00885 00886 initPQExpBuffer(&buf); 00887 00888 appendPQExpBuffer(&buf, 00889 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n" 00890 "FROM (\n", 00891 gettext_noop("Schema"), 00892 gettext_noop("Name"), 00893 gettext_noop("Object"), 00894 gettext_noop("Description")); 00895 00896 /* Constraint descriptions */ 00897 appendPQExpBuffer(&buf, 00898 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n" 00899 " n.nspname as nspname,\n" 00900 " CAST(pgc.conname AS pg_catalog.text) as name," 00901 " CAST('%s' AS pg_catalog.text) as object\n" 00902 " FROM pg_catalog.pg_constraint pgc\n" 00903 " JOIN pg_catalog.pg_class c " 00904 "ON c.oid = pgc.conrelid\n" 00905 " LEFT JOIN pg_catalog.pg_namespace n " 00906 " ON n.oid = c.relnamespace\n", 00907 gettext_noop("constraint")); 00908 00909 if (!showSystem && !pattern) 00910 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 00911 " AND n.nspname <> 'information_schema'\n"); 00912 00913 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, 00914 false, "n.nspname", "pgc.conname", NULL, 00915 "pg_catalog.pg_table_is_visible(c.oid)"); 00916 00917 /* 00918 * pg_opclass.opcmethod only available in 8.3+ 00919 */ 00920 if (pset.sversion >= 80300) 00921 { 00922 /* Operator class descriptions */ 00923 appendPQExpBuffer(&buf, 00924 "UNION ALL\n" 00925 " SELECT o.oid as oid, o.tableoid as tableoid,\n" 00926 " n.nspname as nspname,\n" 00927 " CAST(o.opcname AS pg_catalog.text) as name,\n" 00928 " CAST('%s' AS pg_catalog.text) as object\n" 00929 " FROM pg_catalog.pg_opclass o\n" 00930 " JOIN pg_catalog.pg_am am ON " 00931 "o.opcmethod = am.oid\n" 00932 " JOIN pg_catalog.pg_namespace n ON " 00933 "n.oid = o.opcnamespace\n", 00934 gettext_noop("operator class")); 00935 00936 if (!showSystem && !pattern) 00937 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00938 " AND n.nspname <> 'information_schema'\n"); 00939 00940 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00941 "n.nspname", "o.opcname", NULL, 00942 "pg_catalog.pg_opclass_is_visible(o.oid)"); 00943 } 00944 00945 /* 00946 * although operator family comments have been around since 8.3, 00947 * pg_opfamily_is_visible is only available in 9.2+ 00948 */ 00949 if (pset.sversion >= 90200) 00950 { 00951 /* Operator family descriptions */ 00952 appendPQExpBuffer(&buf, 00953 "UNION ALL\n" 00954 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n" 00955 " n.nspname as nspname,\n" 00956 " CAST(opf.opfname AS pg_catalog.text) AS name,\n" 00957 " CAST('%s' AS pg_catalog.text) as object\n" 00958 " FROM pg_catalog.pg_opfamily opf\n" 00959 " JOIN pg_catalog.pg_am am " 00960 "ON opf.opfmethod = am.oid\n" 00961 " JOIN pg_catalog.pg_namespace n " 00962 "ON opf.opfnamespace = n.oid\n", 00963 gettext_noop("operator family")); 00964 00965 if (!showSystem && !pattern) 00966 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00967 " AND n.nspname <> 'information_schema'\n"); 00968 00969 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00970 "n.nspname", "opf.opfname", NULL, 00971 "pg_catalog.pg_opfamily_is_visible(opf.oid)"); 00972 } 00973 00974 /* Rule descriptions (ignore rules for views) */ 00975 appendPQExpBuffer(&buf, 00976 "UNION ALL\n" 00977 " SELECT r.oid as oid, r.tableoid as tableoid,\n" 00978 " n.nspname as nspname,\n" 00979 " CAST(r.rulename AS pg_catalog.text) as name," 00980 " CAST('%s' AS pg_catalog.text) as object\n" 00981 " FROM pg_catalog.pg_rewrite r\n" 00982 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n" 00983 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" 00984 " WHERE r.rulename != '_RETURN'\n", 00985 gettext_noop("rule")); 00986 00987 if (!showSystem && !pattern) 00988 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 00989 " AND n.nspname <> 'information_schema'\n"); 00990 00991 processSQLNamePattern(pset.db, &buf, pattern, true, false, 00992 "n.nspname", "r.rulename", NULL, 00993 "pg_catalog.pg_table_is_visible(c.oid)"); 00994 00995 /* Trigger descriptions */ 00996 appendPQExpBuffer(&buf, 00997 "UNION ALL\n" 00998 " SELECT t.oid as oid, t.tableoid as tableoid,\n" 00999 " n.nspname as nspname,\n" 01000 " CAST(t.tgname AS pg_catalog.text) as name," 01001 " CAST('%s' AS pg_catalog.text) as object\n" 01002 " FROM pg_catalog.pg_trigger t\n" 01003 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n" 01004 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n", 01005 gettext_noop("trigger")); 01006 01007 if (!showSystem && !pattern) 01008 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 01009 " AND n.nspname <> 'information_schema'\n"); 01010 01011 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, 01012 "n.nspname", "t.tgname", NULL, 01013 "pg_catalog.pg_table_is_visible(c.oid)"); 01014 01015 appendPQExpBuffer(&buf, 01016 ") AS tt\n" 01017 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n"); 01018 01019 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;"); 01020 01021 res = PSQLexec(buf.data, false); 01022 termPQExpBuffer(&buf); 01023 if (!res) 01024 return false; 01025 01026 myopt.nullPrint = NULL; 01027 myopt.title = _("Object descriptions"); 01028 myopt.translate_header = true; 01029 myopt.translate_columns = translate_columns; 01030 01031 printQuery(res, &myopt, pset.queryFout, pset.logfile); 01032 01033 PQclear(res); 01034 return true; 01035 } 01036 01037 01038 /* 01039 * describeTableDetails (for \d) 01040 * 01041 * This routine finds the tables to be displayed, and calls 01042 * describeOneTableDetails for each one. 01043 * 01044 * verbose: if true, this is \d+ 01045 */ 01046 bool 01047 describeTableDetails(const char *pattern, bool verbose, bool showSystem) 01048 { 01049 PQExpBufferData buf; 01050 PGresult *res; 01051 int i; 01052 01053 initPQExpBuffer(&buf); 01054 01055 printfPQExpBuffer(&buf, 01056 "SELECT c.oid,\n" 01057 " n.nspname,\n" 01058 " c.relname\n" 01059 "FROM pg_catalog.pg_class c\n" 01060 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"); 01061 01062 if (!showSystem && !pattern) 01063 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 01064 " AND n.nspname <> 'information_schema'\n"); 01065 01066 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, 01067 "n.nspname", "c.relname", NULL, 01068 "pg_catalog.pg_table_is_visible(c.oid)"); 01069 01070 appendPQExpBuffer(&buf, "ORDER BY 2, 3;"); 01071 01072 res = PSQLexec(buf.data, false); 01073 termPQExpBuffer(&buf); 01074 if (!res) 01075 return false; 01076 01077 if (PQntuples(res) == 0) 01078 { 01079 if (!pset.quiet) 01080 psql_error("Did not find any relation named \"%s\".\n", 01081 pattern); 01082 PQclear(res); 01083 return false; 01084 } 01085 01086 for (i = 0; i < PQntuples(res); i++) 01087 { 01088 const char *oid; 01089 const char *nspname; 01090 const char *relname; 01091 01092 oid = PQgetvalue(res, i, 0); 01093 nspname = PQgetvalue(res, i, 1); 01094 relname = PQgetvalue(res, i, 2); 01095 01096 if (!describeOneTableDetails(nspname, relname, oid, verbose)) 01097 { 01098 PQclear(res); 01099 return false; 01100 } 01101 if (cancel_pressed) 01102 { 01103 PQclear(res); 01104 return false; 01105 } 01106 } 01107 01108 PQclear(res); 01109 return true; 01110 } 01111 01112 /* 01113 * describeOneTableDetails (for \d) 01114 * 01115 * Unfortunately, the information presented here is so complicated that it 01116 * cannot be done in a single query. So we have to assemble the printed table 01117 * by hand and pass it to the underlying printTable() function. 01118 */ 01119 static bool 01120 describeOneTableDetails(const char *schemaname, 01121 const char *relationname, 01122 const char *oid, 01123 bool verbose) 01124 { 01125 PQExpBufferData buf; 01126 PGresult *res = NULL; 01127 printTableOpt myopt = pset.popt.topt; 01128 printTableContent cont; 01129 bool printTableInitialized = false; 01130 int i; 01131 char *view_def = NULL; 01132 char *headers[9]; 01133 char **seq_values = NULL; 01134 char **modifiers = NULL; 01135 char **ptr; 01136 PQExpBufferData title; 01137 PQExpBufferData tmpbuf; 01138 int cols; 01139 int numrows = 0; 01140 struct 01141 { 01142 int16 checks; 01143 char relkind; 01144 bool hasindex; 01145 bool hasrules; 01146 bool hastriggers; 01147 bool hasoids; 01148 Oid tablespace; 01149 char *reloptions; 01150 char *reloftype; 01151 char relpersistence; 01152 } tableinfo; 01153 bool show_modifiers = false; 01154 bool retval; 01155 01156 retval = false; 01157 01158 myopt.default_footer = false; 01159 /* This output looks confusing in expanded mode. */ 01160 myopt.expanded = false; 01161 01162 initPQExpBuffer(&buf); 01163 initPQExpBuffer(&title); 01164 initPQExpBuffer(&tmpbuf); 01165 01166 /* Get general table info */ 01167 if (pset.sversion >= 90100) 01168 { 01169 printfPQExpBuffer(&buf, 01170 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 01171 "c.relhastriggers, c.relhasoids, " 01172 "%s, c.reltablespace, " 01173 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 01174 "c.relpersistence\n" 01175 "FROM pg_catalog.pg_class c\n " 01176 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 01177 "WHERE c.oid = '%s';", 01178 (verbose ? 01179 "pg_catalog.array_to_string(c.reloptions || " 01180 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 01181 : "''"), 01182 oid); 01183 } 01184 else if (pset.sversion >= 90000) 01185 { 01186 printfPQExpBuffer(&buf, 01187 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 01188 "c.relhastriggers, c.relhasoids, " 01189 "%s, c.reltablespace, " 01190 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n" 01191 "FROM pg_catalog.pg_class c\n " 01192 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 01193 "WHERE c.oid = '%s';", 01194 (verbose ? 01195 "pg_catalog.array_to_string(c.reloptions || " 01196 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 01197 : "''"), 01198 oid); 01199 } 01200 else if (pset.sversion >= 80400) 01201 { 01202 printfPQExpBuffer(&buf, 01203 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 01204 "c.relhastriggers, c.relhasoids, " 01205 "%s, c.reltablespace\n" 01206 "FROM pg_catalog.pg_class c\n " 01207 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 01208 "WHERE c.oid = '%s';", 01209 (verbose ? 01210 "pg_catalog.array_to_string(c.reloptions || " 01211 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 01212 : "''"), 01213 oid); 01214 } 01215 else if (pset.sversion >= 80200) 01216 { 01217 printfPQExpBuffer(&buf, 01218 "SELECT relchecks, relkind, relhasindex, relhasrules, " 01219 "reltriggers <> 0, relhasoids, " 01220 "%s, reltablespace\n" 01221 "FROM pg_catalog.pg_class WHERE oid = '%s';", 01222 (verbose ? 01223 "pg_catalog.array_to_string(reloptions, E', ')" : "''"), 01224 oid); 01225 } 01226 else if (pset.sversion >= 80000) 01227 { 01228 printfPQExpBuffer(&buf, 01229 "SELECT relchecks, relkind, relhasindex, relhasrules, " 01230 "reltriggers <> 0, relhasoids, " 01231 "'', reltablespace\n" 01232 "FROM pg_catalog.pg_class WHERE oid = '%s';", 01233 oid); 01234 } 01235 else 01236 { 01237 printfPQExpBuffer(&buf, 01238 "SELECT relchecks, relkind, relhasindex, relhasrules, " 01239 "reltriggers <> 0, relhasoids, " 01240 "'', ''\n" 01241 "FROM pg_catalog.pg_class WHERE oid = '%s';", 01242 oid); 01243 } 01244 01245 res = PSQLexec(buf.data, false); 01246 if (!res) 01247 goto error_return; 01248 01249 /* Did we get anything? */ 01250 if (PQntuples(res) == 0) 01251 { 01252 if (!pset.quiet) 01253 psql_error("Did not find any relation with OID %s.\n", oid); 01254 goto error_return; 01255 } 01256 01257 tableinfo.checks = atoi(PQgetvalue(res, 0, 0)); 01258 tableinfo.relkind = *(PQgetvalue(res, 0, 1)); 01259 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0; 01260 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0; 01261 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0; 01262 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0; 01263 tableinfo.reloptions = (pset.sversion >= 80200) ? 01264 pg_strdup(PQgetvalue(res, 0, 6)) : NULL; 01265 tableinfo.tablespace = (pset.sversion >= 80000) ? 01266 atooid(PQgetvalue(res, 0, 7)) : 0; 01267 tableinfo.reloftype = (pset.sversion >= 90000 && 01268 strcmp(PQgetvalue(res, 0, 8), "") != 0) ? 01269 pg_strdup(PQgetvalue(res, 0, 8)) : NULL; 01270 tableinfo.relpersistence = (pset.sversion >= 90100) ? 01271 *(PQgetvalue(res, 0, 9)) : 0; 01272 PQclear(res); 01273 res = NULL; 01274 01275 /* 01276 * If it's a sequence, fetch its values and store into an array that will 01277 * be used later. 01278 */ 01279 if (tableinfo.relkind == 'S') 01280 { 01281 printfPQExpBuffer(&buf, "SELECT * FROM %s", fmtId(schemaname)); 01282 /* must be separate because fmtId isn't reentrant */ 01283 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname)); 01284 01285 res = PSQLexec(buf.data, false); 01286 if (!res) 01287 goto error_return; 01288 01289 seq_values = pg_malloc((PQnfields(res) + 1) * sizeof(*seq_values)); 01290 01291 for (i = 0; i < PQnfields(res); i++) 01292 seq_values[i] = pg_strdup(PQgetvalue(res, 0, i)); 01293 seq_values[i] = NULL; 01294 01295 PQclear(res); 01296 res = NULL; 01297 } 01298 01299 /* 01300 * Get column info 01301 * 01302 * You need to modify value of "firstvcol" which will be defined below if 01303 * you are adding column(s) preceding to verbose-only columns. 01304 */ 01305 printfPQExpBuffer(&buf, "SELECT a.attname,"); 01306 appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod)," 01307 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)" 01308 "\n FROM pg_catalog.pg_attrdef d" 01309 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)," 01310 "\n a.attnotnull, a.attnum,"); 01311 if (pset.sversion >= 90100) 01312 appendPQExpBuffer(&buf, "\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n" 01313 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation"); 01314 else 01315 appendPQExpBuffer(&buf, "\n NULL AS attcollation"); 01316 if (tableinfo.relkind == 'i') 01317 appendPQExpBuffer(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef"); 01318 else 01319 appendPQExpBuffer(&buf, ",\n NULL AS indexdef"); 01320 if (tableinfo.relkind == 'f' && pset.sversion >= 90200) 01321 appendPQExpBuffer(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE " 01322 " '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM " 01323 " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions"); 01324 else 01325 appendPQExpBuffer(&buf, ",\n NULL AS attfdwoptions"); 01326 if (verbose) 01327 { 01328 appendPQExpBuffer(&buf, ",\n a.attstorage"); 01329 appendPQExpBuffer(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget"); 01330 01331 /* 01332 * In 9.0+, we have column comments for: relations, views, composite 01333 * types, and foreign tables (c.f. CommentObject() in comment.c). 01334 */ 01335 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || 01336 tableinfo.relkind == 'm' || 01337 tableinfo.relkind == 'f' || tableinfo.relkind == 'c') 01338 appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)"); 01339 } 01340 01341 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a"); 01342 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid); 01343 appendPQExpBuffer(&buf, "\nORDER BY a.attnum;"); 01344 01345 res = PSQLexec(buf.data, false); 01346 if (!res) 01347 goto error_return; 01348 numrows = PQntuples(res); 01349 01350 /* Make title */ 01351 switch (tableinfo.relkind) 01352 { 01353 case 'r': 01354 if (tableinfo.relpersistence == 'u') 01355 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""), 01356 schemaname, relationname); 01357 else 01358 printfPQExpBuffer(&title, _("Table \"%s.%s\""), 01359 schemaname, relationname); 01360 break; 01361 case 'v': 01362 printfPQExpBuffer(&title, _("View \"%s.%s\""), 01363 schemaname, relationname); 01364 break; 01365 case 'm': 01366 if (tableinfo.relpersistence == 'u') 01367 printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""), 01368 schemaname, relationname); 01369 else 01370 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""), 01371 schemaname, relationname); 01372 break; 01373 case 'S': 01374 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""), 01375 schemaname, relationname); 01376 break; 01377 case 'i': 01378 if (tableinfo.relpersistence == 'u') 01379 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""), 01380 schemaname, relationname); 01381 else 01382 printfPQExpBuffer(&title, _("Index \"%s.%s\""), 01383 schemaname, relationname); 01384 break; 01385 case 's': 01386 /* not used as of 8.2, but keep it for backwards compatibility */ 01387 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""), 01388 schemaname, relationname); 01389 break; 01390 case 't': 01391 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""), 01392 schemaname, relationname); 01393 break; 01394 case 'c': 01395 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""), 01396 schemaname, relationname); 01397 break; 01398 case 'f': 01399 printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""), 01400 schemaname, relationname); 01401 break; 01402 default: 01403 /* untranslated unknown relkind */ 01404 printfPQExpBuffer(&title, "?%c? \"%s.%s\"", 01405 tableinfo.relkind, schemaname, relationname); 01406 break; 01407 } 01408 01409 /* Set the number of columns, and their names */ 01410 headers[0] = gettext_noop("Column"); 01411 headers[1] = gettext_noop("Type"); 01412 cols = 2; 01413 01414 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || 01415 tableinfo.relkind == 'm' || 01416 tableinfo.relkind == 'f' || tableinfo.relkind == 'c') 01417 { 01418 show_modifiers = true; 01419 headers[cols++] = gettext_noop("Modifiers"); 01420 modifiers = pg_malloc0((numrows + 1) * sizeof(*modifiers)); 01421 } 01422 01423 if (tableinfo.relkind == 'S') 01424 headers[cols++] = gettext_noop("Value"); 01425 01426 if (tableinfo.relkind == 'i') 01427 headers[cols++] = gettext_noop("Definition"); 01428 01429 if (tableinfo.relkind == 'f' && pset.sversion >= 90200) 01430 headers[cols++] = gettext_noop("FDW Options"); 01431 01432 if (verbose) 01433 { 01434 headers[cols++] = gettext_noop("Storage"); 01435 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || 01436 tableinfo.relkind == 'f') 01437 headers[cols++] = gettext_noop("Stats target"); 01438 /* Column comments, if the relkind supports this feature. */ 01439 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || 01440 tableinfo.relkind == 'm' || 01441 tableinfo.relkind == 'c' || tableinfo.relkind == 'f') 01442 headers[cols++] = gettext_noop("Description"); 01443 } 01444 01445 printTableInit(&cont, &myopt, title.data, cols, numrows); 01446 printTableInitialized = true; 01447 01448 for (i = 0; i < cols; i++) 01449 printTableAddHeader(&cont, headers[i], true, 'l'); 01450 01451 /* Check if table is a view or materialized view */ 01452 if ((tableinfo.relkind == 'v' || tableinfo.relkind == 'm') && verbose) 01453 { 01454 PGresult *result; 01455 01456 printfPQExpBuffer(&buf, 01457 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);", 01458 oid); 01459 result = PSQLexec(buf.data, false); 01460 if (!result) 01461 goto error_return; 01462 01463 if (PQntuples(result) > 0) 01464 view_def = pg_strdup(PQgetvalue(result, 0, 0)); 01465 01466 PQclear(result); 01467 } 01468 01469 /* Generate table cells to be printed */ 01470 for (i = 0; i < numrows; i++) 01471 { 01472 /* Column */ 01473 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false); 01474 01475 /* Type */ 01476 printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false); 01477 01478 /* Modifiers: collate, not null, default */ 01479 if (show_modifiers) 01480 { 01481 resetPQExpBuffer(&tmpbuf); 01482 01483 if (!PQgetisnull(res, i, 5)) 01484 { 01485 if (tmpbuf.len > 0) 01486 appendPQExpBufferStr(&tmpbuf, " "); 01487 appendPQExpBuffer(&tmpbuf, _("collate %s"), 01488 PQgetvalue(res, i, 5)); 01489 } 01490 01491 if (strcmp(PQgetvalue(res, i, 3), "t") == 0) 01492 { 01493 if (tmpbuf.len > 0) 01494 appendPQExpBufferStr(&tmpbuf, " "); 01495 appendPQExpBufferStr(&tmpbuf, _("not null")); 01496 } 01497 01498 /* handle "default" here */ 01499 /* (note: above we cut off the 'default' string at 128) */ 01500 if (strlen(PQgetvalue(res, i, 2)) != 0) 01501 { 01502 if (tmpbuf.len > 0) 01503 appendPQExpBufferStr(&tmpbuf, " "); 01504 /* translator: default values of column definitions */ 01505 appendPQExpBuffer(&tmpbuf, _("default %s"), 01506 PQgetvalue(res, i, 2)); 01507 } 01508 01509 modifiers[i] = pg_strdup(tmpbuf.data); 01510 printTableAddCell(&cont, modifiers[i], false, false); 01511 } 01512 01513 /* Value: for sequences only */ 01514 if (tableinfo.relkind == 'S') 01515 printTableAddCell(&cont, seq_values[i], false, false); 01516 01517 /* Expression for index column */ 01518 if (tableinfo.relkind == 'i') 01519 printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); 01520 01521 /* FDW options for foreign table column, only for 9.2 or later */ 01522 if (tableinfo.relkind == 'f' && pset.sversion >= 90200) 01523 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); 01524 01525 /* Storage and Description */ 01526 if (verbose) 01527 { 01528 int firstvcol = 8; 01529 char *storage = PQgetvalue(res, i, firstvcol); 01530 01531 /* these strings are literal in our syntax, so not translated. */ 01532 printTableAddCell(&cont, (storage[0] == 'p' ? "plain" : 01533 (storage[0] == 'm' ? "main" : 01534 (storage[0] == 'x' ? "extended" : 01535 (storage[0] == 'e' ? "external" : 01536 "???")))), 01537 false, false); 01538 01539 /* Statistics target, if the relkind supports this feature */ 01540 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || 01541 tableinfo.relkind == 'f') 01542 { 01543 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1), 01544 false, false); 01545 } 01546 01547 /* Column comments, if the relkind supports this feature. */ 01548 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' || 01549 tableinfo.relkind == 'm' || 01550 tableinfo.relkind == 'c' || tableinfo.relkind == 'f') 01551 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2), 01552 false, false); 01553 } 01554 } 01555 01556 /* Make footers */ 01557 if (tableinfo.relkind == 'i') 01558 { 01559 /* Footer information about an index */ 01560 PGresult *result; 01561 01562 printfPQExpBuffer(&buf, 01563 "SELECT i.indisunique, i.indisprimary, i.indisclustered, "); 01564 if (pset.sversion >= 80200) 01565 appendPQExpBuffer(&buf, "i.indisvalid,\n"); 01566 else 01567 appendPQExpBuffer(&buf, "true AS indisvalid,\n"); 01568 if (pset.sversion >= 90000) 01569 appendPQExpBuffer(&buf, 01570 " (NOT i.indimmediate) AND " 01571 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint " 01572 "WHERE conrelid = i.indrelid AND " 01573 "conindid = i.indexrelid AND " 01574 "contype IN ('p','u','x') AND " 01575 "condeferrable) AS condeferrable,\n" 01576 " (NOT i.indimmediate) AND " 01577 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint " 01578 "WHERE conrelid = i.indrelid AND " 01579 "conindid = i.indexrelid AND " 01580 "contype IN ('p','u','x') AND " 01581 "condeferred) AS condeferred,\n"); 01582 else 01583 appendPQExpBuffer(&buf, 01584 " false AS condeferrable, false AS condeferred,\n"); 01585 appendPQExpBuffer(&buf, " a.amname, c2.relname, " 01586 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" 01587 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n" 01588 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n" 01589 "AND i.indrelid = c2.oid;", 01590 oid); 01591 01592 result = PSQLexec(buf.data, false); 01593 if (!result) 01594 goto error_return; 01595 else if (PQntuples(result) != 1) 01596 { 01597 PQclear(result); 01598 goto error_return; 01599 } 01600 else 01601 { 01602 char *indisunique = PQgetvalue(result, 0, 0); 01603 char *indisprimary = PQgetvalue(result, 0, 1); 01604 char *indisclustered = PQgetvalue(result, 0, 2); 01605 char *indisvalid = PQgetvalue(result, 0, 3); 01606 char *deferrable = PQgetvalue(result, 0, 4); 01607 char *deferred = PQgetvalue(result, 0, 5); 01608 char *indamname = PQgetvalue(result, 0, 6); 01609 char *indtable = PQgetvalue(result, 0, 7); 01610 char *indpred = PQgetvalue(result, 0, 8); 01611 01612 if (strcmp(indisprimary, "t") == 0) 01613 printfPQExpBuffer(&tmpbuf, _("primary key, ")); 01614 else if (strcmp(indisunique, "t") == 0) 01615 printfPQExpBuffer(&tmpbuf, _("unique, ")); 01616 else 01617 resetPQExpBuffer(&tmpbuf); 01618 appendPQExpBuffer(&tmpbuf, "%s, ", indamname); 01619 01620 /* we assume here that index and table are in same schema */ 01621 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""), 01622 schemaname, indtable); 01623 01624 if (strlen(indpred)) 01625 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred); 01626 01627 if (strcmp(indisclustered, "t") == 0) 01628 appendPQExpBuffer(&tmpbuf, _(", clustered")); 01629 01630 if (strcmp(indisvalid, "t") != 0) 01631 appendPQExpBuffer(&tmpbuf, _(", invalid")); 01632 01633 if (strcmp(deferrable, "t") == 0) 01634 appendPQExpBuffer(&tmpbuf, _(", deferrable")); 01635 01636 if (strcmp(deferred, "t") == 0) 01637 appendPQExpBuffer(&tmpbuf, _(", initially deferred")); 01638 01639 printTableAddFooter(&cont, tmpbuf.data); 01640 add_tablespace_footer(&cont, tableinfo.relkind, 01641 tableinfo.tablespace, true); 01642 } 01643 01644 PQclear(result); 01645 } 01646 else if (tableinfo.relkind == 'S') 01647 { 01648 /* Footer information about a sequence */ 01649 PGresult *result = NULL; 01650 01651 /* Get the column that owns this sequence */ 01652 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||" 01653 "\n pg_catalog.quote_ident(relname) || '.' ||" 01654 "\n pg_catalog.quote_ident(attname)" 01655 "\nFROM pg_catalog.pg_class c" 01656 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid" 01657 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace" 01658 "\nINNER JOIN pg_catalog.pg_attribute a ON (" 01659 "\n a.attrelid=c.oid AND" 01660 "\n a.attnum=d.refobjsubid)" 01661 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass" 01662 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass" 01663 "\n AND d.objid=%s" 01664 "\n AND d.deptype='a'", 01665 oid); 01666 01667 result = PSQLexec(buf.data, false); 01668 if (!result) 01669 goto error_return; 01670 else if (PQntuples(result) == 1) 01671 { 01672 printfPQExpBuffer(&buf, _("Owned by: %s"), 01673 PQgetvalue(result, 0, 0)); 01674 printTableAddFooter(&cont, buf.data); 01675 } 01676 01677 /* 01678 * If we get no rows back, don't show anything (obviously). We should 01679 * never get more than one row back, but if we do, just ignore it and 01680 * don't print anything. 01681 */ 01682 PQclear(result); 01683 } 01684 else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || 01685 tableinfo.relkind == 'f') 01686 { 01687 /* Footer information about a table */ 01688 PGresult *result = NULL; 01689 int tuples = 0; 01690 01691 /* print indexes */ 01692 if (tableinfo.hasindex) 01693 { 01694 printfPQExpBuffer(&buf, 01695 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, "); 01696 if (pset.sversion >= 80200) 01697 appendPQExpBuffer(&buf, "i.indisvalid, "); 01698 else 01699 appendPQExpBuffer(&buf, "true as indisvalid, "); 01700 appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "); 01701 if (pset.sversion >= 90000) 01702 appendPQExpBuffer(&buf, 01703 "pg_catalog.pg_get_constraintdef(con.oid, true), " 01704 "contype, condeferrable, condeferred"); 01705 else 01706 appendPQExpBuffer(&buf, 01707 "null AS constraintdef, null AS contype, " 01708 "false AS condeferrable, false AS condeferred"); 01709 if (pset.sversion >= 80000) 01710 appendPQExpBuffer(&buf, ", c2.reltablespace"); 01711 appendPQExpBuffer(&buf, 01712 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"); 01713 if (pset.sversion >= 90000) 01714 appendPQExpBuffer(&buf, 01715 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n"); 01716 appendPQExpBuffer(&buf, 01717 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n" 01718 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;", 01719 oid); 01720 result = PSQLexec(buf.data, false); 01721 if (!result) 01722 goto error_return; 01723 else 01724 tuples = PQntuples(result); 01725 01726 if (tuples > 0) 01727 { 01728 printTableAddFooter(&cont, _("Indexes:")); 01729 for (i = 0; i < tuples; i++) 01730 { 01731 /* untranslated index name */ 01732 printfPQExpBuffer(&buf, " \"%s\"", 01733 PQgetvalue(result, i, 0)); 01734 01735 /* If exclusion constraint, print the constraintdef */ 01736 if (strcmp(PQgetvalue(result, i, 7), "x") == 0) 01737 { 01738 appendPQExpBuffer(&buf, " %s", 01739 PQgetvalue(result, i, 6)); 01740 } 01741 else 01742 { 01743 const char *indexdef; 01744 const char *usingpos; 01745 01746 /* Label as primary key or unique (but not both) */ 01747 if (strcmp(PQgetvalue(result, i, 1), "t") == 0) 01748 appendPQExpBuffer(&buf, " PRIMARY KEY,"); 01749 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0) 01750 { 01751 if (strcmp(PQgetvalue(result, i, 7), "u") == 0) 01752 appendPQExpBuffer(&buf, " UNIQUE CONSTRAINT,"); 01753 else 01754 appendPQExpBuffer(&buf, " UNIQUE,"); 01755 } 01756 01757 /* Everything after "USING" is echoed verbatim */ 01758 indexdef = PQgetvalue(result, i, 5); 01759 usingpos = strstr(indexdef, " USING "); 01760 if (usingpos) 01761 indexdef = usingpos + 7; 01762 appendPQExpBuffer(&buf, " %s", indexdef); 01763 01764 /* Need these for deferrable PK/UNIQUE indexes */ 01765 if (strcmp(PQgetvalue(result, i, 8), "t") == 0) 01766 appendPQExpBuffer(&buf, " DEFERRABLE"); 01767 01768 if (strcmp(PQgetvalue(result, i, 9), "t") == 0) 01769 appendPQExpBuffer(&buf, " INITIALLY DEFERRED"); 01770 } 01771 01772 /* Add these for all cases */ 01773 if (strcmp(PQgetvalue(result, i, 3), "t") == 0) 01774 appendPQExpBuffer(&buf, " CLUSTER"); 01775 01776 if (strcmp(PQgetvalue(result, i, 4), "t") != 0) 01777 appendPQExpBuffer(&buf, " INVALID"); 01778 01779 printTableAddFooter(&cont, buf.data); 01780 01781 /* Print tablespace of the index on the same line */ 01782 if (pset.sversion >= 80000) 01783 add_tablespace_footer(&cont, 'i', 01784 atooid(PQgetvalue(result, i, 10)), 01785 false); 01786 } 01787 } 01788 PQclear(result); 01789 } 01790 01791 /* print table (and column) check constraints */ 01792 if (tableinfo.checks) 01793 { 01794 printfPQExpBuffer(&buf, 01795 "SELECT r.conname, " 01796 "pg_catalog.pg_get_constraintdef(r.oid, true)\n" 01797 "FROM pg_catalog.pg_constraint r\n" 01798 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n" 01799 "ORDER BY 1;", 01800 oid); 01801 result = PSQLexec(buf.data, false); 01802 if (!result) 01803 goto error_return; 01804 else 01805 tuples = PQntuples(result); 01806 01807 if (tuples > 0) 01808 { 01809 printTableAddFooter(&cont, _("Check constraints:")); 01810 for (i = 0; i < tuples; i++) 01811 { 01812 /* untranslated contraint name and def */ 01813 printfPQExpBuffer(&buf, " \"%s\" %s", 01814 PQgetvalue(result, i, 0), 01815 PQgetvalue(result, i, 1)); 01816 01817 printTableAddFooter(&cont, buf.data); 01818 } 01819 } 01820 PQclear(result); 01821 } 01822 01823 /* print foreign-key constraints (there are none if no triggers) */ 01824 if (tableinfo.hastriggers) 01825 { 01826 printfPQExpBuffer(&buf, 01827 "SELECT conname,\n" 01828 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n" 01829 "FROM pg_catalog.pg_constraint r\n" 01830 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;", 01831 oid); 01832 result = PSQLexec(buf.data, false); 01833 if (!result) 01834 goto error_return; 01835 else 01836 tuples = PQntuples(result); 01837 01838 if (tuples > 0) 01839 { 01840 printTableAddFooter(&cont, _("Foreign-key constraints:")); 01841 for (i = 0; i < tuples; i++) 01842 { 01843 /* untranslated constraint name and def */ 01844 printfPQExpBuffer(&buf, " \"%s\" %s", 01845 PQgetvalue(result, i, 0), 01846 PQgetvalue(result, i, 1)); 01847 01848 printTableAddFooter(&cont, buf.data); 01849 } 01850 } 01851 PQclear(result); 01852 } 01853 01854 /* print incoming foreign-key references (none if no triggers) */ 01855 if (tableinfo.hastriggers) 01856 { 01857 printfPQExpBuffer(&buf, 01858 "SELECT conname, conrelid::pg_catalog.regclass,\n" 01859 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n" 01860 "FROM pg_catalog.pg_constraint c\n" 01861 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;", 01862 oid); 01863 result = PSQLexec(buf.data, false); 01864 if (!result) 01865 goto error_return; 01866 else 01867 tuples = PQntuples(result); 01868 01869 if (tuples > 0) 01870 { 01871 printTableAddFooter(&cont, _("Referenced by:")); 01872 for (i = 0; i < tuples; i++) 01873 { 01874 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", 01875 PQgetvalue(result, i, 1), 01876 PQgetvalue(result, i, 0), 01877 PQgetvalue(result, i, 2)); 01878 01879 printTableAddFooter(&cont, buf.data); 01880 } 01881 } 01882 PQclear(result); 01883 } 01884 01885 /* print rules */ 01886 if (tableinfo.hasrules && tableinfo.relkind != 'm') 01887 { 01888 if (pset.sversion >= 80300) 01889 { 01890 printfPQExpBuffer(&buf, 01891 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " 01892 "ev_enabled\n" 01893 "FROM pg_catalog.pg_rewrite r\n" 01894 "WHERE r.ev_class = '%s' ORDER BY 1;", 01895 oid); 01896 } 01897 else 01898 { 01899 printfPQExpBuffer(&buf, 01900 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " 01901 "'O'::char AS ev_enabled\n" 01902 "FROM pg_catalog.pg_rewrite r\n" 01903 "WHERE r.ev_class = '%s' ORDER BY 1;", 01904 oid); 01905 } 01906 result = PSQLexec(buf.data, false); 01907 if (!result) 01908 goto error_return; 01909 else 01910 tuples = PQntuples(result); 01911 01912 if (tuples > 0) 01913 { 01914 bool have_heading; 01915 int category; 01916 01917 for (category = 0; category < 4; category++) 01918 { 01919 have_heading = false; 01920 01921 for (i = 0; i < tuples; i++) 01922 { 01923 const char *ruledef; 01924 bool list_rule = false; 01925 01926 switch (category) 01927 { 01928 case 0: 01929 if (*PQgetvalue(result, i, 2) == 'O') 01930 list_rule = true; 01931 break; 01932 case 1: 01933 if (*PQgetvalue(result, i, 2) == 'D') 01934 list_rule = true; 01935 break; 01936 case 2: 01937 if (*PQgetvalue(result, i, 2) == 'A') 01938 list_rule = true; 01939 break; 01940 case 3: 01941 if (*PQgetvalue(result, i, 2) == 'R') 01942 list_rule = true; 01943 break; 01944 } 01945 if (!list_rule) 01946 continue; 01947 01948 if (!have_heading) 01949 { 01950 switch (category) 01951 { 01952 case 0: 01953 printfPQExpBuffer(&buf, _("Rules:")); 01954 break; 01955 case 1: 01956 printfPQExpBuffer(&buf, _("Disabled rules:")); 01957 break; 01958 case 2: 01959 printfPQExpBuffer(&buf, _("Rules firing always:")); 01960 break; 01961 case 3: 01962 printfPQExpBuffer(&buf, _("Rules firing on replica only:")); 01963 break; 01964 } 01965 printTableAddFooter(&cont, buf.data); 01966 have_heading = true; 01967 } 01968 01969 /* Everything after "CREATE RULE" is echoed verbatim */ 01970 ruledef = PQgetvalue(result, i, 1); 01971 ruledef += 12; 01972 printfPQExpBuffer(&buf, " %s", ruledef); 01973 printTableAddFooter(&cont, buf.data); 01974 } 01975 } 01976 } 01977 PQclear(result); 01978 } 01979 } 01980 01981 if (view_def) 01982 { 01983 PGresult *result = NULL; 01984 01985 /* Footer information about a view */ 01986 printTableAddFooter(&cont, _("View definition:")); 01987 printTableAddFooter(&cont, view_def); 01988 01989 /* print rules */ 01990 if (tableinfo.hasrules) 01991 { 01992 printfPQExpBuffer(&buf, 01993 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" 01994 "FROM pg_catalog.pg_rewrite r\n" 01995 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", 01996 oid); 01997 result = PSQLexec(buf.data, false); 01998 if (!result) 01999 goto error_return; 02000 02001 if (PQntuples(result) > 0) 02002 { 02003 printTableAddFooter(&cont, _("Rules:")); 02004 for (i = 0; i < PQntuples(result); i++) 02005 { 02006 const char *ruledef; 02007 02008 /* Everything after "CREATE RULE" is echoed verbatim */ 02009 ruledef = PQgetvalue(result, i, 1); 02010 ruledef += 12; 02011 02012 printfPQExpBuffer(&buf, " %s", ruledef); 02013 printTableAddFooter(&cont, buf.data); 02014 } 02015 } 02016 PQclear(result); 02017 } 02018 } 02019 02020 /* 02021 * Print triggers next, if any (but only user-defined triggers). This 02022 * could apply to either a table or a view. 02023 */ 02024 if (tableinfo.hastriggers) 02025 { 02026 PGresult *result; 02027 int tuples; 02028 02029 printfPQExpBuffer(&buf, 02030 "SELECT t.tgname, " 02031 "pg_catalog.pg_get_triggerdef(t.oid%s), " 02032 "t.tgenabled\n" 02033 "FROM pg_catalog.pg_trigger t\n" 02034 "WHERE t.tgrelid = '%s' AND ", 02035 (pset.sversion >= 90000 ? ", true" : ""), 02036 oid); 02037 if (pset.sversion >= 90000) 02038 appendPQExpBuffer(&buf, "NOT t.tgisinternal"); 02039 else if (pset.sversion >= 80300) 02040 appendPQExpBuffer(&buf, "t.tgconstraint = 0"); 02041 else 02042 appendPQExpBuffer(&buf, 02043 "(NOT tgisconstraint " 02044 " OR NOT EXISTS" 02045 " (SELECT 1 FROM pg_catalog.pg_depend d " 02046 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) " 02047 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))"); 02048 appendPQExpBuffer(&buf, "\nORDER BY 1;"); 02049 02050 result = PSQLexec(buf.data, false); 02051 if (!result) 02052 goto error_return; 02053 else 02054 tuples = PQntuples(result); 02055 02056 if (tuples > 0) 02057 { 02058 bool have_heading; 02059 int category; 02060 02061 /* 02062 * split the output into 4 different categories. Enabled triggers, 02063 * disabled triggers and the two special ALWAYS and REPLICA 02064 * configurations. 02065 */ 02066 for (category = 0; category < 4; category++) 02067 { 02068 have_heading = false; 02069 for (i = 0; i < tuples; i++) 02070 { 02071 bool list_trigger; 02072 const char *tgdef; 02073 const char *usingpos; 02074 const char *tgenabled; 02075 02076 /* 02077 * Check if this trigger falls into the current category 02078 */ 02079 tgenabled = PQgetvalue(result, i, 2); 02080 list_trigger = false; 02081 switch (category) 02082 { 02083 case 0: 02084 if (*tgenabled == 'O' || *tgenabled == 't') 02085 list_trigger = true; 02086 break; 02087 case 1: 02088 if (*tgenabled == 'D' || *tgenabled == 'f') 02089 list_trigger = true; 02090 break; 02091 case 2: 02092 if (*tgenabled == 'A') 02093 list_trigger = true; 02094 break; 02095 case 3: 02096 if (*tgenabled == 'R') 02097 list_trigger = true; 02098 break; 02099 } 02100 if (list_trigger == false) 02101 continue; 02102 02103 /* Print the category heading once */ 02104 if (have_heading == false) 02105 { 02106 switch (category) 02107 { 02108 case 0: 02109 printfPQExpBuffer(&buf, _("Triggers:")); 02110 break; 02111 case 1: 02112 printfPQExpBuffer(&buf, _("Disabled triggers:")); 02113 break; 02114 case 2: 02115 printfPQExpBuffer(&buf, _("Triggers firing always:")); 02116 break; 02117 case 3: 02118 printfPQExpBuffer(&buf, _("Triggers firing on replica only:")); 02119 break; 02120 02121 } 02122 printTableAddFooter(&cont, buf.data); 02123 have_heading = true; 02124 } 02125 02126 /* Everything after "TRIGGER" is echoed verbatim */ 02127 tgdef = PQgetvalue(result, i, 1); 02128 usingpos = strstr(tgdef, " TRIGGER "); 02129 if (usingpos) 02130 tgdef = usingpos + 9; 02131 02132 printfPQExpBuffer(&buf, " %s", tgdef); 02133 printTableAddFooter(&cont, buf.data); 02134 } 02135 } 02136 } 02137 PQclear(result); 02138 } 02139 02140 /* 02141 * Finish printing the footer information about a table. 02142 */ 02143 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' || 02144 tableinfo.relkind == 'f') 02145 { 02146 PGresult *result; 02147 int tuples; 02148 02149 /* print foreign server name */ 02150 if (tableinfo.relkind == 'f') 02151 { 02152 char *ftoptions; 02153 02154 /* Footer information about foreign table */ 02155 printfPQExpBuffer(&buf, 02156 "SELECT s.srvname,\n" 02157 " array_to_string(ARRAY(SELECT " 02158 " quote_ident(option_name) || ' ' || " 02159 " quote_literal(option_value) FROM " 02160 " pg_options_to_table(ftoptions)), ', ') " 02161 "FROM pg_catalog.pg_foreign_table f,\n" 02162 " pg_catalog.pg_foreign_server s\n" 02163 "WHERE f.ftrelid = %s AND s.oid = f.ftserver;", 02164 oid); 02165 result = PSQLexec(buf.data, false); 02166 if (!result) 02167 goto error_return; 02168 else if (PQntuples(result) != 1) 02169 { 02170 PQclear(result); 02171 goto error_return; 02172 } 02173 02174 /* Print server name */ 02175 printfPQExpBuffer(&buf, "Server: %s", 02176 PQgetvalue(result, 0, 0)); 02177 printTableAddFooter(&cont, buf.data); 02178 02179 /* Print per-table FDW options, if any */ 02180 ftoptions = PQgetvalue(result, 0, 1); 02181 if (ftoptions && ftoptions[0] != '\0') 02182 { 02183 printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions); 02184 printTableAddFooter(&cont, buf.data); 02185 } 02186 PQclear(result); 02187 } 02188 02189 /* print inherited tables */ 02190 printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid); 02191 02192 result = PSQLexec(buf.data, false); 02193 if (!result) 02194 goto error_return; 02195 else 02196 { 02197 const char *s = _("Inherits"); 02198 int sw = pg_wcswidth(s, strlen(s), pset.encoding); 02199 02200 tuples = PQntuples(result); 02201 02202 for (i = 0; i < tuples; i++) 02203 { 02204 if (i == 0) 02205 printfPQExpBuffer(&buf, "%s: %s", 02206 s, PQgetvalue(result, i, 0)); 02207 else 02208 printfPQExpBuffer(&buf, "%*s %s", 02209 sw, "", PQgetvalue(result, i, 0)); 02210 if (i < tuples - 1) 02211 appendPQExpBuffer(&buf, ","); 02212 02213 printTableAddFooter(&cont, buf.data); 02214 } 02215 02216 PQclear(result); 02217 } 02218 02219 /* print child tables */ 02220 if (pset.sversion >= 80300) 02221 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.oid::pg_catalog.regclass::pg_catalog.text;", oid); 02222 else 02223 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); 02224 02225 result = PSQLexec(buf.data, false); 02226 if (!result) 02227 goto error_return; 02228 else 02229 tuples = PQntuples(result); 02230 02231 if (!verbose) 02232 { 02233 /* print the number of child tables, if any */ 02234 if (tuples > 0) 02235 { 02236 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); 02237 printTableAddFooter(&cont, buf.data); 02238 } 02239 } 02240 else 02241 { 02242 /* display the list of child tables */ 02243 const char *ct = _("Child tables"); 02244 int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); 02245 02246 for (i = 0; i < tuples; i++) 02247 { 02248 if (i == 0) 02249 printfPQExpBuffer(&buf, "%s: %s", 02250 ct, PQgetvalue(result, i, 0)); 02251 else 02252 printfPQExpBuffer(&buf, "%*s %s", 02253 ctw, "", PQgetvalue(result, i, 0)); 02254 if (i < tuples - 1) 02255 appendPQExpBuffer(&buf, ","); 02256 02257 printTableAddFooter(&cont, buf.data); 02258 } 02259 } 02260 PQclear(result); 02261 02262 /* Table type */ 02263 if (tableinfo.reloftype) 02264 { 02265 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype); 02266 printTableAddFooter(&cont, buf.data); 02267 } 02268 02269 /* OIDs, if verbose and not a materialized view */ 02270 if (verbose && tableinfo.relkind != 'm') 02271 { 02272 const char *s = _("Has OIDs"); 02273 02274 printfPQExpBuffer(&buf, "%s: %s", s, 02275 (tableinfo.hasoids ? _("yes") : _("no"))); 02276 printTableAddFooter(&cont, buf.data); 02277 } 02278 02279 /* Tablespace info */ 02280 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, 02281 true); 02282 } 02283 02284 /* reloptions, if verbose */ 02285 if (verbose && 02286 tableinfo.reloptions && tableinfo.reloptions[0] != '\0') 02287 { 02288 const char *t = _("Options"); 02289 02290 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions); 02291 printTableAddFooter(&cont, buf.data); 02292 } 02293 02294 printTable(&cont, pset.queryFout, pset.logfile); 02295 printTableCleanup(&cont); 02296 02297 retval = true; 02298 02299 error_return: 02300 02301 /* clean up */ 02302 if (printTableInitialized) 02303 printTableCleanup(&cont); 02304 termPQExpBuffer(&buf); 02305 termPQExpBuffer(&title); 02306 termPQExpBuffer(&tmpbuf); 02307 02308 if (seq_values) 02309 { 02310 for (ptr = seq_values; *ptr; ptr++) 02311 free(*ptr); 02312 free(seq_values); 02313 } 02314 02315 if (modifiers) 02316 { 02317 for (ptr = modifiers; *ptr; ptr++) 02318 free(*ptr); 02319 free(modifiers); 02320 } 02321 02322 if (view_def) 02323 free(view_def); 02324 02325 if (res) 02326 PQclear(res); 02327 02328 return retval; 02329 } 02330 02331 /* 02332 * Add a tablespace description to a footer. If 'newline' is true, it is added 02333 * in a new line; otherwise it's appended to the current value of the last 02334 * footer. 02335 */ 02336 static void 02337 add_tablespace_footer(printTableContent *const cont, char relkind, 02338 Oid tablespace, const bool newline) 02339 { 02340 /* relkinds for which we support tablespaces */ 02341 if (relkind == 'r' || relkind == 'm' || relkind == 'i') 02342 { 02343 /* 02344 * We ignore the database default tablespace so that users not using 02345 * tablespaces don't need to know about them. This case also covers 02346 * pre-8.0 servers, for which tablespace will always be 0. 02347 */ 02348 if (tablespace != 0) 02349 { 02350 PGresult *result = NULL; 02351 PQExpBufferData buf; 02352 02353 initPQExpBuffer(&buf); 02354 printfPQExpBuffer(&buf, 02355 "SELECT spcname FROM pg_catalog.pg_tablespace\n" 02356 "WHERE oid = '%u';", tablespace); 02357 result = PSQLexec(buf.data, false); 02358 if (!result) 02359 return; 02360 /* Should always be the case, but.... */ 02361 if (PQntuples(result) > 0) 02362 { 02363 if (newline) 02364 { 02365 /* Add the tablespace as a new footer */ 02366 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""), 02367 PQgetvalue(result, 0, 0)); 02368 printTableAddFooter(cont, buf.data); 02369 } 02370 else 02371 { 02372 /* Append the tablespace to the latest footer */ 02373 printfPQExpBuffer(&buf, "%s", cont->footer->data); 02374 02375 /* 02376 * translator: before this string there's an index 02377 * description like '"foo_pkey" PRIMARY KEY, btree (a)' 02378 */ 02379 appendPQExpBuffer(&buf, _(", tablespace \"%s\""), 02380 PQgetvalue(result, 0, 0)); 02381 printTableSetFooter(cont, buf.data); 02382 } 02383 } 02384 PQclear(result); 02385 termPQExpBuffer(&buf); 02386 } 02387 } 02388 } 02389 02390 /* 02391 * \du or \dg 02392 * 02393 * Describes roles. Any schema portion of the pattern is ignored. 02394 */ 02395 bool 02396 describeRoles(const char *pattern, bool verbose) 02397 { 02398 PQExpBufferData buf; 02399 PGresult *res; 02400 printTableContent cont; 02401 printTableOpt myopt = pset.popt.topt; 02402 int ncols = 3; 02403 int nrows = 0; 02404 int i; 02405 int conns; 02406 const char align = 'l'; 02407 char **attr; 02408 02409 myopt.default_footer = false; 02410 02411 initPQExpBuffer(&buf); 02412 02413 if (pset.sversion >= 80100) 02414 { 02415 printfPQExpBuffer(&buf, 02416 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n" 02417 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n" 02418 " r.rolconnlimit, r.rolvaliduntil,\n" 02419 " ARRAY(SELECT b.rolname\n" 02420 " FROM pg_catalog.pg_auth_members m\n" 02421 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n" 02422 " WHERE m.member = r.oid) as memberof"); 02423 02424 if (verbose && pset.sversion >= 80200) 02425 { 02426 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description"); 02427 ncols++; 02428 } 02429 if (pset.sversion >= 90100) 02430 { 02431 appendPQExpBufferStr(&buf, "\n, r.rolreplication"); 02432 } 02433 02434 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n"); 02435 02436 processSQLNamePattern(pset.db, &buf, pattern, false, false, 02437 NULL, "r.rolname", NULL, NULL); 02438 } 02439 else 02440 { 02441 printfPQExpBuffer(&buf, 02442 "SELECT u.usename AS rolname,\n" 02443 " u.usesuper AS rolsuper,\n" 02444 " true AS rolinherit, false AS rolcreaterole,\n" 02445 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n" 02446 " -1 AS rolconnlimit," 02447 " u.valuntil as rolvaliduntil,\n" 02448 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof" 02449 "\nFROM pg_catalog.pg_user u\n"); 02450 02451 processSQLNamePattern(pset.db, &buf, pattern, false, false, 02452 NULL, "u.usename", NULL, NULL); 02453 } 02454 02455 appendPQExpBuffer(&buf, "ORDER BY 1;"); 02456 02457 res = PSQLexec(buf.data, false); 02458 if (!res) 02459 return false; 02460 02461 nrows = PQntuples(res); 02462 attr = pg_malloc0((nrows + 1) * sizeof(*attr)); 02463 02464 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows); 02465 02466 printTableAddHeader(&cont, gettext_noop("Role name"), true, align); 02467 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align); 02468 printTableAddHeader(&cont, gettext_noop("Member of"), true, align); 02469 02470 if (verbose && pset.sversion >= 80200) 02471 printTableAddHeader(&cont, gettext_noop("Description"), true, align); 02472 02473 for (i = 0; i < nrows; i++) 02474 { 02475 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false); 02476 02477 resetPQExpBuffer(&buf); 02478 if (strcmp(PQgetvalue(res, i, 1), "t") == 0) 02479 add_role_attribute(&buf, _("Superuser")); 02480 02481 if (strcmp(PQgetvalue(res, i, 2), "t") != 0) 02482 add_role_attribute(&buf, _("No inheritance")); 02483 02484 if (strcmp(PQgetvalue(res, i, 3), "t") == 0) 02485 add_role_attribute(&buf, _("Create role")); 02486 02487 if (strcmp(PQgetvalue(res, i, 4), "t") == 0) 02488 add_role_attribute(&buf, _("Create DB")); 02489 02490 if (strcmp(PQgetvalue(res, i, 5), "t") != 0) 02491 add_role_attribute(&buf, _("Cannot login")); 02492 02493 if (pset.sversion >= 90100) 02494 if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0) 02495 add_role_attribute(&buf, _("Replication")); 02496 02497 conns = atoi(PQgetvalue(res, i, 6)); 02498 if (conns >= 0) 02499 { 02500 if (buf.len > 0) 02501 appendPQExpBufferStr(&buf, "\n"); 02502 02503 if (conns == 0) 02504 appendPQExpBuffer(&buf, _("No connections")); 02505 else 02506 appendPQExpBuffer(&buf, ngettext("%d connection", 02507 "%d connections", 02508 conns), 02509 conns); 02510 } 02511 02512 if (strcmp(PQgetvalue(res, i, 7), "") != 0) 02513 { 02514 if (buf.len > 0) 02515 appendPQExpBufferStr(&buf, "\n"); 02516 appendPQExpBufferStr(&buf, _("Password valid until ")); 02517 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7)); 02518 } 02519 02520 attr[i] = pg_strdup(buf.data); 02521 02522 printTableAddCell(&cont, attr[i], false, false); 02523 02524 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); 02525 02526 if (verbose && pset.sversion >= 80200) 02527 printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); 02528 } 02529 termPQExpBuffer(&buf); 02530 02531 printTable(&cont, pset.queryFout, pset.logfile); 02532 printTableCleanup(&cont); 02533 02534 for (i = 0; i < nrows; i++) 02535 free(attr[i]); 02536 free(attr); 02537 02538 PQclear(res); 02539 return true; 02540 } 02541 02542 static void 02543 add_role_attribute(PQExpBuffer buf, const char *const str) 02544 { 02545 if (buf->len > 0) 02546 appendPQExpBufferStr(buf, ", "); 02547 02548 appendPQExpBufferStr(buf, str); 02549 } 02550 02551 /* 02552 * \drds 02553 */ 02554 bool 02555 listDbRoleSettings(const char *pattern, const char *pattern2) 02556 { 02557 PQExpBufferData buf; 02558 PGresult *res; 02559 printQueryOpt myopt = pset.popt; 02560 02561 initPQExpBuffer(&buf); 02562 02563 if (pset.sversion >= 90000) 02564 { 02565 bool havewhere; 02566 02567 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n" 02568 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n" 02569 "FROM pg_db_role_setting AS s\n" 02570 "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n" 02571 "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n", 02572 gettext_noop("Role"), 02573 gettext_noop("Database"), 02574 gettext_noop("Settings")); 02575 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false, 02576 NULL, "pg_roles.rolname", NULL, NULL); 02577 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false, 02578 NULL, "pg_database.datname", NULL, NULL); 02579 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 02580 } 02581 else 02582 { 02583 fprintf(pset.queryFout, 02584 _("No per-database role settings support in this server version.\n")); 02585 return false; 02586 } 02587 02588 res = PSQLexec(buf.data, false); 02589 if (!res) 02590 return false; 02591 02592 if (PQntuples(res) == 0 && !pset.quiet) 02593 { 02594 if (pattern) 02595 fprintf(pset.queryFout, _("No matching settings found.\n")); 02596 else 02597 fprintf(pset.queryFout, _("No settings found.\n")); 02598 } 02599 else 02600 { 02601 myopt.nullPrint = NULL; 02602 myopt.title = _("List of settings"); 02603 myopt.translate_header = true; 02604 02605 printQuery(res, &myopt, pset.queryFout, pset.logfile); 02606 } 02607 02608 PQclear(res); 02609 resetPQExpBuffer(&buf); 02610 return true; 02611 } 02612 02613 02614 /* 02615 * listTables() 02616 * 02617 * handler for \dt, \di, etc. 02618 * 02619 * tabtypes is an array of characters, specifying what info is desired: 02620 * t - tables 02621 * i - indexes 02622 * v - views 02623 * m - materialized views 02624 * s - sequences 02625 * E - foreign table (Note: different from 'f', the relkind value) 02626 * (any order of the above is fine) 02627 * If tabtypes is empty, we default to \dtvsE. 02628 */ 02629 bool 02630 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem) 02631 { 02632 bool showTables = strchr(tabtypes, 't') != NULL; 02633 bool showIndexes = strchr(tabtypes, 'i') != NULL; 02634 bool showViews = strchr(tabtypes, 'v') != NULL; 02635 bool showMatViews = strchr(tabtypes, 'm') != NULL; 02636 bool showSeq = strchr(tabtypes, 's') != NULL; 02637 bool showForeign = strchr(tabtypes, 'E') != NULL; 02638 02639 PQExpBufferData buf; 02640 PGresult *res; 02641 printQueryOpt myopt = pset.popt; 02642 static const bool translate_columns[] = {false, false, true, false, false, false, false}; 02643 02644 if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign)) 02645 showTables = showViews = showMatViews = showSeq = showForeign = true; 02646 02647 initPQExpBuffer(&buf); 02648 02649 /* 02650 * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here 02651 * for backwards compatibility. 02652 */ 02653 printfPQExpBuffer(&buf, 02654 "SELECT n.nspname as \"%s\",\n" 02655 " c.relname as \"%s\",\n" 02656 " CASE c.relkind" 02657 " WHEN 'r' THEN '%s'" 02658 " WHEN 'v' THEN '%s'" 02659 " WHEN 'm' THEN '%s'" 02660 " WHEN 'i' THEN '%s'" 02661 " WHEN 'S' THEN '%s'" 02662 " WHEN 's' THEN '%s'" 02663 " WHEN 'f' THEN '%s'" 02664 " END as \"%s\",\n" 02665 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", 02666 gettext_noop("Schema"), 02667 gettext_noop("Name"), 02668 gettext_noop("table"), 02669 gettext_noop("view"), 02670 gettext_noop("materialized view"), 02671 gettext_noop("index"), 02672 gettext_noop("sequence"), 02673 gettext_noop("special"), 02674 gettext_noop("foreign table"), 02675 gettext_noop("Type"), 02676 gettext_noop("Owner")); 02677 02678 if (showIndexes) 02679 appendPQExpBuffer(&buf, 02680 ",\n c2.relname as \"%s\"", 02681 gettext_noop("Table")); 02682 02683 if (verbose) 02684 { 02685 /* 02686 * As of PostgreSQL 9.0, use pg_table_size() to show a more acurate 02687 * size of a table, including FSM, VM and TOAST tables. 02688 */ 02689 if (pset.sversion >= 90000) 02690 appendPQExpBuffer(&buf, 02691 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"", 02692 gettext_noop("Size")); 02693 else if (pset.sversion >= 80100) 02694 appendPQExpBuffer(&buf, 02695 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"", 02696 gettext_noop("Size")); 02697 02698 appendPQExpBuffer(&buf, 02699 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", 02700 gettext_noop("Description")); 02701 } 02702 02703 appendPQExpBuffer(&buf, 02704 "\nFROM pg_catalog.pg_class c" 02705 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"); 02706 if (showIndexes) 02707 appendPQExpBuffer(&buf, 02708 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid" 02709 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"); 02710 02711 appendPQExpBuffer(&buf, "\nWHERE c.relkind IN ("); 02712 if (showTables) 02713 appendPQExpBuffer(&buf, "'r',"); 02714 if (showViews) 02715 appendPQExpBuffer(&buf, "'v',"); 02716 if (showMatViews) 02717 appendPQExpBuffer(&buf, "'m',"); 02718 if (showIndexes) 02719 appendPQExpBuffer(&buf, "'i',"); 02720 if (showSeq) 02721 appendPQExpBuffer(&buf, "'S',"); 02722 if (showSystem || pattern) 02723 appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <= 02724 * 8.1 */ 02725 if (showForeign) 02726 appendPQExpBuffer(&buf, "'f',"); 02727 02728 appendPQExpBuffer(&buf, "''"); /* dummy */ 02729 appendPQExpBuffer(&buf, ")\n"); 02730 02731 if (!showSystem && !pattern) 02732 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 02733 " AND n.nspname <> 'information_schema'\n"); 02734 02735 /* 02736 * TOAST objects are suppressed unconditionally. Since we don't provide 02737 * any way to select relkind 't' above, we would never show toast tables 02738 * in any case; it seems a bit confusing to allow their indexes to be 02739 * shown. Use plain \d if you really need to look at a TOAST table/index. 02740 */ 02741 appendPQExpBuffer(&buf, " AND n.nspname !~ '^pg_toast'\n"); 02742 02743 processSQLNamePattern(pset.db, &buf, pattern, true, false, 02744 "n.nspname", "c.relname", NULL, 02745 "pg_catalog.pg_table_is_visible(c.oid)"); 02746 02747 appendPQExpBuffer(&buf, "ORDER BY 1,2;"); 02748 02749 res = PSQLexec(buf.data, false); 02750 termPQExpBuffer(&buf); 02751 if (!res) 02752 return false; 02753 02754 if (PQntuples(res) == 0 && !pset.quiet) 02755 { 02756 if (pattern) 02757 fprintf(pset.queryFout, _("No matching relations found.\n")); 02758 else 02759 fprintf(pset.queryFout, _("No relations found.\n")); 02760 } 02761 else 02762 { 02763 myopt.nullPrint = NULL; 02764 myopt.title = _("List of relations"); 02765 myopt.translate_header = true; 02766 myopt.translate_columns = translate_columns; 02767 02768 printQuery(res, &myopt, pset.queryFout, pset.logfile); 02769 } 02770 02771 PQclear(res); 02772 return true; 02773 } 02774 02775 02776 /* 02777 * \dL 02778 * 02779 * Describes languages. 02780 */ 02781 bool 02782 listLanguages(const char *pattern, bool verbose, bool showSystem) 02783 { 02784 PQExpBufferData buf; 02785 PGresult *res; 02786 printQueryOpt myopt = pset.popt; 02787 02788 initPQExpBuffer(&buf); 02789 02790 printfPQExpBuffer(&buf, 02791 "SELECT l.lanname AS \"%s\",\n", 02792 gettext_noop("Name")); 02793 if (pset.sversion >= 80300) 02794 appendPQExpBuffer(&buf, 02795 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n", 02796 gettext_noop("Owner")); 02797 02798 appendPQExpBuffer(&buf, 02799 " l.lanpltrusted AS \"%s\"", 02800 gettext_noop("Trusted")); 02801 02802 if (verbose) 02803 { 02804 appendPQExpBuffer(&buf, 02805 ",\n NOT l.lanispl AS \"%s\",\n" 02806 " l.lanplcallfoid::regprocedure AS \"%s\",\n" 02807 " l.lanvalidator::regprocedure AS \"%s\",\n ", 02808 gettext_noop("Internal Language"), 02809 gettext_noop("Call Handler"), 02810 gettext_noop("Validator")); 02811 if (pset.sversion >= 90000) 02812 appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n ", 02813 gettext_noop("Inline Handler")); 02814 printACLColumn(&buf, "l.lanacl"); 02815 } 02816 02817 appendPQExpBuffer(&buf, 02818 ",\n d.description AS \"%s\"" 02819 "\nFROM pg_catalog.pg_language l\n" 02820 "LEFT JOIN pg_catalog.pg_description d\n" 02821 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n" 02822 " AND d.objsubid = 0\n", 02823 gettext_noop("Description")); 02824 02825 if (pattern) 02826 processSQLNamePattern(pset.db, &buf, pattern, false, false, 02827 NULL, "l.lanname", NULL, NULL); 02828 02829 if (!showSystem && !pattern) 02830 appendPQExpBuffer(&buf, "WHERE l.lanplcallfoid != 0\n"); 02831 02832 02833 appendPQExpBuffer(&buf, "ORDER BY 1;"); 02834 02835 res = PSQLexec(buf.data, false); 02836 termPQExpBuffer(&buf); 02837 if (!res) 02838 return false; 02839 02840 myopt.nullPrint = NULL; 02841 myopt.title = _("List of languages"); 02842 myopt.translate_header = true; 02843 02844 printQuery(res, &myopt, pset.queryFout, pset.logfile); 02845 02846 PQclear(res); 02847 return true; 02848 } 02849 02850 02851 /* 02852 * \dD 02853 * 02854 * Describes domains. 02855 */ 02856 bool 02857 listDomains(const char *pattern, bool verbose, bool showSystem) 02858 { 02859 PQExpBufferData buf; 02860 PGresult *res; 02861 printQueryOpt myopt = pset.popt; 02862 02863 initPQExpBuffer(&buf); 02864 02865 printfPQExpBuffer(&buf, 02866 "SELECT n.nspname as \"%s\",\n" 02867 " t.typname as \"%s\",\n" 02868 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n" 02869 " TRIM(LEADING\n", 02870 gettext_noop("Schema"), 02871 gettext_noop("Name"), 02872 gettext_noop("Type")); 02873 02874 if (pset.sversion >= 90100) 02875 appendPQExpBuffer(&buf, 02876 " COALESCE((SELECT ' collate ' || c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n" 02877 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation), '') ||\n"); 02878 appendPQExpBuffer(&buf, 02879 " CASE WHEN t.typnotnull THEN ' not null' ELSE '' END ||\n" 02880 " CASE WHEN t.typdefault IS NOT NULL THEN ' default ' || t.typdefault ELSE '' END\n" 02881 " ) as \"%s\",\n" 02882 " pg_catalog.array_to_string(ARRAY(\n" 02883 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n" 02884 " ), ' ') as \"%s\"", 02885 gettext_noop("Modifier"), 02886 gettext_noop("Check")); 02887 02888 if (verbose) 02889 { 02890 if (pset.sversion >= 90200) 02891 { 02892 appendPQExpBuffer(&buf, ",\n "); 02893 printACLColumn(&buf, "t.typacl"); 02894 } 02895 appendPQExpBuffer(&buf, 02896 ",\n d.description as \"%s\"", 02897 gettext_noop("Description")); 02898 } 02899 02900 appendPQExpBuffer(&buf, 02901 "\nFROM pg_catalog.pg_type t\n" 02902 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"); 02903 02904 if (verbose) 02905 appendPQExpBuffer(&buf, 02906 " LEFT JOIN pg_catalog.pg_description d " 02907 "ON d.classoid = t.tableoid AND d.objoid = t.oid " 02908 "AND d.objsubid = 0\n"); 02909 02910 appendPQExpBuffer(&buf, "WHERE t.typtype = 'd'\n"); 02911 02912 if (!showSystem && !pattern) 02913 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 02914 " AND n.nspname <> 'information_schema'\n"); 02915 02916 processSQLNamePattern(pset.db, &buf, pattern, true, false, 02917 "n.nspname", "t.typname", NULL, 02918 "pg_catalog.pg_type_is_visible(t.oid)"); 02919 02920 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 02921 02922 res = PSQLexec(buf.data, false); 02923 termPQExpBuffer(&buf); 02924 if (!res) 02925 return false; 02926 02927 myopt.nullPrint = NULL; 02928 myopt.title = _("List of domains"); 02929 myopt.translate_header = true; 02930 02931 printQuery(res, &myopt, pset.queryFout, pset.logfile); 02932 02933 PQclear(res); 02934 return true; 02935 } 02936 02937 /* 02938 * \dc 02939 * 02940 * Describes conversions. 02941 */ 02942 bool 02943 listConversions(const char *pattern, bool verbose, bool showSystem) 02944 { 02945 PQExpBufferData buf; 02946 PGresult *res; 02947 printQueryOpt myopt = pset.popt; 02948 static const bool translate_columns[] = {false, false, false, false, true}; 02949 02950 initPQExpBuffer(&buf); 02951 02952 printfPQExpBuffer(&buf, 02953 "SELECT n.nspname AS \"%s\",\n" 02954 " c.conname AS \"%s\",\n" 02955 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n" 02956 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n" 02957 " CASE WHEN c.condefault THEN '%s'\n" 02958 " ELSE '%s' END AS \"%s\"", 02959 gettext_noop("Schema"), 02960 gettext_noop("Name"), 02961 gettext_noop("Source"), 02962 gettext_noop("Destination"), 02963 gettext_noop("yes"), gettext_noop("no"), 02964 gettext_noop("Default?")); 02965 02966 if (verbose) 02967 appendPQExpBuffer(&buf, 02968 ",\n d.description AS \"%s\"", 02969 gettext_noop("Description")); 02970 02971 appendPQExpBuffer(&buf, 02972 "\nFROM pg_catalog.pg_conversion c\n" 02973 " JOIN pg_catalog.pg_namespace n " 02974 "ON n.oid = c.connamespace\n"); 02975 02976 if (verbose) 02977 appendPQExpBuffer(&buf, 02978 "LEFT JOIN pg_catalog.pg_description d " 02979 "ON d.classoid = c.tableoid\n" 02980 " AND d.objoid = c.oid " 02981 "AND d.objsubid = 0\n"); 02982 02983 appendPQExpBuffer(&buf, "WHERE true\n"); 02984 02985 if (!showSystem && !pattern) 02986 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 02987 " AND n.nspname <> 'information_schema'\n"); 02988 02989 processSQLNamePattern(pset.db, &buf, pattern, true, false, 02990 "n.nspname", "c.conname", NULL, 02991 "pg_catalog.pg_conversion_is_visible(c.oid)"); 02992 02993 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 02994 02995 res = PSQLexec(buf.data, false); 02996 termPQExpBuffer(&buf); 02997 if (!res) 02998 return false; 02999 03000 myopt.nullPrint = NULL; 03001 myopt.title = _("List of conversions"); 03002 myopt.translate_header = true; 03003 myopt.translate_columns = translate_columns; 03004 03005 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03006 03007 PQclear(res); 03008 return true; 03009 } 03010 03011 /* 03012 * \dy 03013 * 03014 * Describes Event Triggers. 03015 */ 03016 bool 03017 listEventTriggers(const char *pattern, bool verbose) 03018 { 03019 PQExpBufferData buf; 03020 PGresult *res; 03021 printQueryOpt myopt = pset.popt; 03022 static const bool translate_columns[] = 03023 {false, false, false, true, false, false, false}; 03024 03025 initPQExpBuffer(&buf); 03026 03027 printfPQExpBuffer(&buf, 03028 "select evtname as \"%s\", " 03029 "evtevent as \"%s\", " 03030 "pg_catalog.pg_get_userbyid(e.evtowner) AS \"%s\", " 03031 "case evtenabled when 'O' then 'enabled' " 03032 " when 'R' then 'replica' " 03033 " when 'A' then 'always' " 03034 " when 'D' then 'disabled' end as \"%s\", " 03035 "e.evtfoid::regproc as \"%s\", " 03036 "array_to_string(array(select x " 03037 " from unnest(evttags) as t(x)), ', ') as \"%s\" ", 03038 gettext_noop("Name"), 03039 gettext_noop("Event"), 03040 gettext_noop("Owner"), 03041 gettext_noop("Enabled"), 03042 gettext_noop("Procedure"), 03043 gettext_noop("Tags")); 03044 if (verbose) 03045 appendPQExpBuffer(&buf, 03046 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"", 03047 gettext_noop("Description")); 03048 appendPQExpBuffer(&buf, 03049 "\nFROM pg_event_trigger e "); 03050 03051 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03052 NULL, "evtname", NULL, NULL); 03053 03054 appendPQExpBuffer(&buf, "ORDER BY 1"); 03055 03056 res = PSQLexec(buf.data, false); 03057 termPQExpBuffer(&buf); 03058 if (!res) 03059 return false; 03060 03061 myopt.nullPrint = NULL; 03062 myopt.title = _("List of event triggers"); 03063 myopt.translate_header = true; 03064 myopt.translate_columns = translate_columns; 03065 03066 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03067 03068 PQclear(res); 03069 return true; 03070 } 03071 03072 /* 03073 * \dC 03074 * 03075 * Describes casts. 03076 */ 03077 bool 03078 listCasts(const char *pattern, bool verbose) 03079 { 03080 PQExpBufferData buf; 03081 PGresult *res; 03082 printQueryOpt myopt = pset.popt; 03083 static const bool translate_columns[] = {false, false, false, true}; 03084 03085 initPQExpBuffer(&buf); 03086 03087 /* 03088 * We need a left join to pg_proc for binary casts; the others are just 03089 * paranoia. Also note that we don't attempt to localize '(binary 03090 * coercible)', because there's too much risk of gettext translating a 03091 * function name that happens to match some string in the PO database. 03092 */ 03093 printfPQExpBuffer(&buf, 03094 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n" 03095 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n" 03096 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n" 03097 " ELSE p.proname\n" 03098 " END as \"%s\",\n" 03099 " CASE WHEN c.castcontext = 'e' THEN '%s'\n" 03100 " WHEN c.castcontext = 'a' THEN '%s'\n" 03101 " ELSE '%s'\n" 03102 " END as \"%s\"", 03103 gettext_noop("Source type"), 03104 gettext_noop("Target type"), 03105 gettext_noop("Function"), 03106 gettext_noop("no"), 03107 gettext_noop("in assignment"), 03108 gettext_noop("yes"), 03109 gettext_noop("Implicit?")); 03110 03111 if (verbose) 03112 appendPQExpBuffer(&buf, 03113 ",\n d.description AS \"%s\"\n", 03114 gettext_noop("Description")); 03115 03116 appendPQExpBuffer(&buf, 03117 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n" 03118 " ON c.castfunc = p.oid\n" 03119 " LEFT JOIN pg_catalog.pg_type ts\n" 03120 " ON c.castsource = ts.oid\n" 03121 " LEFT JOIN pg_catalog.pg_namespace ns\n" 03122 " ON ns.oid = ts.typnamespace\n" 03123 " LEFT JOIN pg_catalog.pg_type tt\n" 03124 " ON c.casttarget = tt.oid\n" 03125 " LEFT JOIN pg_catalog.pg_namespace nt\n" 03126 " ON nt.oid = tt.typnamespace\n"); 03127 03128 if (verbose) 03129 appendPQExpBuffer(&buf, 03130 " LEFT JOIN pg_catalog.pg_description d\n" 03131 " ON d.classoid = c.tableoid AND d.objoid = " 03132 "c.oid AND d.objsubid = 0\n"); 03133 03134 appendPQExpBuffer(&buf, "WHERE ( (true"); 03135 03136 /* 03137 * Match name pattern against either internal or external name of either 03138 * castsource or casttarget 03139 */ 03140 processSQLNamePattern(pset.db, &buf, pattern, true, false, 03141 "ns.nspname", "ts.typname", 03142 "pg_catalog.format_type(ts.oid, NULL)", 03143 "pg_catalog.pg_type_is_visible(ts.oid)"); 03144 03145 appendPQExpBuffer(&buf, ") OR (true"); 03146 03147 processSQLNamePattern(pset.db, &buf, pattern, true, false, 03148 "nt.nspname", "tt.typname", 03149 "pg_catalog.format_type(tt.oid, NULL)", 03150 "pg_catalog.pg_type_is_visible(tt.oid)"); 03151 03152 appendPQExpBuffer(&buf, ") )\nORDER BY 1, 2;"); 03153 03154 res = PSQLexec(buf.data, false); 03155 termPQExpBuffer(&buf); 03156 if (!res) 03157 return false; 03158 03159 myopt.nullPrint = NULL; 03160 myopt.title = _("List of casts"); 03161 myopt.translate_header = true; 03162 myopt.translate_columns = translate_columns; 03163 03164 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03165 03166 PQclear(res); 03167 return true; 03168 } 03169 03170 /* 03171 * \dO 03172 * 03173 * Describes collations. 03174 */ 03175 bool 03176 listCollations(const char *pattern, bool verbose, bool showSystem) 03177 { 03178 PQExpBufferData buf; 03179 PGresult *res; 03180 printQueryOpt myopt = pset.popt; 03181 static const bool translate_columns[] = {false, false, false, false, false}; 03182 03183 if (pset.sversion < 90100) 03184 { 03185 psql_error("The server (version %d.%d) does not support collations.\n", 03186 pset.sversion / 10000, (pset.sversion / 100) % 100); 03187 return true; 03188 } 03189 03190 initPQExpBuffer(&buf); 03191 03192 printfPQExpBuffer(&buf, 03193 "SELECT n.nspname AS \"%s\",\n" 03194 " c.collname AS \"%s\",\n" 03195 " c.collcollate AS \"%s\",\n" 03196 " c.collctype AS \"%s\"", 03197 gettext_noop("Schema"), 03198 gettext_noop("Name"), 03199 gettext_noop("Collate"), 03200 gettext_noop("Ctype")); 03201 03202 if (verbose) 03203 appendPQExpBuffer(&buf, 03204 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"", 03205 gettext_noop("Description")); 03206 03207 appendPQExpBuffer(&buf, 03208 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n" 03209 "WHERE n.oid = c.collnamespace\n"); 03210 03211 if (!showSystem && !pattern) 03212 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" 03213 " AND n.nspname <> 'information_schema'\n"); 03214 03215 /* 03216 * Hide collations that aren't usable in the current database's encoding. 03217 * If you think to change this, note that pg_collation_is_visible rejects 03218 * unusable collations, so you will need to hack name pattern processing 03219 * somehow to avoid inconsistent behavior. 03220 */ 03221 appendPQExpBuffer(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n"); 03222 03223 processSQLNamePattern(pset.db, &buf, pattern, true, false, 03224 "n.nspname", "c.collname", NULL, 03225 "pg_catalog.pg_collation_is_visible(c.oid)"); 03226 03227 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03228 03229 res = PSQLexec(buf.data, false); 03230 termPQExpBuffer(&buf); 03231 if (!res) 03232 return false; 03233 03234 myopt.nullPrint = NULL; 03235 myopt.title = _("List of collations"); 03236 myopt.translate_header = true; 03237 myopt.translate_columns = translate_columns; 03238 03239 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03240 03241 PQclear(res); 03242 return true; 03243 } 03244 03245 /* 03246 * \dn 03247 * 03248 * Describes schemas (namespaces) 03249 */ 03250 bool 03251 listSchemas(const char *pattern, bool verbose, bool showSystem) 03252 { 03253 PQExpBufferData buf; 03254 PGresult *res; 03255 printQueryOpt myopt = pset.popt; 03256 03257 initPQExpBuffer(&buf); 03258 printfPQExpBuffer(&buf, 03259 "SELECT n.nspname AS \"%s\",\n" 03260 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"", 03261 gettext_noop("Name"), 03262 gettext_noop("Owner")); 03263 03264 if (verbose) 03265 { 03266 appendPQExpBuffer(&buf, ",\n "); 03267 printACLColumn(&buf, "n.nspacl"); 03268 appendPQExpBuffer(&buf, 03269 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", 03270 gettext_noop("Description")); 03271 } 03272 03273 appendPQExpBuffer(&buf, 03274 "\nFROM pg_catalog.pg_namespace n\n"); 03275 03276 if (!showSystem && !pattern) 03277 appendPQExpBuffer(&buf, 03278 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n"); 03279 03280 processSQLNamePattern(pset.db, &buf, pattern, 03281 !showSystem && !pattern, false, 03282 NULL, "n.nspname", NULL, 03283 NULL); 03284 03285 appendPQExpBuffer(&buf, "ORDER BY 1;"); 03286 03287 res = PSQLexec(buf.data, false); 03288 termPQExpBuffer(&buf); 03289 if (!res) 03290 return false; 03291 03292 myopt.nullPrint = NULL; 03293 myopt.title = _("List of schemas"); 03294 myopt.translate_header = true; 03295 03296 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03297 03298 PQclear(res); 03299 return true; 03300 } 03301 03302 03303 /* 03304 * \dFp 03305 * list text search parsers 03306 */ 03307 bool 03308 listTSParsers(const char *pattern, bool verbose) 03309 { 03310 PQExpBufferData buf; 03311 PGresult *res; 03312 printQueryOpt myopt = pset.popt; 03313 03314 if (pset.sversion < 80300) 03315 { 03316 psql_error("The server (version %d.%d) does not support full text search.\n", 03317 pset.sversion / 10000, (pset.sversion / 100) % 100); 03318 return true; 03319 } 03320 03321 if (verbose) 03322 return listTSParsersVerbose(pattern); 03323 03324 initPQExpBuffer(&buf); 03325 03326 printfPQExpBuffer(&buf, 03327 "SELECT \n" 03328 " n.nspname as \"%s\",\n" 03329 " p.prsname as \"%s\",\n" 03330 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n" 03331 "FROM pg_catalog.pg_ts_parser p \n" 03332 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n", 03333 gettext_noop("Schema"), 03334 gettext_noop("Name"), 03335 gettext_noop("Description") 03336 ); 03337 03338 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03339 "n.nspname", "p.prsname", NULL, 03340 "pg_catalog.pg_ts_parser_is_visible(p.oid)"); 03341 03342 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03343 03344 res = PSQLexec(buf.data, false); 03345 termPQExpBuffer(&buf); 03346 if (!res) 03347 return false; 03348 03349 myopt.nullPrint = NULL; 03350 myopt.title = _("List of text search parsers"); 03351 myopt.translate_header = true; 03352 03353 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03354 03355 PQclear(res); 03356 return true; 03357 } 03358 03359 /* 03360 * full description of parsers 03361 */ 03362 static bool 03363 listTSParsersVerbose(const char *pattern) 03364 { 03365 PQExpBufferData buf; 03366 PGresult *res; 03367 int i; 03368 03369 initPQExpBuffer(&buf); 03370 03371 printfPQExpBuffer(&buf, 03372 "SELECT p.oid, \n" 03373 " n.nspname, \n" 03374 " p.prsname \n" 03375 "FROM pg_catalog.pg_ts_parser p\n" 03376 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n" 03377 ); 03378 03379 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03380 "n.nspname", "p.prsname", NULL, 03381 "pg_catalog.pg_ts_parser_is_visible(p.oid)"); 03382 03383 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03384 03385 res = PSQLexec(buf.data, false); 03386 termPQExpBuffer(&buf); 03387 if (!res) 03388 return false; 03389 03390 if (PQntuples(res) == 0) 03391 { 03392 if (!pset.quiet) 03393 psql_error("Did not find any text search parser named \"%s\".\n", 03394 pattern); 03395 PQclear(res); 03396 return false; 03397 } 03398 03399 for (i = 0; i < PQntuples(res); i++) 03400 { 03401 const char *oid; 03402 const char *nspname = NULL; 03403 const char *prsname; 03404 03405 oid = PQgetvalue(res, i, 0); 03406 if (!PQgetisnull(res, i, 1)) 03407 nspname = PQgetvalue(res, i, 1); 03408 prsname = PQgetvalue(res, i, 2); 03409 03410 if (!describeOneTSParser(oid, nspname, prsname)) 03411 { 03412 PQclear(res); 03413 return false; 03414 } 03415 03416 if (cancel_pressed) 03417 { 03418 PQclear(res); 03419 return false; 03420 } 03421 } 03422 03423 PQclear(res); 03424 return true; 03425 } 03426 03427 static bool 03428 describeOneTSParser(const char *oid, const char *nspname, const char *prsname) 03429 { 03430 PQExpBufferData buf; 03431 PGresult *res; 03432 char title[1024]; 03433 printQueryOpt myopt = pset.popt; 03434 static const bool translate_columns[] = {true, false, false}; 03435 03436 initPQExpBuffer(&buf); 03437 03438 printfPQExpBuffer(&buf, 03439 "SELECT '%s' AS \"%s\", \n" 03440 " p.prsstart::pg_catalog.regproc AS \"%s\", \n" 03441 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n" 03442 " FROM pg_catalog.pg_ts_parser p \n" 03443 " WHERE p.oid = '%s' \n" 03444 "UNION ALL \n" 03445 "SELECT '%s', \n" 03446 " p.prstoken::pg_catalog.regproc, \n" 03447 " pg_catalog.obj_description(p.prstoken, 'pg_proc') \n" 03448 " FROM pg_catalog.pg_ts_parser p \n" 03449 " WHERE p.oid = '%s' \n" 03450 "UNION ALL \n" 03451 "SELECT '%s', \n" 03452 " p.prsend::pg_catalog.regproc, \n" 03453 " pg_catalog.obj_description(p.prsend, 'pg_proc') \n" 03454 " FROM pg_catalog.pg_ts_parser p \n" 03455 " WHERE p.oid = '%s' \n" 03456 "UNION ALL \n" 03457 "SELECT '%s', \n" 03458 " p.prsheadline::pg_catalog.regproc, \n" 03459 " pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n" 03460 " FROM pg_catalog.pg_ts_parser p \n" 03461 " WHERE p.oid = '%s' \n" 03462 "UNION ALL \n" 03463 "SELECT '%s', \n" 03464 " p.prslextype::pg_catalog.regproc, \n" 03465 " pg_catalog.obj_description(p.prslextype, 'pg_proc') \n" 03466 " FROM pg_catalog.pg_ts_parser p \n" 03467 " WHERE p.oid = '%s';", 03468 gettext_noop("Start parse"), 03469 gettext_noop("Method"), 03470 gettext_noop("Function"), 03471 gettext_noop("Description"), 03472 oid, 03473 gettext_noop("Get next token"), 03474 oid, 03475 gettext_noop("End parse"), 03476 oid, 03477 gettext_noop("Get headline"), 03478 oid, 03479 gettext_noop("Get token types"), 03480 oid); 03481 03482 res = PSQLexec(buf.data, false); 03483 termPQExpBuffer(&buf); 03484 if (!res) 03485 return false; 03486 03487 myopt.nullPrint = NULL; 03488 if (nspname) 03489 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname); 03490 else 03491 sprintf(title, _("Text search parser \"%s\""), prsname); 03492 myopt.title = title; 03493 myopt.footers = NULL; 03494 myopt.topt.default_footer = false; 03495 myopt.translate_header = true; 03496 myopt.translate_columns = translate_columns; 03497 03498 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03499 03500 PQclear(res); 03501 03502 initPQExpBuffer(&buf); 03503 03504 printfPQExpBuffer(&buf, 03505 "SELECT t.alias as \"%s\", \n" 03506 " t.description as \"%s\" \n" 03507 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n" 03508 "ORDER BY 1;", 03509 gettext_noop("Token name"), 03510 gettext_noop("Description"), 03511 oid); 03512 03513 res = PSQLexec(buf.data, false); 03514 termPQExpBuffer(&buf); 03515 if (!res) 03516 return false; 03517 03518 myopt.nullPrint = NULL; 03519 if (nspname) 03520 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname); 03521 else 03522 sprintf(title, _("Token types for parser \"%s\""), prsname); 03523 myopt.title = title; 03524 myopt.footers = NULL; 03525 myopt.topt.default_footer = true; 03526 myopt.translate_header = true; 03527 myopt.translate_columns = NULL; 03528 03529 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03530 03531 PQclear(res); 03532 return true; 03533 } 03534 03535 03536 /* 03537 * \dFd 03538 * list text search dictionaries 03539 */ 03540 bool 03541 listTSDictionaries(const char *pattern, bool verbose) 03542 { 03543 PQExpBufferData buf; 03544 PGresult *res; 03545 printQueryOpt myopt = pset.popt; 03546 03547 if (pset.sversion < 80300) 03548 { 03549 psql_error("The server (version %d.%d) does not support full text search.\n", 03550 pset.sversion / 10000, (pset.sversion / 100) % 100); 03551 return true; 03552 } 03553 03554 initPQExpBuffer(&buf); 03555 03556 printfPQExpBuffer(&buf, 03557 "SELECT \n" 03558 " n.nspname as \"%s\",\n" 03559 " d.dictname as \"%s\",\n", 03560 gettext_noop("Schema"), 03561 gettext_noop("Name")); 03562 03563 if (verbose) 03564 { 03565 appendPQExpBuffer(&buf, 03566 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n" 03567 " pg_catalog.pg_ts_template t \n" 03568 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n" 03569 " WHERE d.dicttemplate = t.oid ) AS \"%s\", \n" 03570 " d.dictinitoption as \"%s\", \n", 03571 gettext_noop("Template"), 03572 gettext_noop("Init options")); 03573 } 03574 03575 appendPQExpBuffer(&buf, 03576 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n", 03577 gettext_noop("Description")); 03578 03579 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_dict d\n" 03580 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n"); 03581 03582 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03583 "n.nspname", "d.dictname", NULL, 03584 "pg_catalog.pg_ts_dict_is_visible(d.oid)"); 03585 03586 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03587 03588 res = PSQLexec(buf.data, false); 03589 termPQExpBuffer(&buf); 03590 if (!res) 03591 return false; 03592 03593 myopt.nullPrint = NULL; 03594 myopt.title = _("List of text search dictionaries"); 03595 myopt.translate_header = true; 03596 03597 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03598 03599 PQclear(res); 03600 return true; 03601 } 03602 03603 03604 /* 03605 * \dFt 03606 * list text search templates 03607 */ 03608 bool 03609 listTSTemplates(const char *pattern, bool verbose) 03610 { 03611 PQExpBufferData buf; 03612 PGresult *res; 03613 printQueryOpt myopt = pset.popt; 03614 03615 if (pset.sversion < 80300) 03616 { 03617 psql_error("The server (version %d.%d) does not support full text search.\n", 03618 pset.sversion / 10000, (pset.sversion / 100) % 100); 03619 return true; 03620 } 03621 03622 initPQExpBuffer(&buf); 03623 03624 if (verbose) 03625 printfPQExpBuffer(&buf, 03626 "SELECT \n" 03627 " n.nspname AS \"%s\",\n" 03628 " t.tmplname AS \"%s\",\n" 03629 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n" 03630 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n" 03631 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n", 03632 gettext_noop("Schema"), 03633 gettext_noop("Name"), 03634 gettext_noop("Init"), 03635 gettext_noop("Lexize"), 03636 gettext_noop("Description")); 03637 else 03638 printfPQExpBuffer(&buf, 03639 "SELECT \n" 03640 " n.nspname AS \"%s\",\n" 03641 " t.tmplname AS \"%s\",\n" 03642 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n", 03643 gettext_noop("Schema"), 03644 gettext_noop("Name"), 03645 gettext_noop("Description")); 03646 03647 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_template t\n" 03648 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n"); 03649 03650 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03651 "n.nspname", "t.tmplname", NULL, 03652 "pg_catalog.pg_ts_template_is_visible(t.oid)"); 03653 03654 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03655 03656 res = PSQLexec(buf.data, false); 03657 termPQExpBuffer(&buf); 03658 if (!res) 03659 return false; 03660 03661 myopt.nullPrint = NULL; 03662 myopt.title = _("List of text search templates"); 03663 myopt.translate_header = true; 03664 03665 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03666 03667 PQclear(res); 03668 return true; 03669 } 03670 03671 03672 /* 03673 * \dF 03674 * list text search configurations 03675 */ 03676 bool 03677 listTSConfigs(const char *pattern, bool verbose) 03678 { 03679 PQExpBufferData buf; 03680 PGresult *res; 03681 printQueryOpt myopt = pset.popt; 03682 03683 if (pset.sversion < 80300) 03684 { 03685 psql_error("The server (version %d.%d) does not support full text search.\n", 03686 pset.sversion / 10000, (pset.sversion / 100) % 100); 03687 return true; 03688 } 03689 03690 if (verbose) 03691 return listTSConfigsVerbose(pattern); 03692 03693 initPQExpBuffer(&buf); 03694 03695 printfPQExpBuffer(&buf, 03696 "SELECT \n" 03697 " n.nspname as \"%s\",\n" 03698 " c.cfgname as \"%s\",\n" 03699 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n" 03700 "FROM pg_catalog.pg_ts_config c\n" 03701 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n", 03702 gettext_noop("Schema"), 03703 gettext_noop("Name"), 03704 gettext_noop("Description") 03705 ); 03706 03707 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03708 "n.nspname", "c.cfgname", NULL, 03709 "pg_catalog.pg_ts_config_is_visible(c.oid)"); 03710 03711 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 03712 03713 res = PSQLexec(buf.data, false); 03714 termPQExpBuffer(&buf); 03715 if (!res) 03716 return false; 03717 03718 myopt.nullPrint = NULL; 03719 myopt.title = _("List of text search configurations"); 03720 myopt.translate_header = true; 03721 03722 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03723 03724 PQclear(res); 03725 return true; 03726 } 03727 03728 static bool 03729 listTSConfigsVerbose(const char *pattern) 03730 { 03731 PQExpBufferData buf; 03732 PGresult *res; 03733 int i; 03734 03735 initPQExpBuffer(&buf); 03736 03737 printfPQExpBuffer(&buf, 03738 "SELECT c.oid, c.cfgname,\n" 03739 " n.nspname, \n" 03740 " p.prsname, \n" 03741 " np.nspname as pnspname \n" 03742 "FROM pg_catalog.pg_ts_config c \n" 03743 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n" 03744 " pg_catalog.pg_ts_parser p \n" 03745 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n" 03746 "WHERE p.oid = c.cfgparser\n" 03747 ); 03748 03749 processSQLNamePattern(pset.db, &buf, pattern, true, false, 03750 "n.nspname", "c.cfgname", NULL, 03751 "pg_catalog.pg_ts_config_is_visible(c.oid)"); 03752 03753 appendPQExpBuffer(&buf, "ORDER BY 3, 2;"); 03754 03755 res = PSQLexec(buf.data, false); 03756 termPQExpBuffer(&buf); 03757 if (!res) 03758 return false; 03759 03760 if (PQntuples(res) == 0) 03761 { 03762 if (!pset.quiet) 03763 psql_error("Did not find any text search configuration named \"%s\".\n", 03764 pattern); 03765 PQclear(res); 03766 return false; 03767 } 03768 03769 for (i = 0; i < PQntuples(res); i++) 03770 { 03771 const char *oid; 03772 const char *cfgname; 03773 const char *nspname = NULL; 03774 const char *prsname; 03775 const char *pnspname = NULL; 03776 03777 oid = PQgetvalue(res, i, 0); 03778 cfgname = PQgetvalue(res, i, 1); 03779 if (!PQgetisnull(res, i, 2)) 03780 nspname = PQgetvalue(res, i, 2); 03781 prsname = PQgetvalue(res, i, 3); 03782 if (!PQgetisnull(res, i, 4)) 03783 pnspname = PQgetvalue(res, i, 4); 03784 03785 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname)) 03786 { 03787 PQclear(res); 03788 return false; 03789 } 03790 03791 if (cancel_pressed) 03792 { 03793 PQclear(res); 03794 return false; 03795 } 03796 } 03797 03798 PQclear(res); 03799 return true; 03800 } 03801 03802 static bool 03803 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname, 03804 const char *pnspname, const char *prsname) 03805 { 03806 PQExpBufferData buf, 03807 title; 03808 PGresult *res; 03809 printQueryOpt myopt = pset.popt; 03810 03811 initPQExpBuffer(&buf); 03812 03813 printfPQExpBuffer(&buf, 03814 "SELECT \n" 03815 " ( SELECT t.alias FROM \n" 03816 " pg_catalog.ts_token_type(c.cfgparser) AS t \n" 03817 " WHERE t.tokid = m.maptokentype ) AS \"%s\", \n" 03818 " pg_catalog.btrim( \n" 03819 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n" 03820 " FROM pg_catalog.pg_ts_config_map AS mm \n" 03821 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n" 03822 " ORDER BY mapcfg, maptokentype, mapseqno \n" 03823 " ) :: pg_catalog.text , \n" 03824 " '{}') AS \"%s\" \n" 03825 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n" 03826 "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n" 03827 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n" 03828 "ORDER BY 1;", 03829 gettext_noop("Token"), 03830 gettext_noop("Dictionaries"), 03831 oid); 03832 03833 res = PSQLexec(buf.data, false); 03834 termPQExpBuffer(&buf); 03835 if (!res) 03836 return false; 03837 03838 initPQExpBuffer(&title); 03839 03840 if (nspname) 03841 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""), 03842 nspname, cfgname); 03843 else 03844 appendPQExpBuffer(&title, _("Text search configuration \"%s\""), 03845 cfgname); 03846 03847 if (pnspname) 03848 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""), 03849 pnspname, prsname); 03850 else 03851 appendPQExpBuffer(&title, _("\nParser: \"%s\""), 03852 prsname); 03853 03854 myopt.nullPrint = NULL; 03855 myopt.title = title.data; 03856 myopt.footers = NULL; 03857 myopt.topt.default_footer = false; 03858 myopt.translate_header = true; 03859 03860 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03861 03862 termPQExpBuffer(&title); 03863 03864 PQclear(res); 03865 return true; 03866 } 03867 03868 03869 /* 03870 * \dew 03871 * 03872 * Describes foreign-data wrappers 03873 */ 03874 bool 03875 listForeignDataWrappers(const char *pattern, bool verbose) 03876 { 03877 PQExpBufferData buf; 03878 PGresult *res; 03879 printQueryOpt myopt = pset.popt; 03880 03881 if (pset.sversion < 80400) 03882 { 03883 psql_error("The server (version %d.%d) does not support foreign-data wrappers.\n", 03884 pset.sversion / 10000, (pset.sversion / 100) % 100); 03885 return true; 03886 } 03887 03888 initPQExpBuffer(&buf); 03889 printfPQExpBuffer(&buf, 03890 "SELECT fdw.fdwname AS \"%s\",\n" 03891 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n", 03892 gettext_noop("Name"), 03893 gettext_noop("Owner")); 03894 if (pset.sversion >= 90100) 03895 appendPQExpBuffer(&buf, 03896 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n", 03897 gettext_noop("Handler")); 03898 appendPQExpBuffer(&buf, 03899 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"", 03900 gettext_noop("Validator")); 03901 03902 if (verbose) 03903 { 03904 appendPQExpBuffer(&buf, ",\n "); 03905 printACLColumn(&buf, "fdwacl"); 03906 appendPQExpBuffer(&buf, 03907 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE " 03908 " '(' || array_to_string(ARRAY(SELECT " 03909 " quote_ident(option_name) || ' ' || " 03910 " quote_literal(option_value) FROM " 03911 " pg_options_to_table(fdwoptions)), ', ') || ')' " 03912 " END AS \"%s\"", 03913 gettext_noop("FDW Options")); 03914 03915 if (pset.sversion >= 90100) 03916 appendPQExpBuffer(&buf, 03917 ",\n d.description AS \"%s\" ", 03918 gettext_noop("Description")); 03919 } 03920 03921 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n"); 03922 03923 if (verbose && pset.sversion >= 90100) 03924 appendPQExpBuffer(&buf, 03925 "LEFT JOIN pg_catalog.pg_description d\n" 03926 " ON d.classoid = fdw.tableoid " 03927 "AND d.objoid = fdw.oid AND d.objsubid = 0\n"); 03928 03929 processSQLNamePattern(pset.db, &buf, pattern, false, false, 03930 NULL, "fdwname", NULL, NULL); 03931 03932 appendPQExpBuffer(&buf, "ORDER BY 1;"); 03933 03934 res = PSQLexec(buf.data, false); 03935 termPQExpBuffer(&buf); 03936 if (!res) 03937 return false; 03938 03939 myopt.nullPrint = NULL; 03940 myopt.title = _("List of foreign-data wrappers"); 03941 myopt.translate_header = true; 03942 03943 printQuery(res, &myopt, pset.queryFout, pset.logfile); 03944 03945 PQclear(res); 03946 return true; 03947 } 03948 03949 /* 03950 * \des 03951 * 03952 * Describes foreign servers. 03953 */ 03954 bool 03955 listForeignServers(const char *pattern, bool verbose) 03956 { 03957 PQExpBufferData buf; 03958 PGresult *res; 03959 printQueryOpt myopt = pset.popt; 03960 03961 if (pset.sversion < 80400) 03962 { 03963 psql_error("The server (version %d.%d) does not support foreign servers.\n", 03964 pset.sversion / 10000, (pset.sversion / 100) % 100); 03965 return true; 03966 } 03967 03968 initPQExpBuffer(&buf); 03969 printfPQExpBuffer(&buf, 03970 "SELECT s.srvname AS \"%s\",\n" 03971 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n" 03972 " f.fdwname AS \"%s\"", 03973 gettext_noop("Name"), 03974 gettext_noop("Owner"), 03975 gettext_noop("Foreign-data wrapper")); 03976 03977 if (verbose) 03978 { 03979 appendPQExpBuffer(&buf, ",\n "); 03980 printACLColumn(&buf, "s.srvacl"); 03981 appendPQExpBuffer(&buf, 03982 ",\n" 03983 " s.srvtype AS \"%s\",\n" 03984 " s.srvversion AS \"%s\",\n" 03985 " CASE WHEN srvoptions IS NULL THEN '' ELSE " 03986 " '(' || array_to_string(ARRAY(SELECT " 03987 " quote_ident(option_name) || ' ' || " 03988 " quote_literal(option_value) FROM " 03989 " pg_options_to_table(srvoptions)), ', ') || ')' " 03990 " END AS \"%s\",\n" 03991 " d.description AS \"%s\"", 03992 gettext_noop("Type"), 03993 gettext_noop("Version"), 03994 gettext_noop("FDW Options"), 03995 gettext_noop("Description")); 03996 } 03997 03998 appendPQExpBuffer(&buf, 03999 "\nFROM pg_catalog.pg_foreign_server s\n" 04000 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n"); 04001 04002 if (verbose) 04003 appendPQExpBuffer(&buf, 04004 "LEFT JOIN pg_description d\n " 04005 "ON d.classoid = s.tableoid AND d.objoid = s.oid " 04006 "AND d.objsubid = 0\n"); 04007 04008 processSQLNamePattern(pset.db, &buf, pattern, false, false, 04009 NULL, "s.srvname", NULL, NULL); 04010 04011 appendPQExpBuffer(&buf, "ORDER BY 1;"); 04012 04013 res = PSQLexec(buf.data, false); 04014 termPQExpBuffer(&buf); 04015 if (!res) 04016 return false; 04017 04018 myopt.nullPrint = NULL; 04019 myopt.title = _("List of foreign servers"); 04020 myopt.translate_header = true; 04021 04022 printQuery(res, &myopt, pset.queryFout, pset.logfile); 04023 04024 PQclear(res); 04025 return true; 04026 } 04027 04028 /* 04029 * \deu 04030 * 04031 * Describes user mappings. 04032 */ 04033 bool 04034 listUserMappings(const char *pattern, bool verbose) 04035 { 04036 PQExpBufferData buf; 04037 PGresult *res; 04038 printQueryOpt myopt = pset.popt; 04039 04040 if (pset.sversion < 80400) 04041 { 04042 psql_error("The server (version %d.%d) does not support user mappings.\n", 04043 pset.sversion / 10000, (pset.sversion / 100) % 100); 04044 return true; 04045 } 04046 04047 initPQExpBuffer(&buf); 04048 printfPQExpBuffer(&buf, 04049 "SELECT um.srvname AS \"%s\",\n" 04050 " um.usename AS \"%s\"", 04051 gettext_noop("Server"), 04052 gettext_noop("User name")); 04053 04054 if (verbose) 04055 appendPQExpBuffer(&buf, 04056 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE " 04057 " '(' || array_to_string(ARRAY(SELECT " 04058 " quote_ident(option_name) || ' ' || " 04059 " quote_literal(option_value) FROM " 04060 " pg_options_to_table(umoptions)), ', ') || ')' " 04061 " END AS \"%s\"", 04062 gettext_noop("FDW Options")); 04063 04064 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n"); 04065 04066 processSQLNamePattern(pset.db, &buf, pattern, false, false, 04067 NULL, "um.srvname", "um.usename", NULL); 04068 04069 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 04070 04071 res = PSQLexec(buf.data, false); 04072 termPQExpBuffer(&buf); 04073 if (!res) 04074 return false; 04075 04076 myopt.nullPrint = NULL; 04077 myopt.title = _("List of user mappings"); 04078 myopt.translate_header = true; 04079 04080 printQuery(res, &myopt, pset.queryFout, pset.logfile); 04081 04082 PQclear(res); 04083 return true; 04084 } 04085 04086 /* 04087 * \det 04088 * 04089 * Describes foreign tables. 04090 */ 04091 bool 04092 listForeignTables(const char *pattern, bool verbose) 04093 { 04094 PQExpBufferData buf; 04095 PGresult *res; 04096 printQueryOpt myopt = pset.popt; 04097 04098 if (pset.sversion < 90100) 04099 { 04100 psql_error("The server (version %d.%d) does not support foreign tables.\n", 04101 pset.sversion / 10000, (pset.sversion / 100) % 100); 04102 return true; 04103 } 04104 04105 initPQExpBuffer(&buf); 04106 printfPQExpBuffer(&buf, 04107 "SELECT n.nspname AS \"%s\",\n" 04108 " c.relname AS \"%s\",\n" 04109 " s.srvname AS \"%s\"", 04110 gettext_noop("Schema"), 04111 gettext_noop("Table"), 04112 gettext_noop("Server")); 04113 04114 if (verbose) 04115 appendPQExpBuffer(&buf, 04116 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE " 04117 " '(' || array_to_string(ARRAY(SELECT " 04118 " quote_ident(option_name) || ' ' || " 04119 " quote_literal(option_value) FROM " 04120 " pg_options_to_table(ftoptions)), ', ') || ')' " 04121 " END AS \"%s\",\n" 04122 " d.description AS \"%s\"", 04123 gettext_noop("FDW Options"), 04124 gettext_noop("Description")); 04125 04126 appendPQExpBuffer(&buf, 04127 "\nFROM pg_catalog.pg_foreign_table ft\n" 04128 " INNER JOIN pg_catalog.pg_class c" 04129 " ON c.oid = ft.ftrelid\n" 04130 " INNER JOIN pg_catalog.pg_namespace n" 04131 " ON n.oid = c.relnamespace\n" 04132 " INNER JOIN pg_catalog.pg_foreign_server s" 04133 " ON s.oid = ft.ftserver\n"); 04134 if (verbose) 04135 appendPQExpBuffer(&buf, 04136 " LEFT JOIN pg_catalog.pg_description d\n" 04137 " ON d.classoid = c.tableoid AND " 04138 "d.objoid = c.oid AND d.objsubid = 0\n"); 04139 04140 processSQLNamePattern(pset.db, &buf, pattern, false, false, 04141 NULL, "n.nspname", "c.relname", NULL); 04142 04143 appendPQExpBuffer(&buf, "ORDER BY 1, 2;"); 04144 04145 res = PSQLexec(buf.data, false); 04146 termPQExpBuffer(&buf); 04147 if (!res) 04148 return false; 04149 04150 myopt.nullPrint = NULL; 04151 myopt.title = _("List of foreign tables"); 04152 myopt.translate_header = true; 04153 04154 printQuery(res, &myopt, pset.queryFout, pset.logfile); 04155 04156 PQclear(res); 04157 return true; 04158 } 04159 04160 /* 04161 * \dx 04162 * 04163 * Briefly describes installed extensions. 04164 */ 04165 bool 04166 listExtensions(const char *pattern) 04167 { 04168 PQExpBufferData buf; 04169 PGresult *res; 04170 printQueryOpt myopt = pset.popt; 04171 04172 if (pset.sversion < 90100) 04173 { 04174 psql_error("The server (version %d.%d) does not support extensions.\n", 04175 pset.sversion / 10000, (pset.sversion / 100) % 100); 04176 return true; 04177 } 04178 04179 initPQExpBuffer(&buf); 04180 printfPQExpBuffer(&buf, 04181 "SELECT e.extname AS \"%s\", " 04182 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n" 04183 "FROM pg_catalog.pg_extension e " 04184 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace " 04185 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid " 04186 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n", 04187 gettext_noop("Name"), 04188 gettext_noop("Version"), 04189 gettext_noop("Schema"), 04190 gettext_noop("Description")); 04191 04192 processSQLNamePattern(pset.db, &buf, pattern, 04193 false, false, 04194 NULL, "e.extname", NULL, 04195 NULL); 04196 04197 appendPQExpBuffer(&buf, "ORDER BY 1;"); 04198 04199 res = PSQLexec(buf.data, false); 04200 termPQExpBuffer(&buf); 04201 if (!res) 04202 return false; 04203 04204 myopt.nullPrint = NULL; 04205 myopt.title = _("List of installed extensions"); 04206 myopt.translate_header = true; 04207 04208 printQuery(res, &myopt, pset.queryFout, pset.logfile); 04209 04210 PQclear(res); 04211 return true; 04212 } 04213 04214 /* 04215 * \dx+ 04216 * 04217 * List contents of installed extensions. 04218 */ 04219 bool 04220 listExtensionContents(const char *pattern) 04221 { 04222 PQExpBufferData buf; 04223 PGresult *res; 04224 int i; 04225 04226 if (pset.sversion < 90100) 04227 { 04228 psql_error("The server (version %d.%d) does not support extensions.\n", 04229 pset.sversion / 10000, (pset.sversion / 100) % 100); 04230 return true; 04231 } 04232 04233 initPQExpBuffer(&buf); 04234 printfPQExpBuffer(&buf, 04235 "SELECT e.extname, e.oid\n" 04236 "FROM pg_catalog.pg_extension e\n"); 04237 04238 processSQLNamePattern(pset.db, &buf, pattern, 04239 false, false, 04240 NULL, "e.extname", NULL, 04241 NULL); 04242 04243 appendPQExpBuffer(&buf, "ORDER BY 1;"); 04244 04245 res = PSQLexec(buf.data, false); 04246 termPQExpBuffer(&buf); 04247 if (!res) 04248 return false; 04249 04250 if (PQntuples(res) == 0) 04251 { 04252 if (!pset.quiet) 04253 { 04254 if (pattern) 04255 psql_error("Did not find any extension named \"%s\".\n", 04256 pattern); 04257 else 04258 psql_error("Did not find any extensions.\n"); 04259 } 04260 PQclear(res); 04261 return false; 04262 } 04263 04264 for (i = 0; i < PQntuples(res); i++) 04265 { 04266 const char *extname; 04267 const char *oid; 04268 04269 extname = PQgetvalue(res, i, 0); 04270 oid = PQgetvalue(res, i, 1); 04271 04272 if (!listOneExtensionContents(extname, oid)) 04273 { 04274 PQclear(res); 04275 return false; 04276 } 04277 if (cancel_pressed) 04278 { 04279 PQclear(res); 04280 return false; 04281 } 04282 } 04283 04284 PQclear(res); 04285 return true; 04286 } 04287 04288 static bool 04289 listOneExtensionContents(const char *extname, const char *oid) 04290 { 04291 PQExpBufferData buf; 04292 PGresult *res; 04293 char title[1024]; 04294 printQueryOpt myopt = pset.popt; 04295 04296 initPQExpBuffer(&buf); 04297 printfPQExpBuffer(&buf, 04298 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n" 04299 "FROM pg_catalog.pg_depend\n" 04300 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n" 04301 "ORDER BY 1;", 04302 gettext_noop("Object Description"), 04303 oid); 04304 04305 res = PSQLexec(buf.data, false); 04306 termPQExpBuffer(&buf); 04307 if (!res) 04308 return false; 04309 04310 myopt.nullPrint = NULL; 04311 snprintf(title, sizeof(title), _("Objects in extension \"%s\""), extname); 04312 myopt.title = title; 04313 myopt.translate_header = true; 04314 04315 printQuery(res, &myopt, pset.queryFout, pset.logfile); 04316 04317 PQclear(res); 04318 return true; 04319 } 04320 04321 /* 04322 * printACLColumn 04323 * 04324 * Helper function for consistently formatting ACL (privilege) columns. 04325 * The proper targetlist entry is appended to buf. Note lack of any 04326 * whitespace or comma decoration. 04327 */ 04328 static void 04329 printACLColumn(PQExpBuffer buf, const char *colname) 04330 { 04331 if (pset.sversion >= 80100) 04332 appendPQExpBuffer(buf, 04333 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"", 04334 colname, gettext_noop("Access privileges")); 04335 else 04336 appendPQExpBuffer(buf, 04337 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"", 04338 colname, gettext_noop("Access privileges")); 04339 }
1.7.1