granicus.if.org Git - postgresql/blob - src/bin/psql/describe.c

   1 /*
   2  * psql - the PostgreSQL interactive terminal
   3  *
   4  * Support for the various \d ("describe") commands.  Note that the current
   5  * expectation is that all functions in this file will succeed when working
   6  * with servers of versions 7.4 and up.  It's okay to omit irrelevant
   7  * information for an old server, but not to fail outright.
   8  *
   9  * Copyright (c) 2000-2008, PostgreSQL Global Development Group
  10  *
  11  * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.180 2008/07/15 03:16:03 momjian Exp $
  12  */
  13 #include "postgres_fe.h"
  14
  15 #include <ctype.h>
  16
  17 #include "common.h"
  18 #include "describe.h"
  19 #include "dumputils.h"
  20 #include "mbprint.h"
  21 #include "print.h"
  22 #include "settings.h"
  23 #include "variables.h"
  24
  25
  26 static bool describeOneTableDetails(const char *schemaname,
  27                                                 const char *relationname,
  28                                                 const char *oid,
  29                                                 bool verbose);
  30 static void add_tablespace_footer(printTableContent *const cont, char relkind,
  31                                                 Oid tablespace, const bool newline);
  32 static void add_role_attribute(PQExpBuffer buf, const char *const str);
  33 static bool listTSParsersVerbose(const char *pattern);
  34 static bool describeOneTSParser(const char *oid, const char *nspname,
  35                                         const char *prsname);
  36 static bool listTSConfigsVerbose(const char *pattern);
  37 static bool describeOneTSConfig(const char *oid, const char *nspname,
  38                                         const char *cfgname,
  39                                         const char *pnspname, const char *prsname);
  40
  41
  42 /*----------------
  43  * Handlers for various slash commands displaying some sort of list
  44  * of things in the database.
  45  *
  46  * Note: try to format the queries to look nice in -E output.
  47  *----------------
  48  */
  49
  50
  51 /* \da
  52  * Takes an optional regexp to select particular aggregates
  53  */
  54 bool
  55 describeAggregates(const char *pattern, bool verbose)
  56 {
  57         PQExpBufferData buf;
  58         PGresult   *res;
  59         printQueryOpt myopt = pset.popt;
  60
  61         initPQExpBuffer(&buf);
  62
  63         printfPQExpBuffer(&buf,
  64                                           "SELECT n.nspname as \"%s\",\n"
  65                                           "  p.proname AS \"%s\",\n"
  66                                           "  pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n",
  67                                           gettext_noop("Schema"),
  68                                           gettext_noop("Name"),
  69                                           gettext_noop("Result data type"));
  70
  71         if (pset.sversion >= 80200)
  72             appendPQExpBuffer(&buf,
  73                                           "  CASE WHEN p.pronargs = 0\n"
  74                                           "    THEN CAST('*' AS pg_catalog.text)\n"
  75                                           "    ELSE\n"
  76                                           "    pg_catalog.array_to_string(ARRAY(\n"
  77                                           "      SELECT\n"
  78                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
  79                                           "      FROM\n"
  80                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
  81                                           "    ), ', ')\n"
  82                                           "  END AS \"%s\",\n",
  83                                           gettext_noop("Argument data types"));
  84         else
  85             appendPQExpBuffer(&buf,
  86                                           "  pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
  87                                           gettext_noop("Argument data types"));
  88
  89         appendPQExpBuffer(&buf,
  90                                  "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
  91                                           "FROM pg_catalog.pg_proc p\n"
  92            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  93                                           "WHERE p.proisagg\n",
  94                                           gettext_noop("Description"));
  95
  96         processSQLNamePattern(pset.db, &buf, pattern, true, false,
  97                                                   "n.nspname", "p.proname", NULL,
  98                                                   "pg_catalog.pg_function_is_visible(p.oid)");
  99
 100         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
 101
 102         res = PSQLexec(buf.data, false);
 103         termPQExpBuffer(&buf);
 104         if (!res)
 105                 return false;
 106
 107         myopt.nullPrint = NULL;
 108         myopt.title = _("List of aggregate functions");
 109         myopt.translate_header = true;
 110
 111         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 112
 113         PQclear(res);
 114         return true;
 115 }
 116
 117 /* \db
 118  * Takes an optional regexp to select particular tablespaces
 119  */
 120 bool
 121 describeTablespaces(const char *pattern, bool verbose)
 122 {
 123         PQExpBufferData buf;
 124         PGresult   *res;
 125         printQueryOpt myopt = pset.popt;
 126
 127         if (pset.sversion < 80000)
 128         {
 129                 fprintf(stderr, _("The server (version %d.%d) does not support tablespaces.\n"),
 130                                 pset.sversion / 10000, (pset.sversion / 100) % 100);
 131                 return true;
 132         }
 133
 134         initPQExpBuffer(&buf);
 135
 136         printfPQExpBuffer(&buf,
 137                                           "SELECT spcname AS \"%s\",\n"
 138                                           "  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
 139                                           "  spclocation AS \"%s\"",
 140                                           gettext_noop("Name"),
 141                                           gettext_noop("Owner"),
 142                                           gettext_noop("Location"));
 143
 144         if (verbose)
 145                 appendPQExpBuffer(&buf,
 146                                                   ",\n  spcacl AS \"%s\"",
 147                                                   gettext_noop("Access privileges"));
 148         if (verbose && pset.sversion >= 80200)
 149                 appendPQExpBuffer(&buf,
 150                           ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 151                                                   gettext_noop("Description"));
 152
 153         appendPQExpBuffer(&buf,
 154                                           "\nFROM pg_catalog.pg_tablespace\n");
 155
 156         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 157                                                   NULL, "spcname", NULL,
 158                                                   NULL);
 159
 160         appendPQExpBuffer(&buf, "ORDER BY 1;");
 161
 162         res = PSQLexec(buf.data, false);
 163         termPQExpBuffer(&buf);
 164         if (!res)
 165                 return false;
 166
 167         myopt.nullPrint = NULL;
 168         myopt.title = _("List of tablespaces");
 169         myopt.translate_header = true;
 170
 171         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 172
 173         PQclear(res);
 174         return true;
 175 }
 176
 177
 178 /* \df
 179  * Takes an optional regexp to select particular functions
 180  */
 181 bool
 182 describeFunctions(const char *pattern, bool verbose)
 183 {
 184         PQExpBufferData buf;
 185         PGresult   *res;
 186         printQueryOpt myopt = pset.popt;
 187
 188         initPQExpBuffer(&buf);
 189
 190         printfPQExpBuffer(&buf,
 191                                           "SELECT n.nspname as \"%s\",\n"
 192                                           "  p.proname as \"%s\",\n"
 193                                           "  CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||\n"
 194                                   "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n",
 195                                           gettext_noop("Schema"),
 196                                           gettext_noop("Name"),
 197                                           gettext_noop("Result data type"));
 198
 199     if (pset.sversion >= 80100)
 200                 appendPQExpBuffer(&buf,
 201                                           "  CASE WHEN proallargtypes IS NOT NULL THEN\n"
 202                                           "    pg_catalog.array_to_string(ARRAY(\n"
 203                                           "      SELECT\n"
 204                                           "        CASE\n"
 205                                           "          WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
 206                                           "          WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
 207                                         "          WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
 208                                           "        END ||\n"
 209                                           "        CASE\n"
 210                          "          WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
 211                                           "          ELSE p.proargnames[s.i] || ' ' \n"
 212                                           "        END ||\n"
 213                           "        pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
 214                                           "      FROM\n"
 215                                           "        pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
 216                                           "    ), ', ')\n"
 217                                           "  ELSE\n"
 218                                           "    pg_catalog.array_to_string(ARRAY(\n"
 219                                           "      SELECT\n"
 220                                           "        CASE\n"
 221                    "          WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
 222                                           "          ELSE p.proargnames[s.i+1] || ' '\n"
 223                                           "          END ||\n"
 224                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
 225                                           "      FROM\n"
 226                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
 227                                           "    ), ', ')\n"
 228                                           "  END AS \"%s\"",
 229                                           gettext_noop("Argument data types"));
 230         else
 231                 appendPQExpBuffer(&buf,
 232                                           "  pg_catalog.oidvectortypes(p.proargtypes) as \"%s\"",
 233                                                   gettext_noop("Argument data types"));
 234
 235         if (verbose)
 236                 appendPQExpBuffer(&buf,
 237                                                   ",\n CASE\n"
 238                                                   "  WHEN p.provolatile = 'i' THEN 'immutable'\n"
 239                                                   "  WHEN p.provolatile = 's' THEN 'stable'\n"
 240                                                   "  WHEN p.provolatile = 'v' THEN 'volatile'\n"
 241                                                   "END as \"%s\""
 242                                                   ",\n  pg_catalog.pg_get_userbyid(p.proowner) as \"%s\",\n"
 243                                                   "  l.lanname as \"%s\",\n"
 244                                                   "  p.prosrc as \"%s\",\n"
 245                                   "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
 246                                                   gettext_noop("Volatility"),
 247                                                   gettext_noop("Owner"),
 248                                                   gettext_noop("Language"),
 249                                                   gettext_noop("Source code"),
 250                                                   gettext_noop("Description"));
 251
 252         appendPQExpBuffer(&buf,
 253                                           "\nFROM pg_catalog.pg_proc p"
 254                                           "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
 255
 256         if (verbose)
 257                 appendPQExpBuffer(&buf,
 258                                                   "     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
 259
 260         /*
 261          * we skip in/out funcs by excluding functions that take or return cstring
 262          */
 263         appendPQExpBuffer(&buf,
 264                    "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 265                                           "      AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
 266                                           "      AND NOT p.proisagg\n");
 267
 268         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 269                                                   "n.nspname", "p.proname", NULL,
 270                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 271
 272         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
 273
 274         res = PSQLexec(buf.data, false);
 275         termPQExpBuffer(&buf);
 276         if (!res)
 277                 return false;
 278
 279         myopt.nullPrint = NULL;
 280         myopt.title = _("List of functions");
 281         myopt.translate_header = true;
 282
 283         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 284
 285         PQclear(res);
 286         return true;
 287 }
 288
 289
 290
 291 /*
 292  * \dT
 293  * describe types
 294  */
 295 bool
 296 describeTypes(const char *pattern, bool verbose)
 297 {
 298         PQExpBufferData buf;
 299         PGresult   *res;
 300         printQueryOpt myopt = pset.popt;
 301
 302         initPQExpBuffer(&buf);
 303
 304         printfPQExpBuffer(&buf,
 305                                           "SELECT n.nspname as \"%s\",\n"
 306                                           "  pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
 307                                           gettext_noop("Schema"),
 308                                           gettext_noop("Name"));
 309         if (verbose)
 310                 appendPQExpBuffer(&buf,
 311                                                   "  t.typname AS \"%s\",\n"
 312                                                   "  CASE WHEN t.typrelid != 0\n"
 313                                                   "      THEN CAST('tuple' AS pg_catalog.text)\n"
 314                                                   "    WHEN t.typlen < 0\n"
 315                                                   "      THEN CAST('var' AS pg_catalog.text)\n"
 316                                                   "    ELSE CAST(t.typlen AS pg_catalog.text)\n"
 317                                                   "  END AS \"%s\",\n",
 318                                                   gettext_noop("Internal name"),
 319                                                   gettext_noop("Size"));
 320         if (verbose && pset.sversion >= 80300)
 321                 appendPQExpBuffer(&buf,
 322                                                   "  pg_catalog.array_to_string(\n"
 323                                                   "      ARRAY(\n"
 324                                                   "                  SELECT e.enumlabel\n"
 325                                                   "          FROM pg_catalog.pg_enum e\n"
 326                                                   "          WHERE e.enumtypid = t.oid\n"
 327                                                   "          ORDER BY e.oid\n"
 328                                                   "      ),\n"
 329                                                   "      E'\\n'\n"
 330                                                   "  ) AS \"%s\",\n",
 331                                                   gettext_noop("Elements"));
 332
 333         appendPQExpBuffer(&buf,
 334                                           "  pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
 335                                           gettext_noop("Description"));
 336
 337         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
 338          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
 339
 340         /*
 341          * do not include complex types (typrelid!=0) unless they are standalone
 342          * composite types
 343          */
 344         appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
 345         appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
 346                                           "WHERE c.oid = t.typrelid))\n");
 347         /*
 348          * do not include array types (before 8.3 we have to use the assumption
 349          * that their names start with underscore)
 350          */
 351         if (pset.sversion >= 80300)
 352                 appendPQExpBuffer(&buf, "  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
 353         else
 354                 appendPQExpBuffer(&buf, "  AND t.typname !~ '^_'\n");
 355
 356         /* Match name pattern against either internal or external name */
 357         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 358                                                   "n.nspname", "t.typname",
 359                                                   "pg_catalog.format_type(t.oid, NULL)",
 360                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 361
 362         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 363
 364         res = PSQLexec(buf.data, false);
 365         termPQExpBuffer(&buf);
 366         if (!res)
 367                 return false;
 368
 369         myopt.nullPrint = NULL;
 370         myopt.title = _("List of data types");
 371         myopt.translate_header = true;
 372
 373         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 374
 375         PQclear(res);
 376         return true;
 377 }
 378
 379
 380 /* \do
 381  */
 382 bool
 383 describeOperators(const char *pattern)
 384 {
 385         PQExpBufferData buf;
 386         PGresult   *res;
 387         printQueryOpt myopt = pset.popt;
 388
 389         initPQExpBuffer(&buf);
 390
 391         printfPQExpBuffer(&buf,
 392                                           "SELECT n.nspname as \"%s\",\n"
 393                                           "  o.oprname AS \"%s\",\n"
 394                                           "  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
 395                                           "  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
 396                                    "  pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
 397                          "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
 398         "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
 399                                           "FROM pg_catalog.pg_operator o\n"
 400           "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 401                                           gettext_noop("Schema"),
 402                                           gettext_noop("Name"),
 403                                           gettext_noop("Left arg type"),
 404                                           gettext_noop("Right arg type"),
 405                                           gettext_noop("Result type"),
 406                                           gettext_noop("Description"));
 407
 408         processSQLNamePattern(pset.db, &buf, pattern, false, true,
 409                                                   "n.nspname", "o.oprname", NULL,
 410                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 411
 412         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
 413
 414         res = PSQLexec(buf.data, false);
 415         termPQExpBuffer(&buf);
 416         if (!res)
 417                 return false;
 418
 419         myopt.nullPrint = NULL;
 420         myopt.title = _("List of operators");
 421         myopt.translate_header = true;
 422
 423         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 424
 425         PQclear(res);
 426         return true;
 427 }
 428
 429
 430 /*
 431  * listAllDbs
 432  *
 433  * for \l, \list, and -l switch
 434  */
 435 bool
 436 listAllDbs(bool verbose)
 437 {
 438         PGresult   *res;
 439         PQExpBufferData buf;
 440         printQueryOpt myopt = pset.popt;
 441
 442         initPQExpBuffer(&buf);
 443
 444         printfPQExpBuffer(&buf,
 445                                           "SELECT d.datname as \"%s\",\n"
 446                                           "       pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
 447                                           "       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n"
 448                                           "       d.datacl as \"%s\"",
 449                                           gettext_noop("Name"),
 450                                           gettext_noop("Owner"),
 451                                           gettext_noop("Encoding"),
 452                                           gettext_noop("Access Privileges"));
 453         if (verbose && pset.sversion >= 80200)
 454                 appendPQExpBuffer(&buf,
 455                                                   ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 456                                                   "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 457                                                   "            ELSE 'No Access'\n"
 458                                                   "       END as \"%s\"",
 459                                                   gettext_noop("Size"));
 460         if (verbose && pset.sversion >= 80000)
 461                 appendPQExpBuffer(&buf,
 462                                                   ",\n       t.spcname as \"%s\"",
 463                                                   gettext_noop("Tablespace"));
 464         if (verbose && pset.sversion >= 80200)
 465                     appendPQExpBuffer(&buf,
 466                                                   ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
 467                                                   gettext_noop("Description"));
 468         appendPQExpBuffer(&buf,
 469                                           "\nFROM pg_catalog.pg_database d\n");
 470         if (verbose && pset.sversion >= 80000)
 471                 appendPQExpBuffer(&buf,
 472                    "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 473         appendPQExpBuffer(&buf, "ORDER BY 1;");
 474         res = PSQLexec(buf.data, false);
 475         termPQExpBuffer(&buf);
 476         if (!res)
 477                 return false;
 478
 479         myopt.nullPrint = NULL;
 480         myopt.title = _("List of databases");
 481         myopt.translate_header = true;
 482
 483         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 484
 485         PQclear(res);
 486         return true;
 487 }
 488
 489
 490 /*
 491  * List Tables Grant/Revoke Permissions
 492  * \z (now also \dp -- perhaps more mnemonic)
 493  */
 494 bool
 495 permissionsList(const char *pattern)
 496 {
 497         PQExpBufferData buf;
 498         PGresult   *res;
 499         printQueryOpt myopt = pset.popt;
 500         static const bool translate_columns[] = {false, false, true, false};
 501
 502         initPQExpBuffer(&buf);
 503
 504         /*
 505          * we ignore indexes and toast tables since they have no meaningful rights
 506          */
 507         printfPQExpBuffer(&buf,
 508                                           "SELECT n.nspname as \"%s\",\n"
 509                                           "  c.relname as \"%s\",\n"
 510                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n",
 511                                           gettext_noop("Schema"),
 512                                           gettext_noop("Name"),
 513                                           gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"),
 514                                           gettext_noop("Type"));
 515
 516     if (pset.sversion >= 80100)
 517             appendPQExpBuffer(&buf, "  pg_catalog.array_to_string(c.relacl, E'\\n') as \"%s\"\n",
 518                                           gettext_noop("Access privileges"));
 519     else
 520             appendPQExpBuffer(&buf, "  pg_catalog.array_to_string(c.relacl, '\\n') as \"%s\"\n",
 521                                           gettext_noop("Access privileges"));
 522
 523         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_class c\n"
 524            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 525                                           "WHERE c.relkind IN ('r', 'v', 'S')\n");
 526
 527         /*
 528          * Unless a schema pattern is specified, we suppress system and temp
 529          * tables, since they normally aren't very interesting from a permissions
 530          * point of view.  You can see 'em by explicit request though, eg with \z
 531          * pg_catalog.*
 532          */
 533         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 534                                                   "n.nspname", "c.relname", NULL,
 535                         "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
 536
 537         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 538
 539         res = PSQLexec(buf.data, false);
 540         if (!res)
 541         {
 542                 termPQExpBuffer(&buf);
 543                 return false;
 544         }
 545
 546         myopt.nullPrint = NULL;
 547         printfPQExpBuffer(&buf, _("Access privileges"));
 548         myopt.title = buf.data;
 549         myopt.translate_header = true;
 550         myopt.translate_columns = translate_columns;
 551
 552         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 553
 554         termPQExpBuffer(&buf);
 555         PQclear(res);
 556         return true;
 557 }
 558
 559
 560
 561 /*
 562  * Get object comments
 563  *
 564  * \dd [foo]
 565  *
 566  * Note: This only lists things that actually have a description. For complete
 567  * lists of things, there are other \d? commands.
 568  */
 569 bool
 570 objectDescription(const char *pattern)
 571 {
 572         PQExpBufferData buf;
 573         PGresult   *res;
 574         printQueryOpt myopt = pset.popt;
 575         static const bool translate_columns[] = {false, false, true, false};
 576
 577         initPQExpBuffer(&buf);
 578
 579         appendPQExpBuffer(&buf,
 580                                           "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
 581                                           "FROM (\n",
 582                                           gettext_noop("Schema"),
 583                                           gettext_noop("Name"),
 584                                           gettext_noop("Object"),
 585                                           gettext_noop("Description"));
 586
 587         /* Aggregate descriptions */
 588         appendPQExpBuffer(&buf,
 589                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 590                                           "  n.nspname as nspname,\n"
 591                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 592                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 593                                           "  FROM pg_catalog.pg_proc p\n"
 594          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 595                                           "  WHERE p.proisagg\n",
 596                                           gettext_noop("aggregate"));
 597         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 598                                                   "n.nspname", "p.proname", NULL,
 599                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 600
 601         /* Function descriptions (except in/outs for datatypes) */
 602         appendPQExpBuffer(&buf,
 603                                           "UNION ALL\n"
 604                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 605                                           "  n.nspname as nspname,\n"
 606                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 607                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 608                                           "  FROM pg_catalog.pg_proc p\n"
 609          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 610
 611                  "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 612                                           "      AND (p.proargtypes[0] IS NULL\n"
 613                                           "      OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
 614                                           "      AND NOT p.proisagg\n",
 615                                           gettext_noop("function"));
 616         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 617                                                   "n.nspname", "p.proname", NULL,
 618                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 619
 620         /* Operator descriptions (only if operator has its own comment) */
 621         appendPQExpBuffer(&buf,
 622                                           "UNION ALL\n"
 623                                           "  SELECT o.oid as oid, o.tableoid as tableoid,\n"
 624                                           "  n.nspname as nspname,\n"
 625                                           "  CAST(o.oprname AS pg_catalog.text) as name,"
 626                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 627                                           "  FROM pg_catalog.pg_operator o\n"
 628         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 629                                           gettext_noop("operator"));
 630         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 631                                                   "n.nspname", "o.oprname", NULL,
 632                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 633
 634         /* Type description */
 635         appendPQExpBuffer(&buf,
 636                                           "UNION ALL\n"
 637                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 638                                           "  n.nspname as nspname,\n"
 639                                           "  pg_catalog.format_type(t.oid, NULL) as name,"
 640                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 641                                           "  FROM pg_catalog.pg_type t\n"
 642         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
 643                                           gettext_noop("data type"));
 644         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 645                                                   "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
 646                                                   NULL,
 647                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 648
 649         /* Relation (tables, views, indexes, sequences) descriptions */
 650         appendPQExpBuffer(&buf,
 651                                           "UNION ALL\n"
 652                                           "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
 653                                           "  n.nspname as nspname,\n"
 654                                           "  CAST(c.relname AS pg_catalog.text) as name,\n"
 655                                           "  CAST(\n"
 656                                           "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
 657                                           "  AS pg_catalog.text) as object\n"
 658                                           "  FROM pg_catalog.pg_class c\n"
 659          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 660                                           "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
 661                                           gettext_noop("table"),
 662                                           gettext_noop("view"),
 663                                           gettext_noop("index"),
 664                                           gettext_noop("sequence"));
 665         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 666                                                   "n.nspname", "c.relname", NULL,
 667                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 668
 669         /* Rule description (ignore rules for views) */
 670         appendPQExpBuffer(&buf,
 671                                           "UNION ALL\n"
 672                                           "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
 673                                           "  n.nspname as nspname,\n"
 674                                           "  CAST(r.rulename AS pg_catalog.text) as name,"
 675                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 676                                           "  FROM pg_catalog.pg_rewrite r\n"
 677                                   "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
 678          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 679                                           "  WHERE r.rulename != '_RETURN'\n",
 680                                           gettext_noop("rule"));
 681         /* XXX not sure what to do about visibility rule here? */
 682         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 683                                                   "n.nspname", "r.rulename", NULL,
 684                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 685
 686         /* Trigger description */
 687         appendPQExpBuffer(&buf,
 688                                           "UNION ALL\n"
 689                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 690                                           "  n.nspname as nspname,\n"
 691                                           "  CAST(t.tgname AS pg_catalog.text) as name,"
 692                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 693                                           "  FROM pg_catalog.pg_trigger t\n"
 694                                    "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
 695         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
 696                                           gettext_noop("trigger"));
 697         /* XXX not sure what to do about visibility rule here? */
 698         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 699                                                   "n.nspname", "t.tgname", NULL,
 700                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 701
 702         appendPQExpBuffer(&buf,
 703                                           ") AS tt\n"
 704                                           "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
 705
 706         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
 707
 708         res = PSQLexec(buf.data, false);
 709         termPQExpBuffer(&buf);
 710         if (!res)
 711                 return false;
 712
 713         myopt.nullPrint = NULL;
 714         myopt.title = _("Object descriptions");
 715         myopt.translate_header = true;
 716         myopt.translate_columns = translate_columns;
 717
 718         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 719
 720         PQclear(res);
 721         return true;
 722 }
 723
 724
 725 /*
 726  * describeTableDetails (for \d)
 727  *
 728  * This routine finds the tables to be displayed, and calls
 729  * describeOneTableDetails for each one.
 730  *
 731  * verbose: if true, this is \d+
 732  */
 733 bool
 734 describeTableDetails(const char *pattern, bool verbose)
 735 {
 736         PQExpBufferData buf;
 737         PGresult   *res;
 738         int                     i;
 739
 740         initPQExpBuffer(&buf);
 741
 742         printfPQExpBuffer(&buf,
 743                                           "SELECT c.oid,\n"
 744                                           "  n.nspname,\n"
 745                                           "  c.relname\n"
 746                                           "FROM pg_catalog.pg_class c\n"
 747          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
 748
 749         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 750                                                   "n.nspname", "c.relname", NULL,
 751                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 752
 753         appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
 754
 755         res = PSQLexec(buf.data, false);
 756         termPQExpBuffer(&buf);
 757         if (!res)
 758                 return false;
 759
 760         if (PQntuples(res) == 0)
 761         {
 762                 if (!pset.quiet)
 763                         fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
 764                                         pattern);
 765                 PQclear(res);
 766                 return false;
 767         }
 768
 769         for (i = 0; i < PQntuples(res); i++)
 770         {
 771                 const char *oid;
 772                 const char *nspname;
 773                 const char *relname;
 774
 775                 oid = PQgetvalue(res, i, 0);
 776                 nspname = PQgetvalue(res, i, 1);
 777                 relname = PQgetvalue(res, i, 2);
 778
 779                 if (!describeOneTableDetails(nspname, relname, oid, verbose))
 780                 {
 781                         PQclear(res);
 782                         return false;
 783                 }
 784                 if (cancel_pressed)
 785                 {
 786                         PQclear(res);
 787                         return false;
 788                 }
 789         }
 790
 791         PQclear(res);
 792         return true;
 793 }
 794
 795 /*
 796  * describeOneTableDetails (for \d)
 797  *
 798  * Unfortunately, the information presented here is so complicated that it
 799  * cannot be done in a single query. So we have to assemble the printed table
 800  * by hand and pass it to the underlying printTable() function.
 801  */
 802 static bool
 803 describeOneTableDetails(const char *schemaname,
 804                                                 const char *relationname,
 805                                                 const char *oid,
 806                                                 bool verbose)
 807 {
 808         PQExpBufferData buf;
 809         PGresult   *res = NULL;
 810         printTableOpt myopt = pset.popt.topt;
 811         printTableContent cont;
 812         int                     i;
 813         char       *view_def = NULL;
 814         char       *headers[6];
 815         char      **seq_values = NULL;
 816         char      **modifiers = NULL;
 817         char      **ptr;
 818         PQExpBufferData title;
 819         PQExpBufferData tmpbuf;
 820         int                     cols = 0;
 821         int                     numrows = 0;
 822         struct
 823         {
 824                 int16           checks;
 825                 int16           triggers;
 826                 char            relkind;
 827                 bool            hasindex;
 828                 bool            hasrules;
 829                 bool            hasoids;
 830                 Oid                     tablespace;
 831         }                       tableinfo;
 832         bool            show_modifiers = false;
 833         bool            retval;
 834
 835         retval = false;
 836
 837         /* This output looks confusing in expanded mode. */
 838         myopt.expanded = false;
 839
 840         initPQExpBuffer(&buf);
 841         initPQExpBuffer(&title);
 842         initPQExpBuffer(&tmpbuf);
 843
 844         /* Get general table info */
 845         printfPQExpBuffer(&buf,
 846            "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, "
 847                                           "relhasoids%s\n"
 848                                           "FROM pg_catalog.pg_class WHERE oid = '%s'",
 849                                           (pset.sversion >= 80000 ? ", reltablespace" : ""),
 850                                           oid);
 851         res = PSQLexec(buf.data, false);
 852         if (!res)
 853                 goto error_return;
 854
 855         /* Did we get anything? */
 856         if (PQntuples(res) == 0)
 857         {
 858                 if (!pset.quiet)
 859                         fprintf(stderr, _("Did not find any relation with OID %s.\n"),
 860                                         oid);
 861                 goto error_return;
 862         }
 863
 864         tableinfo.checks = atoi(PQgetvalue(res, 0, 2));
 865         tableinfo.triggers = atoi(PQgetvalue(res, 0, 3));
 866         tableinfo.relkind = *(PQgetvalue(res, 0, 1));
 867         tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
 868         tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
 869         tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
 870         tableinfo.tablespace = (pset.sversion >= 80000) ?
 871                 atooid(PQgetvalue(res, 0, 6)) : 0;
 872         PQclear(res);
 873
 874         /*
 875          * This is used to get the values of a sequence and store it in an
 876          * array that will be used later.
 877          */
 878         if (tableinfo.relkind == 'S')
 879         {
 880                 PGresult   *result;
 881
 882 #define SEQ_NUM_COLS 10
 883                 printfPQExpBuffer(&buf,
 884                                 "SELECT sequence_name, last_value, \n"
 885                                 "               start_value, increment_by, \n"
 886                                 "               max_value, min_value, cache_value, \n"
 887                                 "               log_cnt, is_cycled, is_called \n"
 888                                 "FROM \"%s\"",
 889                                 relationname);
 890
 891                 result = PSQLexec(buf.data, false);
 892                 if (!result)
 893                         goto error_return;
 894
 895                 seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values));
 896
 897                 for (i = 0; i < SEQ_NUM_COLS; i++)
 898                         seq_values[i] = pg_strdup(PQgetvalue(result, 0, i));
 899
 900                 PQclear(result);
 901         }
 902
 903         /* Get column info (index requires additional checks) */
 904         printfPQExpBuffer(&buf, "SELECT a.attname,");
 905         appendPQExpBuffer(&buf, "\n  pg_catalog.format_type(a.atttypid, a.atttypmod),"
 906                                           "\n  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
 907                                           "\n   FROM pg_catalog.pg_attrdef d"
 908                                           "\n   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
 909                                           "\n  a.attnotnull, a.attnum");
 910         if (verbose)
 911                 appendPQExpBuffer(&buf, ", a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum)");
 912         appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
 913         if (tableinfo.relkind == 'i')
 914                 appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
 915         appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
 916         if (tableinfo.relkind == 'i')
 917                 appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
 918         appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
 919
 920         res = PSQLexec(buf.data, false);
 921         if (!res)
 922                 goto error_return;
 923         numrows = PQntuples(res);
 924
 925         /* Make title */
 926         switch (tableinfo.relkind)
 927         {
 928                 case 'r':
 929                         printfPQExpBuffer(&title, _("Table \"%s.%s\""),
 930                                                           schemaname, relationname);
 931                         break;
 932                 case 'v':
 933                         printfPQExpBuffer(&title, _("View \"%s.%s\""),
 934                                                           schemaname, relationname);
 935                         break;
 936                 case 'S':
 937                         printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
 938                                                           schemaname, relationname);
 939                         break;
 940                 case 'i':
 941                         printfPQExpBuffer(&title, _("Index \"%s.%s\""),
 942                                                           schemaname, relationname);
 943                         break;
 944                 case 's':
 945                         /* not used as of 8.2, but keep it for backwards compatibility */
 946                         printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
 947                                                           schemaname, relationname);
 948                         break;
 949                 case 't':
 950                         printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
 951                                                           schemaname, relationname);
 952                         break;
 953                 case 'c':
 954                         printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
 955                                                           schemaname, relationname);
 956                         break;
 957                 default:
 958                         /* untranslated unknown relkind */
 959                         printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
 960                                                           tableinfo.relkind, schemaname, relationname);
 961                         break;
 962         }
 963
 964         /* Set the number of columns, and their names */
 965         cols += 2;
 966         headers[0] = gettext_noop("Column");
 967         headers[1] = gettext_noop("Type");
 968
 969         if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
 970         {
 971                 show_modifiers = true;
 972                 headers[cols++] = gettext_noop("Modifiers");
 973                 modifiers = pg_malloc_zero((numrows + 1) * sizeof(*modifiers));
 974         }
 975
 976         if (tableinfo.relkind == 'S')
 977                 headers[cols++] = gettext_noop("Value");
 978
 979         if (verbose)
 980         {
 981                 headers[cols++] = gettext_noop("Storage");
 982                 headers[cols++] = gettext_noop("Description");
 983         }
 984
 985         printTableInit(&cont, &myopt, title.data, cols, numrows);
 986
 987         for (i = 0; i < cols; i++)
 988                 printTableAddHeader(&cont, headers[i], true, 'l');
 989
 990         /* Check if table is a view */
 991         if (tableinfo.relkind == 'v')
 992         {
 993                 PGresult   *result;
 994
 995                 printfPQExpBuffer(&buf,
 996                                                   "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)",
 997                                                   oid);
 998                 result = PSQLexec(buf.data, false);
 999                 if (!result)
1000                         goto error_return;
1001
1002                 if (PQntuples(result) > 0)
1003                         view_def = pg_strdup(PQgetvalue(result, 0, 0));
1004
1005                 PQclear(result);
1006         }
1007
1008         /* Generate table cells to be printed */
1009         for (i = 0; i < numrows; i++)
1010         {
1011                 /* Column */
1012                 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1013
1014                 /* Type */
1015                 printTableAddCell(&cont, PQgetvalue(res, i, 1), false);
1016
1017                 /* A special 'Value' column for sequences */
1018                 if (tableinfo.relkind == 'S')
1019                         printTableAddCell(&cont, seq_values[i], false);
1020
1021                 /* Extra: not null and default */
1022                 if (show_modifiers)
1023                 {
1024                         resetPQExpBuffer(&tmpbuf);
1025                         if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1026                                 appendPQExpBufferStr(&tmpbuf, _("not null"));
1027
1028                         /* handle "default" here */
1029                         /* (note: above we cut off the 'default' string at 128) */
1030                         if (strlen(PQgetvalue(res, i, 2)) != 0)
1031                         {
1032                                 if (tmpbuf.len > 0)
1033                                         appendPQExpBufferStr(&tmpbuf, " ");
1034                                 /* translator: default values of column definitions */
1035                                 appendPQExpBuffer(&tmpbuf, _("default %s"),
1036                                                                   PQgetvalue(res, i, 2));
1037                         }
1038
1039                         modifiers[i] = pg_strdup(tmpbuf.data);
1040                         printTableAddCell(&cont, modifiers[i], false);
1041                 }
1042
1043                 /* Storage and Description */
1044                 if (verbose)
1045                 {
1046                         char *storage = PQgetvalue(res, i, 5);
1047                         /* these strings are literal in our syntax, so not translated. */
1048                         printTableAddCell(&cont, (storage[0]=='p' ? "plain" :
1049                                                                           (storage[0]=='m' ? "main" :
1050                                                                            (storage[0]=='x' ? "extended" :
1051                                                                                 (storage[0]=='e' ? "external" :
1052                                                                                  "???")))),
1053                                                           false);
1054                         printTableAddCell(&cont, PQgetvalue(res, i, 6), false);
1055                 }
1056         }
1057
1058         /* Make footers */
1059         if (tableinfo.relkind == 'i')
1060         {
1061                 /* Footer information about an index */
1062                 PGresult   *result;
1063
1064                 printfPQExpBuffer(&buf,
1065                                                   "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1066                 if (pset.sversion >= 80200)
1067                         appendPQExpBuffer(&buf, "i.indisvalid, ");
1068                 else
1069                         appendPQExpBuffer(&buf, "true as indisvalid, ");
1070                 appendPQExpBuffer(&buf, "a.amname, c2.relname,\n"
1071                                         "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1072                                                   "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1073                   "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1074                                                   "AND i.indrelid = c2.oid",
1075                                                   oid);
1076
1077                 result = PSQLexec(buf.data, false);
1078                 if (!result)
1079                         goto error_return;
1080                 else if (PQntuples(result) != 1)
1081                 {
1082                         PQclear(result);
1083                         goto error_return;
1084                 }
1085                 else
1086                 {
1087                         char       *indisunique = PQgetvalue(result, 0, 0);
1088                         char       *indisprimary = PQgetvalue(result, 0, 1);
1089                         char       *indisclustered = PQgetvalue(result, 0, 2);
1090                         char       *indisvalid = PQgetvalue(result, 0, 3);
1091                         char       *indamname = PQgetvalue(result, 0, 4);
1092                         char       *indtable = PQgetvalue(result, 0, 5);
1093                         char       *indpred = PQgetvalue(result, 0, 6);
1094
1095                         if (strcmp(indisprimary, "t") == 0)
1096                                 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1097                         else if (strcmp(indisunique, "t") == 0)
1098                                 printfPQExpBuffer(&tmpbuf, _("unique, "));
1099                         else
1100                                 resetPQExpBuffer(&tmpbuf);
1101                         appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1102
1103                         /* we assume here that index and table are in same schema */
1104                         appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1105                                                           schemaname, indtable);
1106
1107                         if (strlen(indpred))
1108                                 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1109
1110                         if (strcmp(indisclustered, "t") == 0)
1111                                 appendPQExpBuffer(&tmpbuf, _(", clustered"));
1112
1113                         if (strcmp(indisvalid, "t") != 0)
1114                                 appendPQExpBuffer(&tmpbuf, _(", invalid"));
1115
1116                         printTableAddFooter(&cont, tmpbuf.data);
1117                         add_tablespace_footer(&cont, tableinfo.relkind,
1118                                                                   tableinfo.tablespace, true);
1119                 }
1120
1121                 PQclear(result);
1122         }
1123         else if (view_def)
1124         {
1125                 PGresult   *result = NULL;
1126
1127                 /* Footer information about a view */
1128                 printTableAddFooter(&cont, _("View definition:"));
1129                 printTableAddFooter(&cont, view_def);
1130
1131                 /* print rules */
1132                 if (tableinfo.hasrules)
1133                 {
1134                         printfPQExpBuffer(&buf,
1135                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1136                                                           "FROM pg_catalog.pg_rewrite r\n"
1137                         "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1138                                                           oid);
1139                         result = PSQLexec(buf.data, false);
1140                         if (!result)
1141                                 goto error_return;
1142
1143                         if (PQntuples(result) > 0)
1144                         {
1145                                 printTableAddFooter(&cont, _("Rules:"));
1146                                 for (i = 0; i < PQntuples(result); i++)
1147                                 {
1148                                         const char *ruledef;
1149
1150                                         /* Everything after "CREATE RULE" is echoed verbatim */
1151                                         ruledef = PQgetvalue(result, i, 1);
1152                                         ruledef += 12;
1153
1154                                         printfPQExpBuffer(&buf, " %s", ruledef);
1155                                         printTableAddFooter(&cont, buf.data);
1156                                 }
1157                         }
1158                         PQclear(result);
1159                 }
1160         }
1161         else if (tableinfo.relkind == 'r')
1162         {
1163                 /* Footer information about a table */
1164                 PGresult   *result = NULL;
1165                 int                     tuples = 0;
1166
1167                 /* print indexes */
1168                 if (tableinfo.hasindex)
1169                 {
1170                         printfPQExpBuffer(&buf,
1171                                                           "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
1172             if (pset.sversion >= 80200)
1173                                 appendPQExpBuffer(&buf, "i.indisvalid, ");
1174             else
1175                                 appendPQExpBuffer(&buf, "true as indisvalid, ");
1176                         appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)");
1177             if (pset.sversion >= 80000)
1178                                 appendPQExpBuffer(&buf, ", c2.reltablespace");
1179                         appendPQExpBuffer(&buf,
1180                                                           "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
1181                                                           "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1182                           "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1183                                                           oid);
1184                         result = PSQLexec(buf.data, false);
1185                         if (!result)
1186                                 goto error_return;
1187                         else
1188                                 tuples = PQntuples(result);
1189
1190                         if (tuples > 0)
1191                         {
1192                                 printTableAddFooter(&cont, _("Indexes:"));
1193                                 for (i = 0; i < tuples; i++)
1194                                 {
1195                                         const char *indexdef;
1196                                         const char *usingpos;
1197
1198                                         /* untranslated index name */
1199                                         printfPQExpBuffer(&buf, "    \"%s\"",
1200                                                                           PQgetvalue(result, i, 0));
1201
1202                                         /* Label as primary key or unique (but not both) */
1203                                         appendPQExpBuffer(&buf,
1204                                                                           strcmp(PQgetvalue(result, i, 1), "t") == 0
1205                                                                           ? " PRIMARY KEY," :
1206                                                                           (strcmp(PQgetvalue(result, i, 2), "t") == 0
1207                                                                            ? " UNIQUE,"
1208                                                                            : ""));
1209                                         /* Everything after "USING" is echoed verbatim */
1210                                         indexdef = PQgetvalue(result, i, 5);
1211                                         usingpos = strstr(indexdef, " USING ");
1212                                         if (usingpos)
1213                                                 indexdef = usingpos + 7;
1214
1215                                         appendPQExpBuffer(&buf, " %s", indexdef);
1216
1217                                         if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
1218                                                 appendPQExpBuffer(&buf, " CLUSTER");
1219
1220                                         if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
1221                                                 appendPQExpBuffer(&buf, " INVALID");
1222
1223                                         printTableAddFooter(&cont, buf.data);
1224
1225                                         /* Print tablespace of the index on the same line */
1226                                         if (pset.sversion >= 80000)
1227                                             add_tablespace_footer(&cont, 'i',
1228                                                                                           atooid(PQgetvalue(result, i, 6)),
1229                                                                                           false);
1230                                 }
1231                         }
1232                         PQclear(result);
1233                 }
1234
1235                 /* print table (and column) check constraints */
1236                 if (tableinfo.checks)
1237                 {
1238                         printfPQExpBuffer(&buf,
1239                                                           "SELECT r.conname, "
1240                                                           "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1241                                                           "FROM pg_catalog.pg_constraint r\n"
1242                                         "WHERE r.conrelid = '%s' AND r.contype = 'c'\nORDER BY 1",
1243                                                           oid);
1244                         result = PSQLexec(buf.data, false);
1245                         if (!result)
1246                                 goto error_return;
1247                         else
1248                                 tuples = PQntuples(result);
1249
1250                         if (tuples > 0)
1251                         {
1252                                 printTableAddFooter(&cont, _("Check constraints:"));
1253                                 for (i = 0; i < tuples; i++)
1254                                 {
1255                                         /* untranslated contraint name and def */
1256                                         printfPQExpBuffer(&buf, "    \"%s\" %s",
1257                                                                           PQgetvalue(result, i, 0),
1258                                                                           PQgetvalue(result, i, 1));
1259
1260                                         printTableAddFooter(&cont, buf.data);
1261                                 }
1262                         }
1263                         PQclear(result);
1264                 }
1265
1266                 /* print foreign-key constraints (there are none if no triggers) */
1267                 if (tableinfo.triggers)
1268                 {
1269                         printfPQExpBuffer(&buf,
1270                                                           "SELECT conname,\n"
1271                                    "  pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
1272                                                           "FROM pg_catalog.pg_constraint r\n"
1273                                         "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1274                                                           oid);
1275                         result = PSQLexec(buf.data, false);
1276                         if (!result)
1277                                 goto error_return;
1278                         else
1279                                 tuples = PQntuples(result);
1280
1281                         if (tuples > 0)
1282                         {
1283                                 printTableAddFooter(&cont, _("Foreign-key constraints:"));
1284                                 for (i = 0; i < tuples; i++)
1285                                 {
1286                                         /* untranslated constraint name and def */
1287                                         printfPQExpBuffer(&buf, "    \"%s\" %s",
1288                                                                           PQgetvalue(result, i, 0),
1289                                                                           PQgetvalue(result, i, 1));
1290
1291                                         printTableAddFooter(&cont, buf.data);
1292                                 }
1293                         }
1294                         PQclear(result);
1295                 }
1296
1297                 /* print incoming foreign-key references (none if no triggers) */
1298                 if (tableinfo.triggers)
1299                 {
1300                         printfPQExpBuffer(&buf,
1301                                                           "SELECT conname, conrelid::pg_catalog.regclass,\n"
1302                                                           "  pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
1303                                                           "FROM pg_catalog.pg_constraint c\n"
1304                                                           "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1",
1305                                                           oid);
1306                         result = PSQLexec(buf.data, false);
1307                         if (!result)
1308                                 goto error_return;
1309                         else
1310                                 tuples = PQntuples(result);
1311
1312                         if (tuples > 0)
1313                         {
1314                                 printTableAddFooter(&cont, _("Referenced by:"));
1315                                 for (i = 0; i < tuples; i++)
1316                                 {
1317                                         /* translator: the first %s is a FK name, the following are
1318                                          * a table name and the FK definition */
1319                                         printfPQExpBuffer(&buf, _("  \"%s\" IN %s %s"),
1320                                                                           PQgetvalue(result, i, 0),
1321                                                                           PQgetvalue(result, i, 1),
1322                                                                           PQgetvalue(result, i, 2));
1323
1324                                         printTableAddFooter(&cont, buf.data);
1325                                 }
1326                         }
1327                         PQclear(result);
1328                 }
1329
1330                 /* print rules */
1331                 if (tableinfo.hasrules)
1332                 {
1333                         if (pset.sversion >= 80300)
1334                         {
1335                                 printfPQExpBuffer(&buf,
1336                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1337                                                                   "ev_enabled\n"
1338                                                                   "FROM pg_catalog.pg_rewrite r\n"
1339                                                                   "WHERE r.ev_class = '%s' ORDER BY 1",
1340                                                                   oid);
1341                         }
1342                         else
1343                         {
1344                                 printfPQExpBuffer(&buf,
1345                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1346                                                                   "'O'::char AS ev_enabled\n"
1347                                                                   "FROM pg_catalog.pg_rewrite r\n"
1348                                                                   "WHERE r.ev_class = '%s' ORDER BY 1",
1349                                                                   oid);
1350                         }
1351                         result = PSQLexec(buf.data, false);
1352                         if (!result)
1353                                 goto error_return;
1354                         else
1355                                 tuples = PQntuples(result);
1356
1357                         if (tuples > 0)
1358                         {
1359                                 bool            have_heading;
1360                                 int                     category;
1361
1362                                 for (category = 0; category < 4; category++)
1363                                 {
1364                                         have_heading = false;
1365
1366                                         for (i = 0; i < tuples; i++)
1367                                         {
1368                                                 const char *ruledef;
1369                                                 bool            list_rule = false;
1370
1371                                                 switch (category)
1372                                                 {
1373                                                         case 0:
1374                                                                 if (*PQgetvalue(result, i, 2) == 'O')
1375                                                                         list_rule = true;
1376                                                                 break;
1377                                                         case 1:
1378                                                                 if (*PQgetvalue(result, i, 2) == 'D')
1379                                                                         list_rule = true;
1380                                                                 break;
1381                                                         case 2:
1382                                                                 if (*PQgetvalue(result, i, 2) == 'A')
1383                                                                         list_rule = true;
1384                                                                 break;
1385                                                         case 3:
1386                                                                 if (*PQgetvalue(result, i, 2) == 'R')
1387                                                                         list_rule = true;
1388                                                                 break;
1389                                                 }
1390                                                 if (!list_rule)
1391                                                         continue;
1392
1393                                                 if (!have_heading)
1394                                                 {
1395                                                         switch (category)
1396                                                         {
1397                                                                 case 0:
1398                                                                         printfPQExpBuffer(&buf, _("Rules:"));
1399                                                                         break;
1400                                                                 case 1:
1401                                                                         printfPQExpBuffer(&buf, _("Disabled rules:"));
1402                                                                         break;
1403                                                                 case 2:
1404                                                                         printfPQExpBuffer(&buf, _("Rules firing always:"));
1405                                                                         break;
1406                                                                 case 3:
1407                                                                         printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
1408                                                                         break;
1409                                                         }
1410                                                         printTableAddFooter(&cont, buf.data);
1411                                                         have_heading = true;
1412                                                 }
1413
1414                                                 /* Everything after "CREATE RULE" is echoed verbatim */
1415                                                 ruledef = PQgetvalue(result, i, 1);
1416                                                 ruledef += 12;
1417                                                 printfPQExpBuffer(&buf, "    %s", ruledef);
1418                                                 printTableAddFooter(&cont, buf.data);
1419                                         }
1420                                 }
1421                         }
1422                         PQclear(result);
1423                 }
1424
1425                 /* print triggers (but ignore foreign-key triggers) */
1426                 if (tableinfo.triggers)
1427                 {
1428                         printfPQExpBuffer(&buf,
1429                                                           "SELECT t.tgname, "
1430                                                           "pg_catalog.pg_get_triggerdef(t.oid), "
1431                                                           "t.tgenabled\n"
1432                                                           "FROM pg_catalog.pg_trigger t\n"
1433                                                           "WHERE t.tgrelid = '%s' AND ",
1434                                                           oid);
1435                         if (pset.sversion >= 80300)
1436                                 appendPQExpBuffer(&buf, "t.tgconstraint = 0");
1437                         else
1438                                 appendPQExpBuffer(&buf,
1439                                                                   "(NOT tgisconstraint "
1440                                                                   " OR NOT EXISTS"
1441                                                                   "  (SELECT 1 FROM pg_catalog.pg_depend d "
1442                                                                   "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
1443                                                                   "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
1444                         appendPQExpBuffer(&buf, "\nORDER BY 1");
1445
1446                         result = PSQLexec(buf.data, false);
1447                         if (!result)
1448                                 goto error_return;
1449                         else
1450                                 tuples = PQntuples(result);
1451
1452                         if (tuples > 0)
1453                         {
1454                                 bool            have_heading;
1455                                 int                     category;
1456
1457                                 /*
1458                                  * split the output into 4 different categories. Enabled triggers,
1459                                  * disabled triggers and the two special ALWAYS and REPLICA
1460                                  * configurations.
1461                                  */
1462                                 for (category = 0; category < 4; category++)
1463                                 {
1464                                         have_heading = false;
1465                                         for (i = 0; i < tuples; i++)
1466                                         {
1467                                                 bool            list_trigger;
1468                                                 const char *tgdef;
1469                                                 const char *usingpos;
1470                                                 const char *tgenabled;
1471
1472                                                 /* Check if this trigger falls into the current category */
1473                                                 tgenabled = PQgetvalue(result, i, 2);
1474                                                 list_trigger = false;
1475                                                 switch (category)
1476                                                 {
1477                                                         case 0:
1478                                                                 if (*tgenabled == 'O' || *tgenabled == 't')
1479                                                                         list_trigger = true;
1480                                                                 break;
1481                                                         case 1:
1482                                                                 if (*tgenabled == 'D' || *tgenabled == 'f')
1483                                                                         list_trigger = true;
1484                                                                 break;
1485                                                         case 2:
1486                                                                 if (*tgenabled == 'A')
1487                                                                         list_trigger = true;
1488                                                                 break;
1489                                                         case 3:
1490                                                                 if (*tgenabled == 'R')
1491                                                                         list_trigger = true;
1492                                                                 break;
1493                                                 }
1494                                                 if (list_trigger == false)
1495                                                         continue;
1496
1497                                                 /* Print the category heading once */
1498                                                 if (have_heading == false)
1499                                                 {
1500                                                         switch (category)
1501                                                         {
1502                                                                 case 0:
1503                                                                         printfPQExpBuffer(&buf, _("Triggers:"));
1504                                                                         break;
1505                                                                 case 1:
1506                                                                         printfPQExpBuffer(&buf, _("Disabled triggers:"));
1507                                                                         break;
1508                                                                 case 2:
1509                                                                         printfPQExpBuffer(&buf, _("Triggers firing always:"));
1510                                                                         break;
1511                                                                 case 3:
1512                                                                         printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
1513                                                                         break;
1514
1515                                                         }
1516                                                         printTableAddFooter(&cont, buf.data);
1517                                                         have_heading = true;
1518                                                 }
1519
1520                                                 /* Everything after "TRIGGER" is echoed verbatim */
1521                                                 tgdef = PQgetvalue(result, i, 1);
1522                                                 usingpos = strstr(tgdef, " TRIGGER ");
1523                                                 if (usingpos)
1524                                                         tgdef = usingpos + 9;
1525
1526                                                 printfPQExpBuffer(&buf, "    %s", tgdef);
1527                                                 printTableAddFooter(&cont, buf.data);
1528                                         }
1529                                 }
1530                         }
1531                         PQclear(result);
1532                 }
1533
1534                 /* print inherited tables */
1535                 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);
1536
1537                 result = PSQLexec(buf.data, false);
1538                 if (!result)
1539                         goto error_return;
1540                 else
1541                         tuples = PQntuples(result);
1542
1543                 for (i = 0; i < tuples; i++)
1544                 {
1545                         const char *s = _("Inherits");
1546
1547                         if (i == 0)
1548                                 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0));
1549                         else
1550                                 printfPQExpBuffer(&buf, "%*s  %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
1551                         if (i < tuples - 1)
1552                                 appendPQExpBuffer(&buf, ",");
1553
1554                         printTableAddFooter(&cont, buf.data);
1555                 }
1556                 PQclear(result);
1557
1558                 if (verbose)
1559                 {
1560                         const char *s = _("Has OIDs");
1561
1562                         printfPQExpBuffer(&buf, "%s: %s", s,
1563                                                           (tableinfo.hasoids ? _("yes") : _("no")));
1564                         printTableAddFooter(&cont, buf.data);
1565                 }
1566
1567                 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
1568                                                           true);
1569         }
1570
1571         printTable(&cont, pset.queryFout, pset.logfile);
1572         printTableCleanup(&cont);
1573
1574         retval = true;
1575
1576 error_return:
1577
1578         /* clean up */
1579         printTableCleanup(&cont);
1580         termPQExpBuffer(&buf);
1581         termPQExpBuffer(&title);
1582         termPQExpBuffer(&tmpbuf);
1583
1584         if (tableinfo.relkind == 'S')
1585         {
1586                 for (ptr = seq_values; *ptr; ptr++)
1587                         free(*ptr);
1588                 free(seq_values);
1589         }
1590
1591         if (show_modifiers)
1592         {
1593                 for (ptr = modifiers; *ptr; ptr++)
1594                         free(*ptr);
1595                 free(modifiers);
1596         }
1597
1598         if (view_def)
1599                 free(view_def);
1600
1601         if (res)
1602                 PQclear(res);
1603
1604         return retval;
1605 }
1606
1607 /*
1608  * Add a tablespace description to a footer.  If 'newline' is true, it is added
1609  * in a new line; otherwise it's appended to the current value of the last
1610  * footer.
1611  */
1612 static void
1613 add_tablespace_footer(printTableContent *const cont, char relkind,
1614                                           Oid tablespace, const bool newline)
1615 {
1616         /* relkinds for which we support tablespaces */
1617         if (relkind == 'r' || relkind == 'i')
1618         {
1619                 /*
1620                  * We ignore the database default tablespace so that users not using
1621                  * tablespaces don't need to know about them.  This case also covers
1622                  * pre-8.0 servers, for which tablespace will always be 0.
1623                  */
1624                 if (tablespace != 0)
1625                 {
1626                         PGresult   *result = NULL;
1627                         PQExpBufferData buf;
1628
1629                         initPQExpBuffer(&buf);
1630                         printfPQExpBuffer(&buf,
1631                                                           "SELECT spcname FROM pg_catalog.pg_tablespace\n"
1632                                                           "WHERE oid = '%u'", tablespace);
1633                         result = PSQLexec(buf.data, false);
1634                         if (!result)
1635                                 return;
1636                         /* Should always be the case, but.... */
1637                         if (PQntuples(result) > 0)
1638                         {
1639                                 if (newline)
1640                                 {
1641                                         /* Add the tablespace as a new footer */
1642                                         printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
1643                                                                           PQgetvalue(result, 0, 0));
1644                                         printTableAddFooter(cont, buf.data);
1645                                 }
1646                                 else
1647                                 {
1648                                         /* Append the tablespace to the latest footer */
1649                                         printfPQExpBuffer(&buf, "%s", cont->footer->data);
1650                                         /* translator: before this string there's an index
1651                                          * description like '"foo_pkey" PRIMARY KEY, btree (a)' */
1652                                         appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
1653                                                                           PQgetvalue(result, 0, 0));
1654                                         printTableSetFooter(cont, buf.data);
1655                                 }
1656                         }
1657                         PQclear(result);
1658                         termPQExpBuffer(&buf);
1659                 }
1660         }
1661 }
1662
1663 /*
1664  * \du or \dg
1665  *
1666  * Describes roles.  Any schema portion of the pattern is ignored.
1667  */
1668 bool
1669 describeRoles(const char *pattern, bool verbose)
1670 {
1671         PQExpBufferData buf;
1672         PGresult   *res;
1673         printTableContent cont;
1674         printTableOpt myopt = pset.popt.topt;
1675         int                     ncols = 3;
1676         int                     nrows = 0;
1677         int                     i;
1678         int                     conns;
1679         const char      align = 'l';
1680         char      **attr;
1681
1682         initPQExpBuffer(&buf);
1683
1684         if (pset.sversion >= 80100)
1685         {
1686                 printfPQExpBuffer(&buf,
1687                                              "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
1688                                                  "  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
1689                                                  "  r.rolconnlimit,\n"
1690                                              "  ARRAY(SELECT b.rolname\n"
1691                                              "        FROM pg_catalog.pg_auth_members m\n"
1692                                              "        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
1693                                              "        WHERE m.member = r.oid) as memberof");
1694
1695                 if (verbose && pset.sversion >= 80200)
1696                 {
1697                         appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
1698                         ncols++;
1699                 }
1700
1701                 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
1702
1703                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1704                                                           NULL, "r.rolname", NULL, NULL);
1705     }
1706     else
1707     {
1708             printfPQExpBuffer(&buf,
1709                                           "SELECT u.usename AS rolname,\n"
1710                                           "  u.usesuper AS rolsuper,\n"
1711                                           "  true AS rolinherit, false AS rolcreaterole,\n"
1712                                           "  u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
1713                                           "  -1 AS rolconnlimit,\n"
1714                                           "  ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
1715                                           "\nFROM pg_catalog.pg_user u\n");
1716
1717                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1718                                                           NULL, "u.usename", NULL, NULL);
1719     }
1720
1721         appendPQExpBuffer(&buf, "ORDER BY 1;");
1722
1723         res = PSQLexec(buf.data, false);
1724         if (!res)
1725                 return false;
1726
1727         nrows = PQntuples(res);
1728         attr = pg_malloc_zero((nrows + 1) * sizeof(*attr));
1729
1730         printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
1731
1732         printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
1733         printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
1734         printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
1735
1736         if (verbose && pset.sversion >= 80200)
1737                 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
1738
1739         for (i = 0; i < nrows; i++)
1740         {
1741                 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1742
1743                 resetPQExpBuffer(&buf);
1744                 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
1745                         add_role_attribute(&buf, _("Superuser"));
1746
1747                 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
1748                         add_role_attribute(&buf, _("No inheritance"));
1749
1750                 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1751                         add_role_attribute(&buf, _("Create role"));
1752
1753                 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
1754                         add_role_attribute(&buf, _("Create DB"));
1755
1756                 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
1757                         add_role_attribute(&buf, _("Cannot login"));
1758
1759                 conns = atoi(PQgetvalue(res, i, 6));
1760                 if (conns >= 0)
1761                 {
1762                         if (buf.len > 0)
1763                                 appendPQExpBufferStr(&buf, "\n");
1764
1765                         if (conns == 0)
1766                                 appendPQExpBuffer(&buf, _("No connections"));
1767                         else if (conns == 1)
1768                                 appendPQExpBuffer(&buf, _("1 connection"));
1769                         else
1770                                 appendPQExpBuffer(&buf, _("%d connections"), conns);
1771                 }
1772
1773                 attr[i] = pg_strdup(buf.data);
1774
1775                 printTableAddCell(&cont, attr[i], false);
1776
1777                 printTableAddCell(&cont, PQgetvalue(res, i, 7), false);
1778
1779                 if (verbose && pset.sversion >= 80200)
1780                         printTableAddCell(&cont, PQgetvalue(res, i, 8), false);
1781         }
1782         termPQExpBuffer(&buf);
1783
1784         printTable(&cont, pset.queryFout, pset.logfile);
1785         printTableCleanup(&cont);
1786
1787         for (i = 0; i < nrows; i++)
1788                 free(attr[i]);
1789         free(attr);
1790
1791         PQclear(res);
1792         return true;
1793 }
1794
1795 void
1796 add_role_attribute(PQExpBuffer buf, const char *const str)
1797 {
1798         if (buf->len > 0)
1799                 appendPQExpBufferStr(buf, "\n");
1800
1801         appendPQExpBufferStr(buf, str);
1802 }
1803
1804
1805 /*
1806  * listTables()
1807  *
1808  * handler for \d, \dt, etc.
1809  *
1810  * tabtypes is an array of characters, specifying what info is desired:
1811  * t - tables
1812  * i - indexes
1813  * v - views
1814  * s - sequences
1815  * S - system tables (pg_catalog)
1816  * (any order of the above is fine)
1817  */
1818 bool
1819 listTables(const char *tabtypes, const char *pattern, bool verbose)
1820 {
1821         bool            showTables = strchr(tabtypes, 't') != NULL;
1822         bool            showIndexes = strchr(tabtypes, 'i') != NULL;
1823         bool            showViews = strchr(tabtypes, 'v') != NULL;
1824         bool            showSeq = strchr(tabtypes, 's') != NULL;
1825         bool            showSystem = strchr(tabtypes, 'S') != NULL;
1826
1827         PQExpBufferData buf;
1828         PGresult   *res;
1829         printQueryOpt myopt = pset.popt;
1830         static const bool translate_columns[] = {false, false, true, false, false, false};
1831
1832         if (!(showTables || showIndexes || showViews || showSeq))
1833                 showTables = showViews = showSeq = true;
1834
1835         initPQExpBuffer(&buf);
1836
1837         /*
1838          * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
1839          * for backwards compatibility.
1840          */
1841         printfPQExpBuffer(&buf,
1842                                           "SELECT n.nspname as \"%s\",\n"
1843                                           "  c.relname as \"%s\",\n"
1844                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' END as \"%s\",\n"
1845                                           "  pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
1846                                           gettext_noop("Schema"),
1847                                           gettext_noop("Name"),
1848                                           gettext_noop("table"),
1849                                           gettext_noop("view"),
1850                                           gettext_noop("index"),
1851                                           gettext_noop("sequence"),
1852                                           gettext_noop("special"),
1853                                           gettext_noop("Type"),
1854                                           gettext_noop("Owner"));
1855
1856         if (showIndexes)
1857                 appendPQExpBuffer(&buf,
1858                                                   ",\n c2.relname as \"%s\"",
1859                                                   gettext_noop("Table"));
1860
1861         if (verbose && pset.sversion >= 80100)
1862                 appendPQExpBuffer(&buf,
1863                                                   ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
1864                                                   gettext_noop("Size"));
1865         if (verbose)
1866                 appendPQExpBuffer(&buf,
1867                           ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
1868                                                   gettext_noop("Description"));
1869
1870         appendPQExpBuffer(&buf,
1871                                           "\nFROM pg_catalog.pg_class c"
1872          "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
1873         if (showIndexes)
1874                 appendPQExpBuffer(&buf,
1875                          "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
1876                    "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
1877
1878         appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
1879         if (showTables)
1880                 appendPQExpBuffer(&buf, "'r',");
1881         if (showViews)
1882                 appendPQExpBuffer(&buf, "'v',");
1883         if (showIndexes)
1884                 appendPQExpBuffer(&buf, "'i',");
1885         if (showSeq)
1886                 appendPQExpBuffer(&buf, "'S',");
1887         if (showSystem && showTables)
1888                 appendPQExpBuffer(&buf, "'s',");
1889         appendPQExpBuffer(&buf, "''");          /* dummy */
1890         appendPQExpBuffer(&buf, ")\n");
1891
1892         /*
1893          * If showSystem is specified, show only system objects (those in
1894          * pg_catalog).  Otherwise, suppress system objects, including those in
1895          * pg_catalog and pg_toast.  (We don't want to hide temp tables though.)
1896          */
1897         if (showSystem)
1898                 appendPQExpBuffer(&buf,
1899                                                   "  AND n.nspname = 'pg_catalog'\n");
1900         else
1901                 appendPQExpBuffer(&buf,
1902                                                   "  AND n.nspname <> 'pg_catalog'\n"
1903                                                   "  AND n.nspname !~ '^pg_toast'\n");
1904
1905         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1906                                                   "n.nspname", "c.relname", NULL,
1907                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1908
1909         appendPQExpBuffer(&buf, "ORDER BY 1,2;");
1910
1911         res = PSQLexec(buf.data, false);
1912         termPQExpBuffer(&buf);
1913         if (!res)
1914                 return false;
1915
1916         if (PQntuples(res) == 0 && !pset.quiet)
1917         {
1918                 if (pattern)
1919                         fprintf(pset.queryFout, _("No matching relations found.\n"));
1920                 else
1921                         fprintf(pset.queryFout, _("No relations found.\n"));
1922         }
1923         else
1924         {
1925                 myopt.nullPrint = NULL;
1926                 myopt.title = _("List of relations");
1927                 myopt.translate_header = true;
1928                 myopt.translate_columns = translate_columns;
1929
1930                 printQuery(res, &myopt, pset.queryFout, pset.logfile);
1931         }
1932
1933         PQclear(res);
1934         return true;
1935 }
1936
1937
1938 /*
1939  * \dD
1940  *
1941  * Describes domains.
1942  */
1943 bool
1944 listDomains(const char *pattern)
1945 {
1946         PQExpBufferData buf;
1947         PGresult   *res;
1948         printQueryOpt myopt = pset.popt;
1949
1950         initPQExpBuffer(&buf);
1951
1952         printfPQExpBuffer(&buf,
1953                                           "SELECT n.nspname as \"%s\",\n"
1954                                           "       t.typname as \"%s\",\n"
1955          "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
1956                                           "       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
1957         "            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
1958                                           "            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
1959                                           "            ELSE ''\n"
1960                                           "       END as \"%s\",\n"
1961                         "       pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
1962                                           "FROM pg_catalog.pg_type t\n"
1963            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
1964                   "     LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
1965                                           "WHERE t.typtype = 'd'\n",
1966                                           gettext_noop("Schema"),
1967                                           gettext_noop("Name"),
1968                                           gettext_noop("Type"),
1969                                           gettext_noop("Modifier"),
1970                                           gettext_noop("Check"));
1971
1972         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1973                                                   "n.nspname", "t.typname", NULL,
1974                                                   "pg_catalog.pg_type_is_visible(t.oid)");
1975
1976         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1977
1978         res = PSQLexec(buf.data, false);
1979         termPQExpBuffer(&buf);
1980         if (!res)
1981                 return false;
1982
1983         myopt.nullPrint = NULL;
1984         myopt.title = _("List of domains");
1985         myopt.translate_header = true;
1986
1987         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1988
1989         PQclear(res);
1990         return true;
1991 }
1992
1993 /*
1994  * \dc
1995  *
1996  * Describes conversions.
1997  */
1998 bool
1999 listConversions(const char *pattern)
2000 {
2001         PQExpBufferData buf;
2002         PGresult   *res;
2003         printQueryOpt myopt = pset.popt;
2004         static const bool translate_columns[] = {false, false, false, false, true};
2005
2006         initPQExpBuffer(&buf);
2007
2008         printfPQExpBuffer(&buf,
2009                                           "SELECT n.nspname AS \"%s\",\n"
2010                                           "       c.conname AS \"%s\",\n"
2011            "       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
2012                 "       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
2013                                           "       CASE WHEN c.condefault THEN '%s'\n"
2014                                           "       ELSE '%s' END AS \"%s\"\n"
2015                            "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
2016                                           "WHERE n.oid = c.connamespace\n",
2017                                           gettext_noop("Schema"),
2018                                           gettext_noop("Name"),
2019                                           gettext_noop("Source"),
2020                                           gettext_noop("Destination"),
2021                                           gettext_noop("yes"), gettext_noop("no"),
2022                                           gettext_noop("Default?"));
2023
2024         processSQLNamePattern(pset.db, &buf, pattern, true, false,
2025                                                   "n.nspname", "c.conname", NULL,
2026                                                   "pg_catalog.pg_conversion_is_visible(c.oid)");
2027
2028         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2029
2030         res = PSQLexec(buf.data, false);
2031         termPQExpBuffer(&buf);
2032         if (!res)
2033                 return false;
2034
2035         myopt.nullPrint = NULL;
2036         myopt.title = _("List of conversions");
2037         myopt.translate_header = true;
2038         myopt.translate_columns = translate_columns;
2039
2040         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2041
2042         PQclear(res);
2043         return true;
2044 }
2045
2046 /*
2047  * \dC
2048  *
2049  * Describes casts.
2050  */
2051 bool
2052 listCasts(const char *pattern)
2053 {
2054         PQExpBufferData buf;
2055         PGresult   *res;
2056         printQueryOpt myopt = pset.popt;
2057         static const bool translate_columns[] = {false, false, false, true};
2058
2059         initPQExpBuffer(&buf);
2060         /*
2061          * We need left join here for binary casts.  Also note that we don't
2062          * attempt to localize '(binary coercible)', because there's too much
2063          * risk of gettext translating a function name that happens to match
2064          * some string in the PO database.
2065          */
2066         printfPQExpBuffer(&buf,
2067                            "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
2068                            "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
2069                                           "       CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
2070                                           "            ELSE p.proname\n"
2071                                           "       END as \"%s\",\n"
2072                                           "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
2073                                           "            WHEN c.castcontext = 'a' THEN '%s'\n"
2074                                           "            ELSE '%s'\n"
2075                                           "       END as \"%s\"\n"
2076                                  "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
2077                                           "     ON c.castfunc = p.oid\n"
2078                                           "ORDER BY 1, 2",
2079                                           gettext_noop("Source type"),
2080                                           gettext_noop("Target type"),
2081                                           gettext_noop("Function"),
2082                                           gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
2083                                           gettext_noop("Implicit?"));
2084
2085         res = PSQLexec(buf.data, false);
2086         termPQExpBuffer(&buf);
2087         if (!res)
2088                 return false;
2089
2090         myopt.nullPrint = NULL;
2091         myopt.title = _("List of casts");
2092         myopt.translate_header = true;
2093         myopt.translate_columns = translate_columns;
2094
2095         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2096
2097         PQclear(res);
2098         return true;
2099 }
2100
2101 /*
2102  * \dn
2103  *
2104  * Describes schemas (namespaces)
2105  */
2106 bool
2107 listSchemas(const char *pattern, bool verbose)
2108 {
2109         PQExpBufferData buf;
2110         PGresult   *res;
2111         printQueryOpt myopt = pset.popt;
2112
2113         initPQExpBuffer(&buf);
2114         printfPQExpBuffer(&buf,
2115                                           "SELECT n.nspname AS \"%s\",\n"
2116                                           "  pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
2117                                           gettext_noop("Name"),
2118                                           gettext_noop("Owner"));
2119
2120         if (verbose)
2121                 appendPQExpBuffer(&buf,
2122                                                   ",\n  n.nspacl as \"%s\","
2123                          "  pg_catalog.obj_description(n.oid, 'pg_namespace') as \"%s\"",
2124                                                   gettext_noop("Access privileges"),
2125                                                   gettext_noop("Description"));
2126
2127         appendPQExpBuffer(&buf,
2128                           "\nFROM pg_catalog.pg_namespace n\n"
2129                                           "WHERE        (n.nspname !~ '^pg_temp_' OR\n"
2130                    "             n.nspname = (pg_catalog.current_schemas(true))[1])\n");                /* temp schema is first */
2131
2132         processSQLNamePattern(pset.db, &buf, pattern, true, false,
2133                                                   NULL, "n.nspname", NULL,
2134                                                   NULL);
2135
2136         appendPQExpBuffer(&buf, "ORDER BY 1;");
2137
2138         res = PSQLexec(buf.data, false);
2139         termPQExpBuffer(&buf);
2140         if (!res)
2141                 return false;
2142
2143         myopt.nullPrint = NULL;
2144         myopt.title = _("List of schemas");
2145         myopt.translate_header = true;
2146
2147         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2148
2149         PQclear(res);
2150         return true;
2151 }
2152
2153
2154 /*
2155  * \dFp
2156  * list text search parsers
2157  */
2158 bool
2159 listTSParsers(const char *pattern, bool verbose)
2160 {
2161         PQExpBufferData buf;
2162         PGresult   *res;
2163         printQueryOpt myopt = pset.popt;
2164
2165         if (pset.sversion < 80300)
2166         {
2167                 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2168                                 pset.sversion / 10000, (pset.sversion / 100) % 100);
2169                 return true;
2170         }
2171
2172         if (verbose)
2173                 return listTSParsersVerbose(pattern);
2174
2175         initPQExpBuffer(&buf);
2176
2177         printfPQExpBuffer(&buf,
2178                                           "SELECT \n"
2179                                           "  n.nspname as \"%s\",\n"
2180                                           "  p.prsname as \"%s\",\n"
2181                         "  pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
2182                                           "FROM pg_catalog.pg_ts_parser p \n"
2183                    "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
2184                                           gettext_noop("Schema"),
2185                                           gettext_noop("Name"),
2186                                           gettext_noop("Description")
2187                 );
2188
2189         processSQLNamePattern(pset.db, &buf, pattern, false, false,
2190                                                   "n.nspname", "p.prsname", NULL,
2191                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2192
2193         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2194
2195         res = PSQLexec(buf.data, false);
2196         termPQExpBuffer(&buf);
2197         if (!res)
2198                 return false;
2199
2200         myopt.nullPrint = NULL;
2201         myopt.title = _("List of text search parsers");
2202         myopt.translate_header = true;
2203
2204         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2205
2206         PQclear(res);
2207         return true;
2208 }
2209
2210 /*
2211  * full description of parsers
2212  */
2213 static bool
2214 listTSParsersVerbose(const char *pattern)
2215 {
2216         PQExpBufferData buf;
2217         PGresult   *res;
2218         int                     i;
2219
2220         initPQExpBuffer(&buf);
2221
2222         printfPQExpBuffer(&buf,
2223                                           "SELECT p.oid, \n"
2224                                           "  n.nspname, \n"
2225                                           "  p.prsname \n"
2226                                           "FROM pg_catalog.pg_ts_parser p\n"
2227                         "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
2228                 );
2229
2230         processSQLNamePattern(pset.db, &buf, pattern, false, false,
2231                                                   "n.nspname", "p.prsname", NULL,
2232                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2233
2234         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2235
2236         res = PSQLexec(buf.data, false);
2237         termPQExpBuffer(&buf);
2238         if (!res)
2239                 return false;
2240
2241         if (PQntuples(res) == 0)
2242         {
2243                 if (!pset.quiet)
2244                         fprintf(stderr, _("Did not find any text search parser named \"%s\".\n"),
2245                                         pattern);
2246                 PQclear(res);
2247                 return false;
2248         }
2249
2250         for (i = 0; i < PQntuples(res); i++)
2251         {
2252                 const char *oid;
2253                 const char *nspname = NULL;
2254                 const char *prsname;
2255
2256                 oid = PQgetvalue(res, i, 0);
2257                 if (!PQgetisnull(res, i, 1))
2258                         nspname = PQgetvalue(res, i, 1);
2259                 prsname = PQgetvalue(res, i, 2);
2260
2261                 if (!describeOneTSParser(oid, nspname, prsname))
2262                 {
2263                         PQclear(res);
2264                         return false;
2265                 }
2266
2267                 if (cancel_pressed)
2268                 {
2269                         PQclear(res);
2270                         return false;
2271                 }
2272         }
2273
2274         PQclear(res);
2275         return true;
2276 }
2277
2278 static bool
2279 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
2280 {
2281         PQExpBufferData buf;
2282         PGresult   *res;
2283         char            title[1024];
2284         printQueryOpt myopt = pset.popt;
2285         static const bool translate_columns[] = {true, false, false};
2286
2287         initPQExpBuffer(&buf);
2288
2289         printfPQExpBuffer(&buf,
2290                                           "SELECT '%s' AS \"%s\", \n"
2291                                           "   p.prsstart::pg_catalog.regproc AS \"%s\", \n"
2292                   "   pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
2293                                           " FROM pg_catalog.pg_ts_parser p \n"
2294                                           " WHERE p.oid = '%s' \n"
2295                                           "UNION ALL \n"
2296                                           "SELECT '%s', \n"
2297                                           "   p.prstoken::pg_catalog.regproc, \n"
2298                                         "   pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
2299                                           " FROM pg_catalog.pg_ts_parser p \n"
2300                                           " WHERE p.oid = '%s' \n"
2301                                           "UNION ALL \n"
2302                                           "SELECT '%s', \n"
2303                                           "   p.prsend::pg_catalog.regproc, \n"
2304                                           "   pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
2305                                           " FROM pg_catalog.pg_ts_parser p \n"
2306                                           " WHERE p.oid = '%s' \n"
2307                                           "UNION ALL \n"
2308                                           "SELECT '%s', \n"
2309                                           "   p.prsheadline::pg_catalog.regproc, \n"
2310                                  "   pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
2311                                           " FROM pg_catalog.pg_ts_parser p \n"
2312                                           " WHERE p.oid = '%s' \n"
2313                                           "UNION ALL \n"
2314                                           "SELECT '%s', \n"
2315                                           "   p.prslextype::pg_catalog.regproc, \n"
2316                                   "   pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
2317                                           " FROM pg_catalog.pg_ts_parser p \n"
2318                                           " WHERE p.oid = '%s' \n",
2319                                           gettext_noop("Start parse"),
2320                                           gettext_noop("Method"),
2321                                           gettext_noop("Function"),
2322                                           gettext_noop("Description"),
2323                                           oid,
2324                                           gettext_noop("Get next token"),
2325                                           oid,
2326                                           gettext_noop("End parse"),
2327                                           oid,
2328                                           gettext_noop("Get headline"),
2329                                           oid,
2330                                           gettext_noop("Get token types"),
2331                                           oid);
2332
2333         res = PSQLexec(buf.data, false);
2334         termPQExpBuffer(&buf);
2335         if (!res)
2336                 return false;
2337
2338         myopt.nullPrint = NULL;
2339         if (nspname)
2340                 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
2341         else
2342                 sprintf(title, _("Text search parser \"%s\""), prsname);
2343         myopt.title = title;
2344         myopt.footers = NULL;
2345         myopt.default_footer = false;
2346         myopt.translate_header = true;
2347         myopt.translate_columns = translate_columns;
2348
2349         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2350
2351         PQclear(res);
2352
2353         initPQExpBuffer(&buf);
2354
2355         printfPQExpBuffer(&buf,
2356                                           "SELECT t.alias as \"%s\", \n"
2357                                           "  t.description as \"%s\" \n"
2358                           "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
2359                                           "ORDER BY 1;",
2360                                           gettext_noop("Token name"),
2361                                           gettext_noop("Description"),
2362                                           oid);
2363
2364         res = PSQLexec(buf.data, false);
2365         termPQExpBuffer(&buf);
2366         if (!res)
2367                 return false;
2368
2369         myopt.nullPrint = NULL;
2370         if (nspname)
2371                 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
2372         else
2373                 sprintf(title, _("Token types for parser \"%s\""), prsname);
2374         myopt.title = title;
2375         myopt.footers = NULL;
2376         myopt.default_footer = true;
2377         myopt.translate_header = true;
2378         myopt.translate_columns = NULL;
2379
2380         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2381
2382         PQclear(res);
2383         return true;
2384 }
2385
2386
2387 /*
2388  * \dFd
2389  * list text search dictionaries
2390  */
2391 bool
2392 listTSDictionaries(const char *pattern, bool verbose)
2393 {
2394         PQExpBufferData buf;
2395         PGresult   *res;
2396         printQueryOpt myopt = pset.popt;
2397
2398         if (pset.sversion < 80300)
2399         {
2400                 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2401                                 pset.sversion / 10000, (pset.sversion / 100) % 100);
2402                 return true;
2403         }
2404
2405         initPQExpBuffer(&buf);
2406
2407         printfPQExpBuffer(&buf,
2408                                           "SELECT \n"
2409                                           "  n.nspname as \"%s\",\n"
2410                                           "  d.dictname as \"%s\",\n",
2411                                           gettext_noop("Schema"),
2412                                           gettext_noop("Name"));
2413
2414         if (verbose)
2415         {
2416                 appendPQExpBuffer(&buf,
2417                                                   "  ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
2418                                                   "    pg_catalog.pg_ts_template t \n"
2419                                                   "                      LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
2420                                                   "                      WHERE d.dicttemplate = t.oid ) AS  \"%s\", \n"
2421                                                   "  d.dictinitoption as \"%s\", \n",
2422                                                   gettext_noop("Template"),
2423                                                   gettext_noop("Init options"));
2424         }
2425
2426         appendPQExpBuffer(&buf,
2427                          "  pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
2428                                           gettext_noop("Description"));
2429
2430         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_dict d\n"
2431                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
2432
2433         processSQLNamePattern(pset.db, &buf, pattern, false, false,
2434                                                   "n.nspname", "d.dictname", NULL,
2435                                                   "pg_catalog.pg_ts_dict_is_visible(d.oid)");
2436
2437         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2438
2439         res = PSQLexec(buf.data, false);
2440         termPQExpBuffer(&buf);
2441         if (!res)
2442                 return false;
2443
2444         myopt.nullPrint = NULL;
2445         myopt.title = _("List of text search dictionaries");
2446         myopt.translate_header = true;
2447
2448         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2449
2450         PQclear(res);
2451         return true;
2452 }
2453
2454
2455 /*
2456  * \dFt
2457  * list text search templates
2458  */
2459 bool
2460 listTSTemplates(const char *pattern, bool verbose)
2461 {
2462         PQExpBufferData buf;
2463         PGresult   *res;
2464         printQueryOpt myopt = pset.popt;
2465
2466         if (pset.sversion < 80300)
2467         {
2468                 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2469                                 pset.sversion / 10000, (pset.sversion / 100) % 100);
2470                 return true;
2471         }
2472
2473         initPQExpBuffer(&buf);
2474
2475         if (verbose)
2476                 printfPQExpBuffer(&buf,
2477                                                   "SELECT \n"
2478                                                   "  n.nspname AS \"%s\",\n"
2479                                                   "  t.tmplname AS \"%s\",\n"
2480                                                   "  t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
2481                                                   "  t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
2482                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2483                                                   gettext_noop("Schema"),
2484                                                   gettext_noop("Name"),
2485                                                   gettext_noop("Init"),
2486                                                   gettext_noop("Lexize"),
2487                                                   gettext_noop("Description"));
2488         else
2489                 printfPQExpBuffer(&buf,
2490                                                   "SELECT \n"
2491                                                   "  n.nspname AS \"%s\",\n"
2492                                                   "  t.tmplname AS \"%s\",\n"
2493                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2494                                                   gettext_noop("Schema"),
2495                                                   gettext_noop("Name"),
2496                                                   gettext_noop("Description"));
2497
2498         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_template t\n"
2499                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
2500
2501         processSQLNamePattern(pset.db, &buf, pattern, false, false,
2502                                                   "n.nspname", "t.tmplname", NULL,
2503                                                   "pg_catalog.pg_ts_template_is_visible(t.oid)");
2504
2505         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2506
2507         res = PSQLexec(buf.data, false);
2508         termPQExpBuffer(&buf);
2509         if (!res)
2510                 return false;
2511
2512         myopt.nullPrint = NULL;
2513         myopt.title = _("List of text search templates");
2514         myopt.translate_header = true;
2515
2516         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2517
2518         PQclear(res);
2519         return true;
2520 }
2521
2522
2523 /*
2524  * \dF
2525  * list text search configurations
2526  */
2527 bool
2528 listTSConfigs(const char *pattern, bool verbose)
2529 {
2530         PQExpBufferData buf;
2531         PGresult   *res;
2532         printQueryOpt myopt = pset.popt;
2533
2534         if (pset.sversion < 80300)
2535         {
2536                 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2537                                 pset.sversion / 10000, (pset.sversion / 100) % 100);
2538                 return true;
2539         }
2540
2541         if (verbose)
2542                 return listTSConfigsVerbose(pattern);
2543
2544         initPQExpBuffer(&buf);
2545
2546         printfPQExpBuffer(&buf,
2547                                           "SELECT \n"
2548                                           "   n.nspname as \"%s\",\n"
2549                                           "   c.cfgname as \"%s\",\n"
2550                    "   pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
2551                                           "FROM pg_catalog.pg_ts_config c\n"
2552                   "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
2553                                           gettext_noop("Schema"),
2554                                           gettext_noop("Name"),
2555                                           gettext_noop("Description")
2556                 );
2557
2558         processSQLNamePattern(pset.db, &buf, pattern, false, false,
2559                                                   "n.nspname", "c.cfgname", NULL,
2560                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
2561
2562         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2563
2564         res = PSQLexec(buf.data, false);
2565         termPQExpBuffer(&buf);
2566         if (!res)
2567                 return false;
2568
2569         myopt.nullPrint = NULL;
2570         myopt.title = _("List of text search configurations");
2571         myopt.translate_header = true;
2572
2573         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2574
2575         PQclear(res);
2576         return true;
2577 }
2578
2579 static bool
2580 listTSConfigsVerbose(const char *pattern)
2581 {
2582         PQExpBufferData buf;
2583         PGresult   *res;
2584         int                     i;
2585
2586         initPQExpBuffer(&buf);
2587
2588         printfPQExpBuffer(&buf,
2589                                           "SELECT c.oid, c.cfgname,\n"
2590                                           "   n.nspname, \n"
2591                                           "   p.prsname, \n"
2592                                           "   np.nspname as pnspname \n"
2593                                           "FROM pg_catalog.pg_ts_config c \n"
2594            "   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
2595                                           " pg_catalog.pg_ts_parser p \n"
2596           "   LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
2597                                           "WHERE  p.oid = c.cfgparser\n"
2598                 );
2599
2600         processSQLNamePattern(pset.db, &buf, pattern, true, false,
2601                                                   "n.nspname", "c.cfgname", NULL,
2602                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
2603
2604         appendPQExpBuffer(&buf, "ORDER BY 3, 2;");
2605
2606         res = PSQLexec(buf.data, false);
2607         termPQExpBuffer(&buf);
2608         if (!res)
2609                 return false;
2610
2611         if (PQntuples(res) == 0)
2612         {
2613                 if (!pset.quiet)
2614                         fprintf(stderr, _("Did not find any text search configuration named \"%s\".\n"),
2615                                         pattern);
2616                 PQclear(res);
2617                 return false;
2618         }
2619
2620         for (i = 0; i < PQntuples(res); i++)
2621         {
2622                 const char *oid;
2623                 const char *cfgname;
2624                 const char *nspname = NULL;
2625                 const char *prsname;
2626                 const char *pnspname = NULL;
2627
2628                 oid = PQgetvalue(res, i, 0);
2629                 cfgname = PQgetvalue(res, i, 1);
2630                 if (!PQgetisnull(res, i, 2))
2631                         nspname = PQgetvalue(res, i, 2);
2632                 prsname = PQgetvalue(res, i, 3);
2633                 if (!PQgetisnull(res, i, 4))
2634                         pnspname = PQgetvalue(res, i, 4);
2635
2636                 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
2637                 {
2638                         PQclear(res);
2639                         return false;
2640                 }
2641
2642                 if (cancel_pressed)
2643                 {
2644                         PQclear(res);
2645                         return false;
2646                 }
2647         }
2648
2649         PQclear(res);
2650         return true;
2651 }
2652
2653 static bool
2654 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
2655                                         const char *pnspname, const char *prsname)
2656 {
2657         PQExpBufferData buf,
2658                                 title;
2659         PGresult   *res;
2660         printQueryOpt myopt = pset.popt;
2661
2662         initPQExpBuffer(&buf);
2663
2664         printfPQExpBuffer(&buf,
2665                                           "SELECT \n"
2666                                           "  ( SELECT t.alias FROM \n"
2667                                           "    pg_catalog.ts_token_type(c.cfgparser) AS t \n"
2668                                           "    WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
2669                                           "  pg_catalog.btrim( \n"
2670                                   "    ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
2671                                           "           FROM pg_catalog.pg_ts_config_map AS mm \n"
2672                                           "           WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
2673                                           "           ORDER BY mapcfg, maptokentype, mapseqno \n"
2674                                           "    ) :: pg_catalog.text , \n"
2675                                           "  '{}') AS \"%s\" \n"
2676          "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
2677                                           "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
2678                                           "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
2679                                           "ORDER BY 1",
2680                                           gettext_noop("Token"),
2681                                           gettext_noop("Dictionaries"),
2682                                           oid);
2683
2684         res = PSQLexec(buf.data, false);
2685         termPQExpBuffer(&buf);
2686         if (!res)
2687                 return false;
2688
2689         initPQExpBuffer(&title);
2690
2691         if (nspname)
2692                 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
2693                                                   nspname, cfgname);
2694         else
2695                 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
2696                                                   cfgname);
2697
2698         if (pnspname)
2699                 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
2700                                                   pnspname, prsname);
2701         else
2702                 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
2703                                                   prsname);
2704
2705         myopt.nullPrint = NULL;
2706         myopt.title = title.data;
2707         myopt.footers = NULL;
2708         myopt.default_footer = false;
2709         myopt.translate_header = true;
2710
2711         printQuery(res, &myopt, pset.queryFout, pset.logfile);
2712
2713         termPQExpBuffer(&title);
2714
2715         PQclear(res);
2716         return true;
2717 }