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-2017, PostgreSQL Global Development Group
  10  *
  11  * src/bin/psql/describe.c
  12  */
  13 #include "postgres_fe.h"
  14
  15 #include <ctype.h>
  16
  17 #include "catalog/pg_class.h"
  18 #include "catalog/pg_default_acl.h"
  19 #include "fe_utils/string_utils.h"
  20
  21 #include "common.h"
  22 #include "describe.h"
  23 #include "fe_utils/mbprint.h"
  24 #include "fe_utils/print.h"
  25 #include "settings.h"
  26 #include "variables.h"
  27
  28
  29 static bool describeOneTableDetails(const char *schemaname,
  30                                                 const char *relationname,
  31                                                 const char *oid,
  32                                                 bool verbose);
  33 static void add_tablespace_footer(printTableContent *const cont, char relkind,
  34                                           Oid tablespace, const bool newline);
  35 static void add_role_attribute(PQExpBuffer buf, const char *const str);
  36 static bool listTSParsersVerbose(const char *pattern);
  37 static bool describeOneTSParser(const char *oid, const char *nspname,
  38                                         const char *prsname);
  39 static bool listTSConfigsVerbose(const char *pattern);
  40 static bool describeOneTSConfig(const char *oid, const char *nspname,
  41                                         const char *cfgname,
  42                                         const char *pnspname, const char *prsname);
  43 static void printACLColumn(PQExpBuffer buf, const char *colname);
  44 static bool listOneExtensionContents(const char *extname, const char *oid);
  45
  46
  47 /*----------------
  48  * Handlers for various slash commands displaying some sort of list
  49  * of things in the database.
  50  *
  51  * Note: try to format the queries to look nice in -E output.
  52  *----------------
  53  */
  54
  55
  56 /* \da
  57  * Takes an optional regexp to select particular aggregates
  58  */
  59 bool
  60 describeAggregates(const char *pattern, bool verbose, bool showSystem)
  61 {
  62         PQExpBufferData buf;
  63         PGresult   *res;
  64         printQueryOpt myopt = pset.popt;
  65
  66         initPQExpBuffer(&buf);
  67
  68         printfPQExpBuffer(&buf,
  69                                           "SELECT n.nspname as \"%s\",\n"
  70                                           "  p.proname AS \"%s\",\n"
  71                                  "  pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n",
  72                                           gettext_noop("Schema"),
  73                                           gettext_noop("Name"),
  74                                           gettext_noop("Result data type"));
  75
  76         if (pset.sversion >= 80400)
  77                 appendPQExpBuffer(&buf,
  78                                                   "  CASE WHEN p.pronargs = 0\n"
  79                                                   "    THEN CAST('*' AS pg_catalog.text)\n"
  80                                          "    ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
  81                                                   "  END AS \"%s\",\n",
  82                                                   gettext_noop("Argument data types"));
  83         else if (pset.sversion >= 80200)
  84                 appendPQExpBuffer(&buf,
  85                                                   "  CASE WHEN p.pronargs = 0\n"
  86                                                   "    THEN CAST('*' AS pg_catalog.text)\n"
  87                                                   "    ELSE\n"
  88                                                   "    pg_catalog.array_to_string(ARRAY(\n"
  89                                                   "      SELECT\n"
  90                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
  91                                                   "      FROM\n"
  92                                                   "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
  93                                                   "    ), ', ')\n"
  94                                                   "  END AS \"%s\",\n",
  95                                                   gettext_noop("Argument data types"));
  96         else
  97                 appendPQExpBuffer(&buf,
  98                          "  pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
  99                                                   gettext_noop("Argument data types"));
 100
 101         appendPQExpBuffer(&buf,
 102                                  "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
 103                                           "FROM pg_catalog.pg_proc p\n"
 104            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 105                                           "WHERE p.proisagg\n",
 106                                           gettext_noop("Description"));
 107
 108         if (!showSystem && !pattern)
 109                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
 110                                                          "      AND n.nspname <> 'information_schema'\n");
 111
 112         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 113                                                   "n.nspname", "p.proname", NULL,
 114                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 115
 116         appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
 117
 118         res = PSQLexec(buf.data);
 119         termPQExpBuffer(&buf);
 120         if (!res)
 121                 return false;
 122
 123         myopt.nullPrint = NULL;
 124         myopt.title = _("List of aggregate functions");
 125         myopt.translate_header = true;
 126
 127         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 128
 129         PQclear(res);
 130         return true;
 131 }
 132
 133 /* \dA
 134  * Takes an optional regexp to select particular access methods
 135  */
 136 bool
 137 describeAccessMethods(const char *pattern, bool verbose)
 138 {
 139         PQExpBufferData buf;
 140         PGresult   *res;
 141         printQueryOpt myopt = pset.popt;
 142         static const bool translate_columns[] = {false, true, false, false};
 143
 144         if (pset.sversion < 90600)
 145         {
 146                 char            sverbuf[32];
 147
 148                 psql_error("The server (version %s) does not support access methods.\n",
 149                                    formatPGVersionNumber(pset.sversion, false,
 150                                                                                  sverbuf, sizeof(sverbuf)));
 151                 return true;
 152         }
 153
 154         initPQExpBuffer(&buf);
 155
 156         printfPQExpBuffer(&buf,
 157                                           "SELECT amname AS \"%s\",\n"
 158                                           "  CASE amtype"
 159                                           " WHEN 'i' THEN '%s'"
 160                                           " END AS \"%s\"",
 161                                           gettext_noop("Name"),
 162                                           gettext_noop("Index"),
 163                                           gettext_noop("Type"));
 164
 165         if (verbose)
 166         {
 167                 appendPQExpBuffer(&buf,
 168                                                   ",\n  amhandler AS \"%s\",\n"
 169                                           "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 170                                                   gettext_noop("Handler"),
 171                                                   gettext_noop("Description"));
 172         }
 173
 174         appendPQExpBufferStr(&buf,
 175                                                  "\nFROM pg_catalog.pg_am\n");
 176
 177         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 178                                                   NULL, "amname", NULL,
 179                                                   NULL);
 180
 181         appendPQExpBufferStr(&buf, "ORDER BY 1;");
 182
 183         res = PSQLexec(buf.data);
 184         termPQExpBuffer(&buf);
 185         if (!res)
 186                 return false;
 187
 188         myopt.nullPrint = NULL;
 189         myopt.title = _("List of access methods");
 190         myopt.translate_header = true;
 191         myopt.translate_columns = translate_columns;
 192         myopt.n_translate_columns = lengthof(translate_columns);
 193
 194         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 195
 196         PQclear(res);
 197         return true;
 198 }
 199
 200 /* \db
 201  * Takes an optional regexp to select particular tablespaces
 202  */
 203 bool
 204 describeTablespaces(const char *pattern, bool verbose)
 205 {
 206         PQExpBufferData buf;
 207         PGresult   *res;
 208         printQueryOpt myopt = pset.popt;
 209
 210         if (pset.sversion < 80000)
 211         {
 212                 char            sverbuf[32];
 213
 214                 psql_error("The server (version %s) does not support tablespaces.\n",
 215                                    formatPGVersionNumber(pset.sversion, false,
 216                                                                                  sverbuf, sizeof(sverbuf)));
 217                 return true;
 218         }
 219
 220         initPQExpBuffer(&buf);
 221
 222         if (pset.sversion >= 90200)
 223                 printfPQExpBuffer(&buf,
 224                                                   "SELECT spcname AS \"%s\",\n"
 225                                                 "  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
 226                                                 "  pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
 227                                                   gettext_noop("Name"),
 228                                                   gettext_noop("Owner"),
 229                                                   gettext_noop("Location"));
 230         else
 231                 printfPQExpBuffer(&buf,
 232                                                   "SELECT spcname AS \"%s\",\n"
 233                                                 "  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
 234                                                   "  spclocation AS \"%s\"",
 235                                                   gettext_noop("Name"),
 236                                                   gettext_noop("Owner"),
 237                                                   gettext_noop("Location"));
 238
 239         if (verbose)
 240         {
 241                 appendPQExpBufferStr(&buf, ",\n  ");
 242                 printACLColumn(&buf, "spcacl");
 243         }
 244
 245         if (verbose && pset.sversion >= 90000)
 246                 appendPQExpBuffer(&buf,
 247                                                   ",\n  spcoptions AS \"%s\"",
 248                                                   gettext_noop("Options"));
 249
 250         if (verbose && pset.sversion >= 90200)
 251                 appendPQExpBuffer(&buf,
 252                                                   ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
 253                                                   gettext_noop("Size"));
 254
 255         if (verbose && pset.sversion >= 80200)
 256                 appendPQExpBuffer(&buf,
 257                  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 258                                                   gettext_noop("Description"));
 259
 260         appendPQExpBufferStr(&buf,
 261                                                  "\nFROM pg_catalog.pg_tablespace\n");
 262
 263         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 264                                                   NULL, "spcname", NULL,
 265                                                   NULL);
 266
 267         appendPQExpBufferStr(&buf, "ORDER BY 1;");
 268
 269         res = PSQLexec(buf.data);
 270         termPQExpBuffer(&buf);
 271         if (!res)
 272                 return false;
 273
 274         myopt.nullPrint = NULL;
 275         myopt.title = _("List of tablespaces");
 276         myopt.translate_header = true;
 277
 278         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 279
 280         PQclear(res);
 281         return true;
 282 }
 283
 284
 285 /* \df
 286  * Takes an optional regexp to select particular functions.
 287  *
 288  * As with \d, you can specify the kinds of functions you want:
 289  *
 290  * a for aggregates
 291  * n for normal
 292  * t for trigger
 293  * w for window
 294  *
 295  * and you can mix and match these in any order.
 296  */
 297 bool
 298 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
 299 {
 300         bool            showAggregate = strchr(functypes, 'a') != NULL;
 301         bool            showNormal = strchr(functypes, 'n') != NULL;
 302         bool            showTrigger = strchr(functypes, 't') != NULL;
 303         bool            showWindow = strchr(functypes, 'w') != NULL;
 304         bool            have_where;
 305         PQExpBufferData buf;
 306         PGresult   *res;
 307         printQueryOpt myopt = pset.popt;
 308         static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
 309
 310         /* No "Parallel" column before 9.6 */
 311         static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
 312
 313         if (strlen(functypes) != strspn(functypes, "antwS+"))
 314         {
 315                 psql_error("\\df only takes [antwS+] as options\n");
 316                 return true;
 317         }
 318
 319         if (showWindow && pset.sversion < 80400)
 320         {
 321                 char            sverbuf[32];
 322
 323                 psql_error("\\df does not take a \"w\" option with server version %s\n",
 324                                    formatPGVersionNumber(pset.sversion, false,
 325                                                                                  sverbuf, sizeof(sverbuf)));
 326                 return true;
 327         }
 328
 329         if (!showAggregate && !showNormal && !showTrigger && !showWindow)
 330         {
 331                 showAggregate = showNormal = showTrigger = true;
 332                 if (pset.sversion >= 80400)
 333                         showWindow = true;
 334         }
 335
 336         initPQExpBuffer(&buf);
 337
 338         printfPQExpBuffer(&buf,
 339                                           "SELECT n.nspname as \"%s\",\n"
 340                                           "  p.proname as \"%s\",\n",
 341                                           gettext_noop("Schema"),
 342                                           gettext_noop("Name"));
 343
 344         if (pset.sversion >= 80400)
 345                 appendPQExpBuffer(&buf,
 346                                         "  pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
 347                                  "  pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
 348                                                   " CASE\n"
 349                                                   "  WHEN p.proisagg THEN '%s'\n"
 350                                                   "  WHEN p.proiswindow THEN '%s'\n"
 351                                                   "  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
 352                                                   "  ELSE '%s'\n"
 353                                                   " END as \"%s\"",
 354                                                   gettext_noop("Result data type"),
 355                                                   gettext_noop("Argument data types"),
 356                 /* translator: "agg" is short for "aggregate" */
 357                                                   gettext_noop("agg"),
 358                                                   gettext_noop("window"),
 359                                                   gettext_noop("trigger"),
 360                                                   gettext_noop("normal"),
 361                                                   gettext_noop("Type"));
 362         else if (pset.sversion >= 80100)
 363                 appendPQExpBuffer(&buf,
 364                                          "  CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
 365                                   "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
 366                                                   "  CASE WHEN proallargtypes IS NOT NULL THEN\n"
 367                                                   "    pg_catalog.array_to_string(ARRAY(\n"
 368                                                   "      SELECT\n"
 369                                                   "        CASE\n"
 370                                                   "          WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
 371                                           "          WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
 372                                         "          WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
 373                                  "          WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
 374                                                   "        END ||\n"
 375                                                   "        CASE\n"
 376                          "          WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
 377                                                   "          ELSE p.proargnames[s.i] || ' ' \n"
 378                                                   "        END ||\n"
 379                           "        pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
 380                                                   "      FROM\n"
 381                                                   "        pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
 382                                                   "    ), ', ')\n"
 383                                                   "  ELSE\n"
 384                                                   "    pg_catalog.array_to_string(ARRAY(\n"
 385                                                   "      SELECT\n"
 386                                                   "        CASE\n"
 387                    "          WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
 388                                                   "          ELSE p.proargnames[s.i+1] || ' '\n"
 389                                                   "          END ||\n"
 390                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
 391                                                   "      FROM\n"
 392                                                   "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
 393                                                   "    ), ', ')\n"
 394                                                   "  END AS \"%s\",\n"
 395                                                   "  CASE\n"
 396                                                   "    WHEN p.proisagg THEN '%s'\n"
 397                                                   "    WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
 398                                                   "    ELSE '%s'\n"
 399                                                   "  END AS \"%s\"",
 400                                                   gettext_noop("Result data type"),
 401                                                   gettext_noop("Argument data types"),
 402                 /* translator: "agg" is short for "aggregate" */
 403                                                   gettext_noop("agg"),
 404                                                   gettext_noop("trigger"),
 405                                                   gettext_noop("normal"),
 406                                                   gettext_noop("Type"));
 407         else
 408                 appendPQExpBuffer(&buf,
 409                                          "  CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
 410                                   "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
 411                                         "  pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n"
 412                                                   "  CASE\n"
 413                                                   "    WHEN p.proisagg THEN '%s'\n"
 414                                                   "    WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
 415                                                   "    ELSE '%s'\n"
 416                                                   "  END AS \"%s\"",
 417                                                   gettext_noop("Result data type"),
 418                                                   gettext_noop("Argument data types"),
 419                 /* translator: "agg" is short for "aggregate" */
 420                                                   gettext_noop("agg"),
 421                                                   gettext_noop("trigger"),
 422                                                   gettext_noop("normal"),
 423                                                   gettext_noop("Type"));
 424
 425         if (verbose)
 426         {
 427                 appendPQExpBuffer(&buf,
 428                                                   ",\n CASE\n"
 429                                                   "  WHEN p.provolatile = 'i' THEN '%s'\n"
 430                                                   "  WHEN p.provolatile = 's' THEN '%s'\n"
 431                                                   "  WHEN p.provolatile = 'v' THEN '%s'\n"
 432                                                   " END as \"%s\"",
 433                                                   gettext_noop("immutable"),
 434                                                   gettext_noop("stable"),
 435                                                   gettext_noop("volatile"),
 436                                                   gettext_noop("Volatility"));
 437                 if (pset.sversion >= 90600)
 438                         appendPQExpBuffer(&buf,
 439                                                           ",\n CASE\n"
 440                                                           "  WHEN p.proparallel = 'r' THEN '%s'\n"
 441                                                           "  WHEN p.proparallel = 's' THEN '%s'\n"
 442                                                           "  WHEN p.proparallel = 'u' THEN '%s'\n"
 443                                                           " END as \"%s\"",
 444                                                           gettext_noop("restricted"),
 445                                                           gettext_noop("safe"),
 446                                                           gettext_noop("unsafe"),
 447                                                           gettext_noop("Parallel"));
 448                 appendPQExpBuffer(&buf,
 449                                            ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
 450                                  ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
 451                                                   gettext_noop("Owner"),
 452                                                   gettext_noop("definer"),
 453                                                   gettext_noop("invoker"),
 454                                                   gettext_noop("Security"));
 455                 appendPQExpBufferStr(&buf, ",\n ");
 456                 printACLColumn(&buf, "p.proacl");
 457                 appendPQExpBuffer(&buf,
 458                                                   ",\n l.lanname as \"%s\""
 459                                                   ",\n p.prosrc as \"%s\""
 460                                 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
 461                                                   gettext_noop("Language"),
 462                                                   gettext_noop("Source code"),
 463                                                   gettext_noop("Description"));
 464         }
 465
 466         appendPQExpBufferStr(&buf,
 467                                                  "\nFROM pg_catalog.pg_proc p"
 468         "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
 469
 470         if (verbose)
 471                 appendPQExpBufferStr(&buf,
 472                    "     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
 473
 474         have_where = false;
 475
 476         /* filter by function type, if requested */
 477         if (showNormal && showAggregate && showTrigger && showWindow)
 478                  /* Do nothing */ ;
 479         else if (showNormal)
 480         {
 481                 if (!showAggregate)
 482                 {
 483                         if (have_where)
 484                                 appendPQExpBufferStr(&buf, "      AND ");
 485                         else
 486                         {
 487                                 appendPQExpBufferStr(&buf, "WHERE ");
 488                                 have_where = true;
 489                         }
 490                         appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
 491                 }
 492                 if (!showTrigger)
 493                 {
 494                         if (have_where)
 495                                 appendPQExpBufferStr(&buf, "      AND ");
 496                         else
 497                         {
 498                                 appendPQExpBufferStr(&buf, "WHERE ");
 499                                 have_where = true;
 500                         }
 501                         appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
 502                 }
 503                 if (!showWindow && pset.sversion >= 80400)
 504                 {
 505                         if (have_where)
 506                                 appendPQExpBufferStr(&buf, "      AND ");
 507                         else
 508                         {
 509                                 appendPQExpBufferStr(&buf, "WHERE ");
 510                                 have_where = true;
 511                         }
 512                         appendPQExpBufferStr(&buf, "NOT p.proiswindow\n");
 513                 }
 514         }
 515         else
 516         {
 517                 bool            needs_or = false;
 518
 519                 appendPQExpBufferStr(&buf, "WHERE (\n       ");
 520                 have_where = true;
 521                 /* Note: at least one of these must be true ... */
 522                 if (showAggregate)
 523                 {
 524                         appendPQExpBufferStr(&buf, "p.proisagg\n");
 525                         needs_or = true;
 526                 }
 527                 if (showTrigger)
 528                 {
 529                         if (needs_or)
 530                                 appendPQExpBufferStr(&buf, "       OR ");
 531                         appendPQExpBufferStr(&buf,
 532                                 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
 533                         needs_or = true;
 534                 }
 535                 if (showWindow)
 536                 {
 537                         if (needs_or)
 538                                 appendPQExpBufferStr(&buf, "       OR ");
 539                         appendPQExpBufferStr(&buf, "p.proiswindow\n");
 540                         needs_or = true;
 541                 }
 542                 appendPQExpBufferStr(&buf, "      )\n");
 543         }
 544
 545         processSQLNamePattern(pset.db, &buf, pattern, have_where, false,
 546                                                   "n.nspname", "p.proname", NULL,
 547                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 548
 549         if (!showSystem && !pattern)
 550                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
 551                                                          "      AND n.nspname <> 'information_schema'\n");
 552
 553         appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
 554
 555         res = PSQLexec(buf.data);
 556         termPQExpBuffer(&buf);
 557         if (!res)
 558                 return false;
 559
 560         myopt.nullPrint = NULL;
 561         myopt.title = _("List of functions");
 562         myopt.translate_header = true;
 563         if (pset.sversion >= 90600)
 564         {
 565                 myopt.translate_columns = translate_columns;
 566                 myopt.n_translate_columns = lengthof(translate_columns);
 567         }
 568         else
 569         {
 570                 myopt.translate_columns = translate_columns_pre_96;
 571                 myopt.n_translate_columns = lengthof(translate_columns_pre_96);
 572         }
 573
 574         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 575
 576         PQclear(res);
 577         return true;
 578 }
 579
 580
 581
 582 /*
 583  * \dT
 584  * describe types
 585  */
 586 bool
 587 describeTypes(const char *pattern, bool verbose, bool showSystem)
 588 {
 589         PQExpBufferData buf;
 590         PGresult   *res;
 591         printQueryOpt myopt = pset.popt;
 592
 593         initPQExpBuffer(&buf);
 594
 595         printfPQExpBuffer(&buf,
 596                                           "SELECT n.nspname as \"%s\",\n"
 597                                           "  pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
 598                                           gettext_noop("Schema"),
 599                                           gettext_noop("Name"));
 600         if (verbose)
 601                 appendPQExpBuffer(&buf,
 602                                                   "  t.typname AS \"%s\",\n"
 603                                                   "  CASE WHEN t.typrelid != 0\n"
 604                                                   "      THEN CAST('tuple' AS pg_catalog.text)\n"
 605                                                   "    WHEN t.typlen < 0\n"
 606                                                   "      THEN CAST('var' AS pg_catalog.text)\n"
 607                                                   "    ELSE CAST(t.typlen AS pg_catalog.text)\n"
 608                                                   "  END AS \"%s\",\n",
 609                                                   gettext_noop("Internal name"),
 610                                                   gettext_noop("Size"));
 611         if (verbose && pset.sversion >= 80300)
 612         {
 613                 appendPQExpBufferStr(&buf,
 614                                                          "  pg_catalog.array_to_string(\n"
 615                                                          "      ARRAY(\n"
 616                                                          "                   SELECT e.enumlabel\n"
 617                                                          "          FROM pg_catalog.pg_enum e\n"
 618                                                          "          WHERE e.enumtypid = t.oid\n");
 619
 620                 if (pset.sversion >= 90100)
 621                         appendPQExpBufferStr(&buf,
 622                                                                  "          ORDER BY e.enumsortorder\n");
 623                 else
 624                         appendPQExpBufferStr(&buf,
 625                                                                  "          ORDER BY e.oid\n");
 626
 627                 appendPQExpBuffer(&buf,
 628                                                   "      ),\n"
 629                                                   "      E'\\n'\n"
 630                                                   "  ) AS \"%s\",\n",
 631                                                   gettext_noop("Elements"));
 632         }
 633         if (verbose)
 634         {
 635                 appendPQExpBuffer(&buf,
 636                                          "  pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
 637                                                   gettext_noop("Owner"));
 638         }
 639         if (verbose && pset.sversion >= 90200)
 640         {
 641                 printACLColumn(&buf, "t.typacl");
 642                 appendPQExpBufferStr(&buf, ",\n  ");
 643         }
 644
 645         appendPQExpBuffer(&buf,
 646                                 "  pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
 647                                           gettext_noop("Description"));
 648
 649         appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
 650          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
 651
 652         /*
 653          * do not include complex types (typrelid!=0) unless they are standalone
 654          * composite types
 655          */
 656         appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 ");
 657         appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
 658                                                  " FROM pg_catalog.pg_class c "
 659                                                  "WHERE c.oid = t.typrelid))\n");
 660
 661         /*
 662          * do not include array types (before 8.3 we have to use the assumption
 663          * that their names start with underscore)
 664          */
 665         if (pset.sversion >= 80300)
 666                 appendPQExpBufferStr(&buf, "  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
 667         else
 668                 appendPQExpBufferStr(&buf, "  AND t.typname !~ '^_'\n");
 669
 670         if (!showSystem && !pattern)
 671                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
 672                                                          "      AND n.nspname <> 'information_schema'\n");
 673
 674         /* Match name pattern against either internal or external name */
 675         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 676                                                   "n.nspname", "t.typname",
 677                                                   "pg_catalog.format_type(t.oid, NULL)",
 678                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 679
 680         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
 681
 682         res = PSQLexec(buf.data);
 683         termPQExpBuffer(&buf);
 684         if (!res)
 685                 return false;
 686
 687         myopt.nullPrint = NULL;
 688         myopt.title = _("List of data types");
 689         myopt.translate_header = true;
 690
 691         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 692
 693         PQclear(res);
 694         return true;
 695 }
 696
 697
 698 /* \do
 699  * Describe operators
 700  */
 701 bool
 702 describeOperators(const char *pattern, bool verbose, bool showSystem)
 703 {
 704         PQExpBufferData buf;
 705         PGresult   *res;
 706         printQueryOpt myopt = pset.popt;
 707
 708         initPQExpBuffer(&buf);
 709
 710         /*
 711          * Note: before Postgres 9.1, we did not assign comments to any built-in
 712          * operators, preferring to let the comment on the underlying function
 713          * suffice.  The coalesce() on the obj_description() calls below supports
 714          * this convention by providing a fallback lookup of a comment on the
 715          * operator's function.  As of 9.1 there is a policy that every built-in
 716          * operator should have a comment; so the coalesce() is no longer
 717          * necessary so far as built-in operators are concerned.  We keep it
 718          * anyway, for now, because (1) third-party modules may still be following
 719          * the old convention, and (2) we'd need to do it anyway when talking to a
 720          * pre-9.1 server.
 721          */
 722
 723         printfPQExpBuffer(&buf,
 724                                           "SELECT n.nspname as \"%s\",\n"
 725                                           "  o.oprname AS \"%s\",\n"
 726                                           "  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
 727                                           "  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
 728                                   "  pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
 729                                           gettext_noop("Schema"),
 730                                           gettext_noop("Name"),
 731                                           gettext_noop("Left arg type"),
 732                                           gettext_noop("Right arg type"),
 733                                           gettext_noop("Result type"));
 734
 735         if (verbose)
 736                 appendPQExpBuffer(&buf,
 737                                                   "  o.oprcode AS \"%s\",\n",
 738                                                   gettext_noop("Function"));
 739
 740         appendPQExpBuffer(&buf,
 741                          "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
 742         "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
 743                                           "FROM pg_catalog.pg_operator o\n"
 744           "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 745                                           gettext_noop("Description"));
 746
 747         if (!showSystem && !pattern)
 748                 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
 749                                                          "      AND n.nspname <> 'information_schema'\n");
 750
 751         processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
 752                                                   "n.nspname", "o.oprname", NULL,
 753                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 754
 755         appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
 756
 757         res = PSQLexec(buf.data);
 758         termPQExpBuffer(&buf);
 759         if (!res)
 760                 return false;
 761
 762         myopt.nullPrint = NULL;
 763         myopt.title = _("List of operators");
 764         myopt.translate_header = true;
 765
 766         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 767
 768         PQclear(res);
 769         return true;
 770 }
 771
 772
 773 /*
 774  * listAllDbs
 775  *
 776  * for \l, \list, and -l switch
 777  */
 778 bool
 779 listAllDbs(const char *pattern, bool verbose)
 780 {
 781         PGresult   *res;
 782         PQExpBufferData buf;
 783         printQueryOpt myopt = pset.popt;
 784
 785         initPQExpBuffer(&buf);
 786
 787         printfPQExpBuffer(&buf,
 788                                           "SELECT d.datname as \"%s\",\n"
 789                                    "       pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
 790                         "       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
 791                                           gettext_noop("Name"),
 792                                           gettext_noop("Owner"),
 793                                           gettext_noop("Encoding"));
 794         if (pset.sversion >= 80400)
 795                 appendPQExpBuffer(&buf,
 796                                                   "       d.datcollate as \"%s\",\n"
 797                                                   "       d.datctype as \"%s\",\n",
 798                                                   gettext_noop("Collate"),
 799                                                   gettext_noop("Ctype"));
 800         appendPQExpBufferStr(&buf, "       ");
 801         printACLColumn(&buf, "d.datacl");
 802         if (verbose && pset.sversion >= 80200)
 803                 appendPQExpBuffer(&buf,
 804                                                   ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 805                                                   "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 806                                                   "            ELSE 'No Access'\n"
 807                                                   "       END as \"%s\"",
 808                                                   gettext_noop("Size"));
 809         if (verbose && pset.sversion >= 80000)
 810                 appendPQExpBuffer(&buf,
 811                                                   ",\n       t.spcname as \"%s\"",
 812                                                   gettext_noop("Tablespace"));
 813         if (verbose && pset.sversion >= 80200)
 814                 appendPQExpBuffer(&buf,
 815                                                   ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
 816                                                   gettext_noop("Description"));
 817         appendPQExpBufferStr(&buf,
 818                                                  "\nFROM pg_catalog.pg_database d\n");
 819         if (verbose && pset.sversion >= 80000)
 820                 appendPQExpBufferStr(&buf,
 821                    "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 822
 823         if (pattern)
 824                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
 825                                                           NULL, "d.datname", NULL, NULL);
 826
 827         appendPQExpBufferStr(&buf, "ORDER BY 1;");
 828         res = PSQLexec(buf.data);
 829         termPQExpBuffer(&buf);
 830         if (!res)
 831                 return false;
 832
 833         myopt.nullPrint = NULL;
 834         myopt.title = _("List of databases");
 835         myopt.translate_header = true;
 836
 837         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 838
 839         PQclear(res);
 840         return true;
 841 }
 842
 843
 844 /*
 845  * List Tables' Grant/Revoke Permissions
 846  * \z (now also \dp -- perhaps more mnemonic)
 847  */
 848 bool
 849 permissionsList(const char *pattern)
 850 {
 851         PQExpBufferData buf;
 852         PGresult   *res;
 853         printQueryOpt myopt = pset.popt;
 854         static const bool translate_columns[] = {false, false, true, false, false, false};
 855
 856         initPQExpBuffer(&buf);
 857
 858         /*
 859          * we ignore indexes and toast tables since they have no meaningful rights
 860          */
 861         printfPQExpBuffer(&buf,
 862                                           "SELECT n.nspname as \"%s\",\n"
 863                                           "  c.relname as \"%s\",\n"
 864                                           "  CASE c.relkind"
 865                                           " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
 866                                           " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
 867                                           " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
 868                                           " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
 869                                           " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
 870                                           " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
 871                                           " END as \"%s\",\n"
 872                                           "  ",
 873                                           gettext_noop("Schema"),
 874                                           gettext_noop("Name"),
 875                                           gettext_noop("table"),
 876                                           gettext_noop("view"),
 877                                           gettext_noop("materialized view"),
 878                                           gettext_noop("sequence"),
 879                                           gettext_noop("foreign table"),
 880                                           gettext_noop("table"),        /* partitioned table */
 881                                           gettext_noop("Type"));
 882
 883         printACLColumn(&buf, "c.relacl");
 884
 885         if (pset.sversion >= 80400)
 886                 appendPQExpBuffer(&buf,
 887                                                   ",\n  pg_catalog.array_to_string(ARRAY(\n"
 888                                                   "    SELECT attname || E':\\n  ' || pg_catalog.array_to_string(attacl, E'\\n  ')\n"
 889                                                   "    FROM pg_catalog.pg_attribute a\n"
 890                                                   "    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
 891                                                   "  ), E'\\n') AS \"%s\"",
 892                                                   gettext_noop("Column privileges"));
 893
 894         if (pset.sversion >= 90500 && pset.sversion < 100000)
 895                 appendPQExpBuffer(&buf,
 896                                                   ",\n  pg_catalog.array_to_string(ARRAY(\n"
 897                                                   "    SELECT polname\n"
 898                                                   "    || CASE WHEN polcmd != '*' THEN\n"
 899                                                   "           E' (' || polcmd || E'):'\n"
 900                                                   "       ELSE E':' \n"
 901                                                   "       END\n"
 902                                                   "    || CASE WHEN polqual IS NOT NULL THEN\n"
 903                                                   "           E'\\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
 904                                                   "       ELSE E''\n"
 905                                                   "       END\n"
 906                                                   "    || CASE WHEN polwithcheck IS NOT NULL THEN\n"
 907                                                   "           E'\\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
 908                                                   "       ELSE E''\n"
 909                                                   "       END"
 910                                                   "    || CASE WHEN polroles <> '{0}' THEN\n"
 911                                    "           E'\\n  to: ' || pg_catalog.array_to_string(\n"
 912                                                   "               ARRAY(\n"
 913                                                   "                   SELECT rolname\n"
 914                                                   "                   FROM pg_catalog.pg_roles\n"
 915                                                   "                   WHERE oid = ANY (polroles)\n"
 916                                                   "                   ORDER BY 1\n"
 917                                                   "               ), E', ')\n"
 918                                                   "       ELSE E''\n"
 919                                                   "       END\n"
 920                                                   "    FROM pg_catalog.pg_policy pol\n"
 921                                                   "    WHERE polrelid = c.oid), E'\\n')\n"
 922                                                   "    AS \"%s\"",
 923                                                   gettext_noop("Policies"));
 924
 925         if (pset.sversion >= 100000)
 926                 appendPQExpBuffer(&buf,
 927                                                   ",\n  pg_catalog.array_to_string(ARRAY(\n"
 928                                                   "    SELECT polname\n"
 929                                                   "    || CASE WHEN NOT polpermissive THEN\n"
 930                                                   "       E' (RESTRICTIVE)'\n"
 931                                                   "       ELSE '' END\n"
 932                                                   "    || CASE WHEN polcmd != '*' THEN\n"
 933                                                   "           E' (' || polcmd || E'):'\n"
 934                                                   "       ELSE E':' \n"
 935                                                   "       END\n"
 936                                                   "    || CASE WHEN polqual IS NOT NULL THEN\n"
 937                                                   "           E'\\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
 938                                                   "       ELSE E''\n"
 939                                                   "       END\n"
 940                                                   "    || CASE WHEN polwithcheck IS NOT NULL THEN\n"
 941                                                   "           E'\\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
 942                                                   "       ELSE E''\n"
 943                                                   "       END"
 944                                                   "    || CASE WHEN polroles <> '{0}' THEN\n"
 945                                    "           E'\\n  to: ' || pg_catalog.array_to_string(\n"
 946                                                   "               ARRAY(\n"
 947                                                   "                   SELECT rolname\n"
 948                                                   "                   FROM pg_catalog.pg_roles\n"
 949                                                   "                   WHERE oid = ANY (polroles)\n"
 950                                                   "                   ORDER BY 1\n"
 951                                                   "               ), E', ')\n"
 952                                                   "       ELSE E''\n"
 953                                                   "       END\n"
 954                                                   "    FROM pg_catalog.pg_policy pol\n"
 955                                                   "    WHERE polrelid = c.oid), E'\\n')\n"
 956                                                   "    AS \"%s\"",
 957                                                   gettext_noop("Policies"));
 958
 959         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
 960            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 961                                                  "WHERE c.relkind IN ("
 962                                                  CppAsString2(RELKIND_RELATION) ","
 963                                                  CppAsString2(RELKIND_VIEW) ","
 964                                                  CppAsString2(RELKIND_MATVIEW) ","
 965                                                  CppAsString2(RELKIND_SEQUENCE) ","
 966                                                  CppAsString2(RELKIND_FOREIGN_TABLE) ","
 967                                                  CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n");
 968
 969         /*
 970          * Unless a schema pattern is specified, we suppress system and temp
 971          * tables, since they normally aren't very interesting from a permissions
 972          * point of view.  You can see 'em by explicit request though, eg with \z
 973          * pg_catalog.*
 974          */
 975         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 976                                                   "n.nspname", "c.relname", NULL,
 977                         "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
 978
 979         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
 980
 981         res = PSQLexec(buf.data);
 982         if (!res)
 983         {
 984                 termPQExpBuffer(&buf);
 985                 return false;
 986         }
 987
 988         myopt.nullPrint = NULL;
 989         printfPQExpBuffer(&buf, _("Access privileges"));
 990         myopt.title = buf.data;
 991         myopt.translate_header = true;
 992         myopt.translate_columns = translate_columns;
 993         myopt.n_translate_columns = lengthof(translate_columns);
 994
 995         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
 996
 997         termPQExpBuffer(&buf);
 998         PQclear(res);
 999         return true;
1000 }
1001
1002
1003 /*
1004  * \ddp
1005  *
1006  * List Default ACLs.  The pattern can match either schema or role name.
1007  */
1008 bool
1009 listDefaultACLs(const char *pattern)
1010 {
1011         PQExpBufferData buf;
1012         PGresult   *res;
1013         printQueryOpt myopt = pset.popt;
1014         static const bool translate_columns[] = {false, false, true, false};
1015
1016         if (pset.sversion < 90000)
1017         {
1018                 char            sverbuf[32];
1019
1020                 psql_error("The server (version %s) does not support altering default privileges.\n",
1021                                    formatPGVersionNumber(pset.sversion, false,
1022                                                                                  sverbuf, sizeof(sverbuf)));
1023                 return true;
1024         }
1025
1026         initPQExpBuffer(&buf);
1027
1028         printfPQExpBuffer(&buf,
1029                            "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
1030                                           "  n.nspname AS \"%s\",\n"
1031                                           "  CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
1032                                           "  ",
1033                                           gettext_noop("Owner"),
1034                                           gettext_noop("Schema"),
1035                                           DEFACLOBJ_RELATION,
1036                                           gettext_noop("table"),
1037                                           DEFACLOBJ_SEQUENCE,
1038                                           gettext_noop("sequence"),
1039                                           DEFACLOBJ_FUNCTION,
1040                                           gettext_noop("function"),
1041                                           DEFACLOBJ_TYPE,
1042                                           gettext_noop("type"),
1043                                           gettext_noop("Type"));
1044
1045         printACLColumn(&buf, "d.defaclacl");
1046
1047         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
1048                                                  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
1049
1050         processSQLNamePattern(pset.db, &buf, pattern, false, false,
1051                                                   NULL,
1052                                                   "n.nspname",
1053                                                   "pg_catalog.pg_get_userbyid(d.defaclrole)",
1054                                                   NULL);
1055
1056         appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1057
1058         res = PSQLexec(buf.data);
1059         if (!res)
1060         {
1061                 termPQExpBuffer(&buf);
1062                 return false;
1063         }
1064
1065         myopt.nullPrint = NULL;
1066         printfPQExpBuffer(&buf, _("Default access privileges"));
1067         myopt.title = buf.data;
1068         myopt.translate_header = true;
1069         myopt.translate_columns = translate_columns;
1070         myopt.n_translate_columns = lengthof(translate_columns);
1071
1072         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1073
1074         termPQExpBuffer(&buf);
1075         PQclear(res);
1076         return true;
1077 }
1078
1079
1080 /*
1081  * Get object comments
1082  *
1083  * \dd [foo]
1084  *
1085  * Note: This command only lists comments for object types which do not have
1086  * their comments displayed by their own backslash commands. The following
1087  * types of objects will be displayed: constraint, operator class,
1088  * operator family, rule, and trigger.
1089  *
1090  */
1091 bool
1092 objectDescription(const char *pattern, bool showSystem)
1093 {
1094         PQExpBufferData buf;
1095         PGresult   *res;
1096         printQueryOpt myopt = pset.popt;
1097         static const bool translate_columns[] = {false, false, true, false};
1098
1099         initPQExpBuffer(&buf);
1100
1101         appendPQExpBuffer(&buf,
1102                                           "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1103                                           "FROM (\n",
1104                                           gettext_noop("Schema"),
1105                                           gettext_noop("Name"),
1106                                           gettext_noop("Object"),
1107                                           gettext_noop("Description"));
1108
1109         /* Table constraint descriptions */
1110         appendPQExpBuffer(&buf,
1111                                           "  SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1112                                           "  n.nspname as nspname,\n"
1113                                           "  CAST(pgc.conname AS pg_catalog.text) as name,"
1114                                           "  CAST('%s' AS pg_catalog.text) as object\n"
1115                                           "  FROM pg_catalog.pg_constraint pgc\n"
1116                                           "    JOIN pg_catalog.pg_class c "
1117                                           "ON c.oid = pgc.conrelid\n"
1118                                           "    LEFT JOIN pg_catalog.pg_namespace n "
1119                                           "    ON n.oid = c.relnamespace\n",
1120                                           gettext_noop("table constraint"));
1121
1122         if (!showSystem && !pattern)
1123                 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1124                                                          "      AND n.nspname <> 'information_schema'\n");
1125
1126         processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1127                                                   false, "n.nspname", "pgc.conname", NULL,
1128                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1129
1130         /* Domain constraint descriptions */
1131         appendPQExpBuffer(&buf,
1132                                           "UNION ALL\n"
1133                                           "  SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1134                                           "  n.nspname as nspname,\n"
1135                                           "  CAST(pgc.conname AS pg_catalog.text) as name,"
1136                                           "  CAST('%s' AS pg_catalog.text) as object\n"
1137                                           "  FROM pg_catalog.pg_constraint pgc\n"
1138                                           "    JOIN pg_catalog.pg_type t "
1139                                           "ON t.oid = pgc.contypid\n"
1140                                           "    LEFT JOIN pg_catalog.pg_namespace n "
1141                                           "    ON n.oid = t.typnamespace\n",
1142                                           gettext_noop("domain constraint"));
1143
1144         if (!showSystem && !pattern)
1145                 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1146                                                          "      AND n.nspname <> 'information_schema'\n");
1147
1148         processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1149                                                   false, "n.nspname", "pgc.conname", NULL,
1150                                                   "pg_catalog.pg_type_is_visible(t.oid)");
1151
1152
1153         /*
1154          * pg_opclass.opcmethod only available in 8.3+
1155          */
1156         if (pset.sversion >= 80300)
1157         {
1158                 /* Operator class descriptions */
1159                 appendPQExpBuffer(&buf,
1160                                                   "UNION ALL\n"
1161                                                   "  SELECT o.oid as oid, o.tableoid as tableoid,\n"
1162                                                   "  n.nspname as nspname,\n"
1163                                                   "  CAST(o.opcname AS pg_catalog.text) as name,\n"
1164                                                   "  CAST('%s' AS pg_catalog.text) as object\n"
1165                                                   "  FROM pg_catalog.pg_opclass o\n"
1166                                                   "    JOIN pg_catalog.pg_am am ON "
1167                                                   "o.opcmethod = am.oid\n"
1168                                                   "    JOIN pg_catalog.pg_namespace n ON "
1169                                                   "n.oid = o.opcnamespace\n",
1170                                                   gettext_noop("operator class"));
1171
1172                 if (!showSystem && !pattern)
1173                         appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
1174                                                         "      AND n.nspname <> 'information_schema'\n");
1175
1176                 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1177                                                           "n.nspname", "o.opcname", NULL,
1178                                                           "pg_catalog.pg_opclass_is_visible(o.oid)");
1179         }
1180
1181         /*
1182          * although operator family comments have been around since 8.3,
1183          * pg_opfamily_is_visible is only available in 9.2+
1184          */
1185         if (pset.sversion >= 90200)
1186         {
1187                 /* Operator family descriptions */
1188                 appendPQExpBuffer(&buf,
1189                                                   "UNION ALL\n"
1190                                            "  SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1191                                                   "  n.nspname as nspname,\n"
1192                                                   "  CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1193                                                   "  CAST('%s' AS pg_catalog.text) as object\n"
1194                                                   "  FROM pg_catalog.pg_opfamily opf\n"
1195                                                   "    JOIN pg_catalog.pg_am am "
1196                                                   "ON opf.opfmethod = am.oid\n"
1197                                                   "    JOIN pg_catalog.pg_namespace n "
1198                                                   "ON opf.opfnamespace = n.oid\n",
1199                                                   gettext_noop("operator family"));
1200
1201                 if (!showSystem && !pattern)
1202                         appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
1203                                                         "      AND n.nspname <> 'information_schema'\n");
1204
1205                 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1206                                                           "n.nspname", "opf.opfname", NULL,
1207                                                           "pg_catalog.pg_opfamily_is_visible(opf.oid)");
1208         }
1209
1210         /* Rule descriptions (ignore rules for views) */
1211         appendPQExpBuffer(&buf,
1212                                           "UNION ALL\n"
1213                                           "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
1214                                           "  n.nspname as nspname,\n"
1215                                           "  CAST(r.rulename AS pg_catalog.text) as name,"
1216                                           "  CAST('%s' AS pg_catalog.text) as object\n"
1217                                           "  FROM pg_catalog.pg_rewrite r\n"
1218                                   "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1219          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1220                                           "  WHERE r.rulename != '_RETURN'\n",
1221                                           gettext_noop("rule"));
1222
1223         if (!showSystem && !pattern)
1224                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
1225                                                          "      AND n.nspname <> 'information_schema'\n");
1226
1227         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1228                                                   "n.nspname", "r.rulename", NULL,
1229                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1230
1231         /* Trigger descriptions */
1232         appendPQExpBuffer(&buf,
1233                                           "UNION ALL\n"
1234                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
1235                                           "  n.nspname as nspname,\n"
1236                                           "  CAST(t.tgname AS pg_catalog.text) as name,"
1237                                           "  CAST('%s' AS pg_catalog.text) as object\n"
1238                                           "  FROM pg_catalog.pg_trigger t\n"
1239                                    "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1240         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1241                                           gettext_noop("trigger"));
1242
1243         if (!showSystem && !pattern)
1244                 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1245                                                          "      AND n.nspname <> 'information_schema'\n");
1246
1247         processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1248                                                   "n.nspname", "t.tgname", NULL,
1249                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1250
1251         appendPQExpBufferStr(&buf,
1252                                                  ") AS tt\n"
1253                                                  "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1254
1255         appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1256
1257         res = PSQLexec(buf.data);
1258         termPQExpBuffer(&buf);
1259         if (!res)
1260                 return false;
1261
1262         myopt.nullPrint = NULL;
1263         myopt.title = _("Object descriptions");
1264         myopt.translate_header = true;
1265         myopt.translate_columns = translate_columns;
1266         myopt.n_translate_columns = lengthof(translate_columns);
1267
1268         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1269
1270         PQclear(res);
1271         return true;
1272 }
1273
1274
1275 /*
1276  * describeTableDetails (for \d)
1277  *
1278  * This routine finds the tables to be displayed, and calls
1279  * describeOneTableDetails for each one.
1280  *
1281  * verbose: if true, this is \d+
1282  */
1283 bool
1284 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1285 {
1286         PQExpBufferData buf;
1287         PGresult   *res;
1288         int                     i;
1289
1290         initPQExpBuffer(&buf);
1291
1292         printfPQExpBuffer(&buf,
1293                                           "SELECT c.oid,\n"
1294                                           "  n.nspname,\n"
1295                                           "  c.relname\n"
1296                                           "FROM pg_catalog.pg_class c\n"
1297          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1298
1299         if (!showSystem && !pattern)
1300                 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1301                                                          "      AND n.nspname <> 'information_schema'\n");
1302
1303         processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1304                                                   "n.nspname", "c.relname", NULL,
1305                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1306
1307         appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
1308
1309         res = PSQLexec(buf.data);
1310         termPQExpBuffer(&buf);
1311         if (!res)
1312                 return false;
1313
1314         if (PQntuples(res) == 0)
1315         {
1316                 if (!pset.quiet)
1317                         psql_error("Did not find any relation named \"%s\".\n",
1318                                            pattern);
1319                 PQclear(res);
1320                 return false;
1321         }
1322
1323         for (i = 0; i < PQntuples(res); i++)
1324         {
1325                 const char *oid;
1326                 const char *nspname;
1327                 const char *relname;
1328
1329                 oid = PQgetvalue(res, i, 0);
1330                 nspname = PQgetvalue(res, i, 1);
1331                 relname = PQgetvalue(res, i, 2);
1332
1333                 if (!describeOneTableDetails(nspname, relname, oid, verbose))
1334                 {
1335                         PQclear(res);
1336                         return false;
1337                 }
1338                 if (cancel_pressed)
1339                 {
1340                         PQclear(res);
1341                         return false;
1342                 }
1343         }
1344
1345         PQclear(res);
1346         return true;
1347 }
1348
1349 /*
1350  * describeOneTableDetails (for \d)
1351  *
1352  * Unfortunately, the information presented here is so complicated that it
1353  * cannot be done in a single query. So we have to assemble the printed table
1354  * by hand and pass it to the underlying printTable() function.
1355  */
1356 static bool
1357 describeOneTableDetails(const char *schemaname,
1358                                                 const char *relationname,
1359                                                 const char *oid,
1360                                                 bool verbose)
1361 {
1362         PQExpBufferData buf;
1363         PGresult   *res = NULL;
1364         printTableOpt myopt = pset.popt.topt;
1365         printTableContent cont;
1366         bool            printTableInitialized = false;
1367         int                     i;
1368         char       *view_def = NULL;
1369         char       *headers[11];
1370         char      **seq_values = NULL;
1371         char      **ptr;
1372         PQExpBufferData title;
1373         PQExpBufferData tmpbuf;
1374         int                     cols;
1375         int                     numrows = 0;
1376         struct
1377         {
1378                 int16           checks;
1379                 char            relkind;
1380                 bool            hasindex;
1381                 bool            hasrules;
1382                 bool            hastriggers;
1383                 bool            rowsecurity;
1384                 bool            forcerowsecurity;
1385                 bool            hasoids;
1386                 Oid                     tablespace;
1387                 char       *reloptions;
1388                 char       *reloftype;
1389                 char            relpersistence;
1390                 char            relreplident;
1391         }                       tableinfo;
1392         bool            show_column_details = false;
1393         bool            retval;
1394
1395         retval = false;
1396
1397         myopt.default_footer = false;
1398         /* This output looks confusing in expanded mode. */
1399         myopt.expanded = false;
1400
1401         initPQExpBuffer(&buf);
1402         initPQExpBuffer(&title);
1403         initPQExpBuffer(&tmpbuf);
1404
1405         /* Get general table info */
1406         if (pset.sversion >= 90500)
1407         {
1408                 printfPQExpBuffer(&buf,
1409                           "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1410                                 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1411                                                   "c.relhasoids, %s, c.reltablespace, "
1412                                                   "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1413                                                   "c.relpersistence, c.relreplident\n"
1414                                                   "FROM pg_catalog.pg_class c\n "
1415                    "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1416                                                   "WHERE c.oid = '%s';",
1417                                                   (verbose ?
1418                                                    "pg_catalog.array_to_string(c.reloptions || "
1419                                                    "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1420                                                    : "''"),
1421                                                   oid);
1422         }
1423         else if (pset.sversion >= 90400)
1424         {
1425                 printfPQExpBuffer(&buf,
1426                           "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1427                                                   "c.relhastriggers, false, false, c.relhasoids, "
1428                                                   "%s, c.reltablespace, "
1429                                                   "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1430                                                   "c.relpersistence, c.relreplident\n"
1431                                                   "FROM pg_catalog.pg_class c\n "
1432                    "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1433                                                   "WHERE c.oid = '%s';",
1434                                                   (verbose ?
1435                                                    "pg_catalog.array_to_string(c.reloptions || "
1436                                                    "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1437                                                    : "''"),
1438                                                   oid);
1439         }
1440         else if (pset.sversion >= 90100)
1441         {
1442                 printfPQExpBuffer(&buf,
1443                           "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1444                                                   "c.relhastriggers, false, false, c.relhasoids, "
1445                                                   "%s, c.reltablespace, "
1446                                                   "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1447                                                   "c.relpersistence\n"
1448                                                   "FROM pg_catalog.pg_class c\n "
1449                    "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1450                                                   "WHERE c.oid = '%s';",
1451                                                   (verbose ?
1452                                                    "pg_catalog.array_to_string(c.reloptions || "
1453                                                    "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1454                                                    : "''"),
1455                                                   oid);
1456         }
1457         else if (pset.sversion >= 90000)
1458         {
1459                 printfPQExpBuffer(&buf,
1460                           "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1461                                                   "c.relhastriggers, false, false, c.relhasoids, "
1462                                                   "%s, c.reltablespace, "
1463                                                   "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
1464                                                   "FROM pg_catalog.pg_class c\n "
1465                    "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1466                                                   "WHERE c.oid = '%s';",
1467                                                   (verbose ?
1468                                                    "pg_catalog.array_to_string(c.reloptions || "
1469                                                    "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1470                                                    : "''"),
1471                                                   oid);
1472         }
1473         else if (pset.sversion >= 80400)
1474         {
1475                 printfPQExpBuffer(&buf,
1476                           "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1477                                                   "c.relhastriggers, false, false, c.relhasoids, "
1478                                                   "%s, c.reltablespace\n"
1479                                                   "FROM pg_catalog.pg_class c\n "
1480                    "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1481                                                   "WHERE c.oid = '%s';",
1482                                                   (verbose ?
1483                                                    "pg_catalog.array_to_string(c.reloptions || "
1484                                                    "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1485                                                    : "''"),
1486                                                   oid);
1487         }
1488         else if (pset.sversion >= 80200)
1489         {
1490                 printfPQExpBuffer(&buf,
1491                                           "SELECT relchecks, relkind, relhasindex, relhasrules, "
1492                                                   "reltriggers <> 0, false, false, relhasoids, "
1493                                                   "%s, reltablespace\n"
1494                                                   "FROM pg_catalog.pg_class WHERE oid = '%s';",
1495                                                   (verbose ?
1496                                          "pg_catalog.array_to_string(reloptions, E', ')" : "''"),
1497                                                   oid);
1498         }
1499         else if (pset.sversion >= 80000)
1500         {
1501                 printfPQExpBuffer(&buf,
1502                                           "SELECT relchecks, relkind, relhasindex, relhasrules, "
1503                                                   "reltriggers <> 0, false, false, relhasoids, "
1504                                                   "'', reltablespace\n"
1505                                                   "FROM pg_catalog.pg_class WHERE oid = '%s';",
1506                                                   oid);
1507         }
1508         else
1509         {
1510                 printfPQExpBuffer(&buf,
1511                                           "SELECT relchecks, relkind, relhasindex, relhasrules, "
1512                                                   "reltriggers <> 0, false, false, relhasoids, "
1513                                                   "'', ''\n"
1514                                                   "FROM pg_catalog.pg_class WHERE oid = '%s';",
1515                                                   oid);
1516         }
1517
1518         res = PSQLexec(buf.data);
1519         if (!res)
1520                 goto error_return;
1521
1522         /* Did we get anything? */
1523         if (PQntuples(res) == 0)
1524         {
1525                 if (!pset.quiet)
1526                         psql_error("Did not find any relation with OID %s.\n", oid);
1527                 goto error_return;
1528         }
1529
1530         tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1531         tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1532         tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1533         tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1534         tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1535         tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1536         tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
1537         tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1538         tableinfo.reloptions = (pset.sversion >= 80200) ?
1539                 pg_strdup(PQgetvalue(res, 0, 8)) : NULL;
1540         tableinfo.tablespace = (pset.sversion >= 80000) ?
1541                 atooid(PQgetvalue(res, 0, 9)) : 0;
1542         tableinfo.reloftype = (pset.sversion >= 90000 &&
1543                                                    strcmp(PQgetvalue(res, 0, 10), "") != 0) ?
1544                 pg_strdup(PQgetvalue(res, 0, 10)) : NULL;
1545         tableinfo.relpersistence = (pset.sversion >= 90100) ?
1546                 *(PQgetvalue(res, 0, 11)) : 0;
1547         tableinfo.relreplident = (pset.sversion >= 90400) ?
1548                 *(PQgetvalue(res, 0, 12)) : 'd';
1549         PQclear(res);
1550         res = NULL;
1551
1552         /*
1553          * If it's a sequence, fetch its values and store into an array that will
1554          * be used later.
1555          */
1556         if (tableinfo.relkind == RELKIND_SEQUENCE)
1557         {
1558                 printfPQExpBuffer(&buf, "SELECT * FROM %s", fmtId(schemaname));
1559                 /* must be separate because fmtId isn't reentrant */
1560                 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
1561
1562                 res = PSQLexec(buf.data);
1563                 if (!res)
1564                         goto error_return;
1565
1566                 seq_values = pg_malloc((PQnfields(res) + 1) * sizeof(*seq_values));
1567
1568                 for (i = 0; i < PQnfields(res); i++)
1569                         seq_values[i] = pg_strdup(PQgetvalue(res, 0, i));
1570                 seq_values[i] = NULL;
1571
1572                 PQclear(res);
1573                 res = NULL;
1574         }
1575
1576         /*
1577          * Get column info
1578          *
1579          * You need to modify value of "firstvcol" which will be defined below if
1580          * you are adding column(s) preceding to verbose-only columns.
1581          */
1582         printfPQExpBuffer(&buf, "SELECT a.attname,");
1583         appendPQExpBufferStr(&buf, "\n  pg_catalog.format_type(a.atttypid, a.atttypmod),"
1584                                                  "\n  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1585                                                  "\n   FROM pg_catalog.pg_attrdef d"
1586                                                  "\n   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1587                                                  "\n  a.attnotnull, a.attnum,");
1588         if (pset.sversion >= 90100)
1589                 appendPQExpBufferStr(&buf, "\n  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1590                                                          "   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1591         else
1592                 appendPQExpBufferStr(&buf, "\n  NULL AS attcollation");
1593         if (tableinfo.relkind == RELKIND_INDEX)
1594                 appendPQExpBufferStr(&buf, ",\n  pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1595         else
1596                 appendPQExpBufferStr(&buf, ",\n  NULL AS indexdef");
1597         if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1598                 appendPQExpBufferStr(&buf, ",\n  CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1599                                                          "  '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) ||  ' ' || quote_literal(option_value)  FROM "
1600                                                          "  pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1601         else
1602                 appendPQExpBufferStr(&buf, ",\n  NULL AS attfdwoptions");
1603         if (verbose)
1604         {
1605                 appendPQExpBufferStr(&buf, ",\n  a.attstorage");
1606                 appendPQExpBufferStr(&buf, ",\n  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1607
1608                 /*
1609                  * In 9.0+, we have column comments for: relations, views, composite
1610                  * types, and foreign tables (c.f. CommentObject() in comment.c).
1611                  */
1612                 if (tableinfo.relkind == RELKIND_RELATION ||
1613                         tableinfo.relkind == RELKIND_VIEW ||
1614                         tableinfo.relkind == RELKIND_MATVIEW ||
1615                         tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1616                         tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1617                         tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1618                         appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
1619         }
1620
1621         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
1622         appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1623         appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
1624
1625         res = PSQLexec(buf.data);
1626         if (!res)
1627                 goto error_return;
1628         numrows = PQntuples(res);
1629
1630         /* Make title */
1631         switch (tableinfo.relkind)
1632         {
1633                 case RELKIND_RELATION:
1634                         if (tableinfo.relpersistence == 'u')
1635                                 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1636                                                                   schemaname, relationname);
1637                         else
1638                                 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1639                                                                   schemaname, relationname);
1640                         break;
1641                 case RELKIND_VIEW:
1642                         printfPQExpBuffer(&title, _("View \"%s.%s\""),
1643                                                           schemaname, relationname);
1644                         break;
1645                 case RELKIND_MATVIEW:
1646                         if (tableinfo.relpersistence == 'u')
1647                                 printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""),
1648                                                                   schemaname, relationname);
1649                         else
1650                                 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""),
1651                                                                   schemaname, relationname);
1652                         break;
1653                 case RELKIND_SEQUENCE:
1654                         printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1655                                                           schemaname, relationname);
1656                         break;
1657                 case RELKIND_INDEX:
1658                         if (tableinfo.relpersistence == 'u')
1659                                 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
1660                                                                   schemaname, relationname);
1661                         else
1662                                 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1663                                                                   schemaname, relationname);
1664                         break;
1665                 case 's':
1666                         /* not used as of 8.2, but keep it for backwards compatibility */
1667                         printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1668                                                           schemaname, relationname);
1669                         break;
1670                 case RELKIND_TOASTVALUE:
1671                         printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1672                                                           schemaname, relationname);
1673                         break;
1674                 case RELKIND_COMPOSITE_TYPE:
1675                         printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1676                                                           schemaname, relationname);
1677                         break;
1678                 case RELKIND_FOREIGN_TABLE:
1679                         printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
1680                                                           schemaname, relationname);
1681                         break;
1682                 case RELKIND_PARTITIONED_TABLE:
1683                         if (tableinfo.relpersistence == 'u')
1684                                 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1685                                                                   schemaname, relationname);
1686                         else
1687                                 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1688                                                                   schemaname, relationname);
1689                         break;
1690                 default:
1691                         /* untranslated unknown relkind */
1692                         printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1693                                                           tableinfo.relkind, schemaname, relationname);
1694                         break;
1695         }
1696
1697         /* Set the number of columns, and their names */
1698         headers[0] = gettext_noop("Column");
1699         headers[1] = gettext_noop("Type");
1700         cols = 2;
1701
1702         if (tableinfo.relkind == RELKIND_RELATION ||
1703                 tableinfo.relkind == RELKIND_VIEW ||
1704                 tableinfo.relkind == RELKIND_MATVIEW ||
1705                 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1706                 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1707                 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1708         {
1709                 headers[cols++] = gettext_noop("Collation");
1710                 headers[cols++] = gettext_noop("Nullable");
1711                 headers[cols++] = gettext_noop("Default");
1712                 show_column_details = true;
1713         }
1714
1715         if (tableinfo.relkind == RELKIND_SEQUENCE)
1716                 headers[cols++] = gettext_noop("Value");
1717
1718         if (tableinfo.relkind == RELKIND_INDEX)
1719                 headers[cols++] = gettext_noop("Definition");
1720
1721         if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1722                 headers[cols++] = gettext_noop("FDW Options");
1723
1724         if (verbose)
1725         {
1726                 headers[cols++] = gettext_noop("Storage");
1727                 if (tableinfo.relkind == RELKIND_RELATION ||
1728                         tableinfo.relkind == RELKIND_MATVIEW ||
1729                         tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1730                         tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1731                         headers[cols++] = gettext_noop("Stats target");
1732                 /* Column comments, if the relkind supports this feature. */
1733                 if (tableinfo.relkind == RELKIND_RELATION ||
1734                         tableinfo.relkind == RELKIND_VIEW ||
1735                         tableinfo.relkind == RELKIND_MATVIEW ||
1736                         tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1737                         tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1738                         tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1739                         headers[cols++] = gettext_noop("Description");
1740         }
1741
1742         printTableInit(&cont, &myopt, title.data, cols, numrows);
1743         printTableInitialized = true;
1744
1745         for (i = 0; i < cols; i++)
1746                 printTableAddHeader(&cont, headers[i], true, 'l');
1747
1748         /* Get view_def if table is a view or materialized view */
1749         if ((tableinfo.relkind == RELKIND_VIEW ||
1750                  tableinfo.relkind == RELKIND_MATVIEW) && verbose)
1751         {
1752                 PGresult   *result;
1753
1754                 printfPQExpBuffer(&buf,
1755                          "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
1756                                                   oid);
1757                 result = PSQLexec(buf.data);
1758                 if (!result)
1759                         goto error_return;
1760
1761                 if (PQntuples(result) > 0)
1762                         view_def = pg_strdup(PQgetvalue(result, 0, 0));
1763
1764                 PQclear(result);
1765         }
1766
1767         /* Generate table cells to be printed */
1768         for (i = 0; i < numrows; i++)
1769         {
1770                 /* Column */
1771                 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
1772
1773                 /* Type */
1774                 printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
1775
1776                 /* Collation, Nullable, Default */
1777                 if (show_column_details)
1778                 {
1779                         printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
1780
1781                         printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") == 0 ? "not null" : "", false, false);
1782
1783                         /* (note: above we cut off the 'default' string at 128) */
1784                         printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
1785                 }
1786
1787                 /* Value: for sequences only */
1788                 if (tableinfo.relkind == RELKIND_SEQUENCE)
1789                         printTableAddCell(&cont, seq_values[i], false, false);
1790
1791                 /* Expression for index column */
1792                 if (tableinfo.relkind == RELKIND_INDEX)
1793                         printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
1794
1795                 /* FDW options for foreign table column, only for 9.2 or later */
1796                 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1797                         printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
1798
1799                 /* Storage and Description */
1800                 if (verbose)
1801                 {
1802                         int                     firstvcol = 8;
1803                         char       *storage = PQgetvalue(res, i, firstvcol);
1804
1805                         /* these strings are literal in our syntax, so not translated. */
1806                         printTableAddCell(&cont, (storage[0] == 'p' ? "plain" :
1807                                                                           (storage[0] == 'm' ? "main" :
1808                                                                            (storage[0] == 'x' ? "extended" :
1809                                                                                 (storage[0] == 'e' ? "external" :
1810                                                                                  "???")))),
1811                                                           false, false);
1812
1813                         /* Statistics target, if the relkind supports this feature */
1814                         if (tableinfo.relkind == RELKIND_RELATION ||
1815                                 tableinfo.relkind == RELKIND_MATVIEW ||
1816                                 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1817                                 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1818                         {
1819                                 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
1820                                                                   false, false);
1821                         }
1822
1823                         /* Column comments, if the relkind supports this feature. */
1824                         if (tableinfo.relkind == RELKIND_RELATION ||
1825                                 tableinfo.relkind == RELKIND_VIEW ||
1826                                 tableinfo.relkind == RELKIND_MATVIEW ||
1827                                 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1828                                 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1829                                 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1830                                 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
1831                                                                   false, false);
1832                 }
1833         }
1834
1835         /* Make footers */
1836         if (pset.sversion >= 100000)
1837         {
1838                 /* Get the partition information  */
1839                 PGresult   *result;
1840                 char       *parent_name;
1841                 char       *partdef;
1842
1843                 printfPQExpBuffer(&buf,
1844                          "SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
1845                          " FROM pg_catalog.pg_class c"
1846                          " JOIN pg_catalog.pg_inherits"
1847                          " ON c.oid = inhrelid"
1848                          " WHERE c.oid = '%s' AND c.relispartition;", oid);
1849                 result = PSQLexec(buf.data);
1850                 if (!result)
1851                         goto error_return;
1852
1853                 if (PQntuples(result) > 0)
1854                 {
1855                         parent_name = PQgetvalue(result, 0, 0);
1856                         partdef = PQgetvalue(result, 0, 1);
1857                         printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
1858                                                   partdef);
1859                         printTableAddFooter(&cont, tmpbuf.data);
1860                         PQclear(result);
1861                 }
1862         }
1863
1864         if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1865         {
1866                 /* Get the partition key information  */
1867                 PGresult   *result;
1868                 char       *partkeydef;
1869
1870                 printfPQExpBuffer(&buf,
1871                          "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
1872                                                   oid);
1873                 result = PSQLexec(buf.data);
1874                 if (!result || PQntuples(result) != 1)
1875                         goto error_return;
1876
1877                 partkeydef = PQgetvalue(result, 0, 0);
1878                 printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
1879                 printTableAddFooter(&cont, tmpbuf.data);
1880                 PQclear(result);
1881         }
1882
1883         if (tableinfo.relkind == RELKIND_INDEX)
1884         {
1885                 /* Footer information about an index */
1886                 PGresult   *result;
1887
1888                 printfPQExpBuffer(&buf,
1889                                  "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1890                 if (pset.sversion >= 80200)
1891                         appendPQExpBufferStr(&buf, "i.indisvalid,\n");
1892                 else
1893                         appendPQExpBufferStr(&buf, "true AS indisvalid,\n");
1894                 if (pset.sversion >= 90000)
1895                         appendPQExpBufferStr(&buf,
1896                                                                  "  (NOT i.indimmediate) AND "
1897                                                         "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1898                                                                  "WHERE conrelid = i.indrelid AND "
1899                                                                  "conindid = i.indexrelid AND "
1900                                                                  "contype IN ('p','u','x') AND "
1901                                                                  "condeferrable) AS condeferrable,\n"
1902                                                                  "  (NOT i.indimmediate) AND "
1903                                                         "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1904                                                                  "WHERE conrelid = i.indrelid AND "
1905                                                                  "conindid = i.indexrelid AND "
1906                                                                  "contype IN ('p','u','x') AND "
1907                                                                  "condeferred) AS condeferred,\n");
1908                 else
1909                         appendPQExpBufferStr(&buf,
1910                                                 "  false AS condeferrable, false AS condeferred,\n");
1911
1912                 if (pset.sversion >= 90400)
1913                         appendPQExpBuffer(&buf, "i.indisreplident,\n");
1914                 else
1915                         appendPQExpBuffer(&buf, "false AS indisreplident,\n");
1916
1917                 appendPQExpBuffer(&buf, "  a.amname, c2.relname, "
1918                                           "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1919                                                   "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1920                   "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1921                                                   "AND i.indrelid = c2.oid;",
1922                                                   oid);
1923
1924                 result = PSQLexec(buf.data);
1925                 if (!result)
1926                         goto error_return;
1927                 else if (PQntuples(result) != 1)
1928                 {
1929                         PQclear(result);
1930                         goto error_return;
1931                 }
1932                 else
1933                 {
1934                         char       *indisunique = PQgetvalue(result, 0, 0);
1935                         char       *indisprimary = PQgetvalue(result, 0, 1);
1936                         char       *indisclustered = PQgetvalue(result, 0, 2);
1937                         char       *indisvalid = PQgetvalue(result, 0, 3);
1938                         char       *deferrable = PQgetvalue(result, 0, 4);
1939                         char       *deferred = PQgetvalue(result, 0, 5);
1940                         char       *indisreplident = PQgetvalue(result, 0, 6);
1941                         char       *indamname = PQgetvalue(result, 0, 7);
1942                         char       *indtable = PQgetvalue(result, 0, 8);
1943                         char       *indpred = PQgetvalue(result, 0, 9);
1944
1945                         if (strcmp(indisprimary, "t") == 0)
1946                                 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1947                         else if (strcmp(indisunique, "t") == 0)
1948                                 printfPQExpBuffer(&tmpbuf, _("unique, "));
1949                         else
1950                                 resetPQExpBuffer(&tmpbuf);
1951                         appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1952
1953                         /* we assume here that index and table are in same schema */
1954                         appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1955                                                           schemaname, indtable);
1956
1957                         if (strlen(indpred))
1958                                 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1959
1960                         if (strcmp(indisclustered, "t") == 0)
1961                                 appendPQExpBufferStr(&tmpbuf, _(", clustered"));
1962
1963                         if (strcmp(indisvalid, "t") != 0)
1964                                 appendPQExpBufferStr(&tmpbuf, _(", invalid"));
1965
1966                         if (strcmp(deferrable, "t") == 0)
1967                                 appendPQExpBufferStr(&tmpbuf, _(", deferrable"));
1968
1969                         if (strcmp(deferred, "t") == 0)
1970                                 appendPQExpBufferStr(&tmpbuf, _(", initially deferred"));
1971
1972                         if (strcmp(indisreplident, "t") == 0)
1973                                 appendPQExpBuffer(&tmpbuf, _(", replica identity"));
1974
1975                         printTableAddFooter(&cont, tmpbuf.data);
1976                         add_tablespace_footer(&cont, tableinfo.relkind,
1977                                                                   tableinfo.tablespace, true);
1978                 }
1979
1980                 PQclear(result);
1981         }
1982         else if (tableinfo.relkind == RELKIND_SEQUENCE)
1983         {
1984                 /* Footer information about a sequence */
1985                 PGresult   *result = NULL;
1986
1987                 /* Get the column that owns this sequence */
1988                 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1989                                                   "\n   pg_catalog.quote_ident(relname) || '.' ||"
1990                                                   "\n   pg_catalog.quote_ident(attname)"
1991                                                   "\nFROM pg_catalog.pg_class c"
1992                                         "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1993                          "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1994                                                   "\nINNER JOIN pg_catalog.pg_attribute a ON ("
1995                                                   "\n a.attrelid=c.oid AND"
1996                                                   "\n a.attnum=d.refobjsubid)"
1997                            "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1998                          "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1999                                                   "\n AND d.objid=%s"
2000                                                   "\n AND d.deptype='a'",
2001                                                   oid);
2002
2003                 result = PSQLexec(buf.data);
2004                 if (!result)
2005                         goto error_return;
2006                 else if (PQntuples(result) == 1)
2007                 {
2008                         printfPQExpBuffer(&buf, _("Owned by: %s"),
2009                                                           PQgetvalue(result, 0, 0));
2010                         printTableAddFooter(&cont, buf.data);
2011                 }
2012
2013                 /*
2014                  * If we get no rows back, don't show anything (obviously). We should
2015                  * never get more than one row back, but if we do, just ignore it and
2016                  * don't print anything.
2017                  */
2018                 PQclear(result);
2019         }
2020         else if (tableinfo.relkind == RELKIND_RELATION ||
2021                          tableinfo.relkind == RELKIND_MATVIEW ||
2022                          tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2023                          tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2024         {
2025                 /* Footer information about a table */
2026                 PGresult   *result = NULL;
2027                 int                     tuples = 0;
2028
2029                 /* print indexes */
2030                 if (tableinfo.hasindex)
2031                 {
2032                         printfPQExpBuffer(&buf,
2033                                                           "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
2034                         if (pset.sversion >= 80200)
2035                                 appendPQExpBufferStr(&buf, "i.indisvalid, ");
2036                         else
2037                                 appendPQExpBufferStr(&buf, "true as indisvalid, ");
2038                         appendPQExpBufferStr(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n  ");
2039                         if (pset.sversion >= 90000)
2040                                 appendPQExpBufferStr(&buf,
2041                                                    "pg_catalog.pg_get_constraintdef(con.oid, true), "
2042                                                                          "contype, condeferrable, condeferred");
2043                         else
2044                                 appendPQExpBufferStr(&buf,
2045                                                                    "null AS constraintdef, null AS contype, "
2046                                                          "false AS condeferrable, false AS condeferred");
2047                         if (pset.sversion >= 90400)
2048                                 appendPQExpBufferStr(&buf, ", i.indisreplident");
2049                         else
2050                                 appendPQExpBufferStr(&buf, ", false AS indisreplident");
2051                         if (pset.sversion >= 80000)
2052                                 appendPQExpBufferStr(&buf, ", c2.reltablespace");
2053                         appendPQExpBufferStr(&buf,
2054                                                                  "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
2055                         if (pset.sversion >= 90000)
2056                                 appendPQExpBufferStr(&buf,
2057                                                                          "  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n");
2058                         appendPQExpBuffer(&buf,
2059                                                           "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2060                          "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;",
2061                                                           oid);
2062                         result = PSQLexec(buf.data);
2063                         if (!result)
2064                                 goto error_return;
2065                         else
2066                                 tuples = PQntuples(result);
2067
2068                         if (tuples > 0)
2069                         {
2070                                 printTableAddFooter(&cont, _("Indexes:"));
2071                                 for (i = 0; i < tuples; i++)
2072                                 {
2073                                         /* untranslated index name */
2074                                         printfPQExpBuffer(&buf, "    \"%s\"",
2075                                                                           PQgetvalue(result, i, 0));
2076
2077                                         /* If exclusion constraint, print the constraintdef */
2078                                         if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
2079                                         {
2080                                                 appendPQExpBuffer(&buf, " %s",
2081                                                                                   PQgetvalue(result, i, 6));
2082                                         }
2083                                         else
2084                                         {
2085                                                 const char *indexdef;
2086                                                 const char *usingpos;
2087
2088                                                 /* Label as primary key or unique (but not both) */
2089                                                 if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
2090                                                         appendPQExpBufferStr(&buf, " PRIMARY KEY,");
2091                                                 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
2092                                                 {
2093                                                         if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
2094                                                                 appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
2095                                                         else
2096                                                                 appendPQExpBufferStr(&buf, " UNIQUE,");
2097                                                 }
2098
2099                                                 /* Everything after "USING" is echoed verbatim */
2100                                                 indexdef = PQgetvalue(result, i, 5);
2101                                                 usingpos = strstr(indexdef, " USING ");
2102                                                 if (usingpos)
2103                                                         indexdef = usingpos + 7;
2104                                                 appendPQExpBuffer(&buf, " %s", indexdef);
2105
2106                                                 /* Need these for deferrable PK/UNIQUE indexes */
2107                                                 if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
2108                                                         appendPQExpBufferStr(&buf, " DEFERRABLE");
2109
2110                                                 if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
2111                                                         appendPQExpBufferStr(&buf, " INITIALLY DEFERRED");
2112                                         }
2113
2114                                         /* Add these for all cases */
2115                                         if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
2116                                                 appendPQExpBufferStr(&buf, " CLUSTER");
2117
2118                                         if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
2119                                                 appendPQExpBufferStr(&buf, " INVALID");
2120
2121                                         if (strcmp(PQgetvalue(result, i, 10), "t") == 0)
2122                                                 appendPQExpBuffer(&buf, " REPLICA IDENTITY");
2123
2124                                         printTableAddFooter(&cont, buf.data);
2125
2126                                         /* Print tablespace of the index on the same line */
2127                                         if (pset.sversion >= 80000)
2128                                                 add_tablespace_footer(&cont, RELKIND_INDEX,
2129                                                                                    atooid(PQgetvalue(result, i, 11)),
2130                                                                                           false);
2131                                 }
2132                         }
2133                         PQclear(result);
2134                 }
2135
2136                 /* print table (and column) check constraints */
2137                 if (tableinfo.checks)
2138                 {
2139                         printfPQExpBuffer(&buf,
2140                                                           "SELECT r.conname, "
2141                                                           "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2142                                                           "FROM pg_catalog.pg_constraint r\n"
2143                                                           "WHERE r.conrelid = '%s' AND r.contype = 'c'\n"
2144                                                           "ORDER BY 1;",
2145                                                           oid);
2146                         result = PSQLexec(buf.data);
2147                         if (!result)
2148                                 goto error_return;
2149                         else
2150                                 tuples = PQntuples(result);
2151
2152                         if (tuples > 0)
2153                         {
2154                                 printTableAddFooter(&cont, _("Check constraints:"));
2155                                 for (i = 0; i < tuples; i++)
2156                                 {
2157                                         /* untranslated constraint name and def */
2158                                         printfPQExpBuffer(&buf, "    \"%s\" %s",
2159                                                                           PQgetvalue(result, i, 0),
2160                                                                           PQgetvalue(result, i, 1));
2161
2162                                         printTableAddFooter(&cont, buf.data);
2163                                 }
2164                         }
2165                         PQclear(result);
2166                 }
2167
2168                 /* print foreign-key constraints (there are none if no triggers) */
2169                 if (tableinfo.hastriggers)
2170                 {
2171                         printfPQExpBuffer(&buf,
2172                                                           "SELECT conname,\n"
2173                                  "  pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
2174                                                           "FROM pg_catalog.pg_constraint r\n"
2175                                    "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;",
2176                                                           oid);
2177                         result = PSQLexec(buf.data);
2178                         if (!result)
2179                                 goto error_return;
2180                         else
2181                                 tuples = PQntuples(result);
2182
2183                         if (tuples > 0)
2184                         {
2185                                 printTableAddFooter(&cont, _("Foreign-key constraints:"));
2186                                 for (i = 0; i < tuples; i++)
2187                                 {
2188                                         /* untranslated constraint name and def */
2189                                         printfPQExpBuffer(&buf, "    \"%s\" %s",
2190                                                                           PQgetvalue(result, i, 0),
2191                                                                           PQgetvalue(result, i, 1));
2192
2193                                         printTableAddFooter(&cont, buf.data);
2194                                 }
2195                         }
2196                         PQclear(result);
2197                 }
2198
2199                 /* print incoming foreign-key references (none if no triggers) */
2200                 if (tableinfo.hastriggers)
2201                 {
2202                         printfPQExpBuffer(&buf,
2203                                                    "SELECT conname, conrelid::pg_catalog.regclass,\n"
2204                                  "  pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
2205                                                           "FROM pg_catalog.pg_constraint c\n"
2206                                   "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;",
2207                                                           oid);
2208                         result = PSQLexec(buf.data);
2209                         if (!result)
2210                                 goto error_return;
2211                         else
2212                                 tuples = PQntuples(result);
2213
2214                         if (tuples > 0)
2215                         {
2216                                 printTableAddFooter(&cont, _("Referenced by:"));
2217                                 for (i = 0; i < tuples; i++)
2218                                 {
2219                                         printfPQExpBuffer(&buf, "    TABLE \"%s\" CONSTRAINT \"%s\" %s",
2220                                                                           PQgetvalue(result, i, 1),
2221                                                                           PQgetvalue(result, i, 0),
2222                                                                           PQgetvalue(result, i, 2));
2223
2224                                         printTableAddFooter(&cont, buf.data);
2225                                 }
2226                         }
2227                         PQclear(result);
2228                 }
2229
2230                 /* print any row-level policies */
2231                 if (pset.sversion >= 90500)
2232                 {
2233                         if (pset.sversion >= 100000)
2234                                 printfPQExpBuffer(&buf,
2235                                                                   "SELECT pol.polname, pol.polpermissive,\n"
2236                                                                   "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2237                                                    "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2238                                           "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2239                                                                   "CASE pol.polcmd \n"
2240                                                                   "WHEN 'r' THEN 'SELECT'\n"
2241                                                                   "WHEN 'a' THEN 'INSERT'\n"
2242                                                                   "WHEN 'w' THEN 'UPDATE'\n"
2243                                                                   "WHEN 'd' THEN 'DELETE'\n"
2244                                                                   "END AS cmd\n"
2245                                                                   "FROM pg_catalog.pg_policy pol\n"
2246                                                                   "WHERE pol.polrelid = '%s' ORDER BY 1;",
2247                                                                   oid);
2248                         else
2249                                 printfPQExpBuffer(&buf,
2250                                                                   "SELECT pol.polname, 't' as polpermissive,\n"
2251                                                                   "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2252                                                    "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2253                                           "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2254                                                                   "CASE pol.polcmd \n"
2255                                                                   "WHEN 'r' THEN 'SELECT'\n"
2256                                                                   "WHEN 'a' THEN 'INSERT'\n"
2257                                                                   "WHEN 'w' THEN 'UPDATE'\n"
2258                                                                   "WHEN 'd' THEN 'DELETE'\n"
2259                                                                   "END AS cmd\n"
2260                                                                   "FROM pg_catalog.pg_policy pol\n"
2261                                                                   "WHERE pol.polrelid = '%s' ORDER BY 1;",
2262                                                                   oid);
2263
2264                         result = PSQLexec(buf.data);
2265                         if (!result)
2266                                 goto error_return;
2267                         else
2268                                 tuples = PQntuples(result);
2269
2270                         /*
2271                          * Handle cases where RLS is enabled and there are policies, or
2272                          * there aren't policies, or RLS isn't enabled but there are
2273                          * policies
2274                          */
2275                         if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0)
2276                                 printTableAddFooter(&cont, _("Policies:"));
2277
2278                         if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0)
2279                                 printTableAddFooter(&cont, _("Policies (forced row security enabled):"));
2280
2281                         if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0)
2282                                 printTableAddFooter(&cont, _("Policies (row security enabled): (none)"));
2283
2284                         if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0)
2285                                 printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)"));
2286
2287                         if (!tableinfo.rowsecurity && tuples > 0)
2288                                 printTableAddFooter(&cont, _("Policies (row security disabled):"));
2289
2290                         /* Might be an empty set - that's ok */
2291                         for (i = 0; i < tuples; i++)
2292                         {
2293                                 printfPQExpBuffer(&buf, "    POLICY \"%s\"",
2294                                                                   PQgetvalue(result, i, 0));
2295
2296                                 if (*(PQgetvalue(result, i, 1)) == 'f')
2297                                         appendPQExpBuffer(&buf, " AS RESTRICTIVE");
2298
2299                                 if (!PQgetisnull(result, i, 5))
2300                                         appendPQExpBuffer(&buf, " FOR %s",
2301                                                                           PQgetvalue(result, i, 5));
2302
2303                                 if (!PQgetisnull(result, i, 2))
2304                                 {
2305                                         appendPQExpBuffer(&buf, "\n      TO %s",
2306                                                                           PQgetvalue(result, i, 2));
2307                                 }
2308
2309                                 if (!PQgetisnull(result, i, 3))
2310                                         appendPQExpBuffer(&buf, "\n      USING (%s)",
2311                                                                           PQgetvalue(result, i, 3));
2312
2313                                 if (!PQgetisnull(result, i, 4))
2314                                         appendPQExpBuffer(&buf, "\n      WITH CHECK (%s)",
2315                                                                           PQgetvalue(result, i, 4));
2316
2317                                 printTableAddFooter(&cont, buf.data);
2318
2319                         }
2320                         PQclear(result);
2321                 }
2322
2323                 /* print any extended statistics */
2324                 if (pset.sversion >= 100000)
2325                 {
2326                         printfPQExpBuffer(&buf,
2327                                                           "SELECT oid, stanamespace::regnamespace AS nsp, staname, stakeys,\n"
2328                                                           "  (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname::text),', ') \n"
2329                                                    "    FROM ((SELECT pg_catalog.unnest(stakeys) AS attnum) s\n"
2330                            "         JOIN pg_catalog.pg_attribute a ON (starelid = a.attrelid AND\n"
2331                                                           "a.attnum = s.attnum AND not attisdropped))) AS columns,\n"
2332                                                           "  (staenabled::char[] @> '{d}'::char[]) AS ndist_enabled\n"
2333                           "FROM pg_catalog.pg_statistic_ext stat WHERE starelid  = '%s'\n"
2334                           "ORDER BY 1;",
2335                                                           oid);
2336
2337                         result = PSQLexec(buf.data);
2338                         if (!result)
2339                                 goto error_return;
2340                         else
2341                                 tuples = PQntuples(result);
2342
2343                         if (tuples > 0)
2344                         {
2345                                 printTableAddFooter(&cont, _("Statistics:"));
2346
2347                                 for (i = 0; i < tuples; i++)
2348                                 {
2349                                         int             cnt = 0;
2350
2351                                         printfPQExpBuffer(&buf, "    ");
2352
2353                                         /* statistics name (qualified with namespace) */
2354                                         appendPQExpBuffer(&buf, "\"%s.%s\" WITH (",
2355                                                                           PQgetvalue(result, i, 1),
2356                                                                           PQgetvalue(result, i, 2));
2357
2358                                         /* options */
2359                                         if (strcmp(PQgetvalue(result, i, 5), "t") == 0)
2360                                         {
2361                                                 appendPQExpBufferStr(&buf, "ndistinct");
2362                                                 cnt++;
2363                                         }
2364
2365                                         appendPQExpBuffer(&buf, ") ON (%s)",
2366                                                                           PQgetvalue(result, i, 4));
2367
2368                                         printTableAddFooter(&cont, buf.data);
2369                                 }
2370                         }
2371                         PQclear(result);
2372                 }
2373
2374                 /* print rules */
2375                 if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW)
2376                 {
2377                         if (pset.sversion >= 80300)
2378                         {
2379                                 printfPQExpBuffer(&buf,
2380                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2381                                                                   "ev_enabled\n"
2382                                                                   "FROM pg_catalog.pg_rewrite r\n"
2383                                                                   "WHERE r.ev_class = '%s' ORDER BY 1;",
2384                                                                   oid);
2385                         }
2386                         else
2387                         {
2388                                 printfPQExpBuffer(&buf,
2389                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2390                                                                   "'O'::char AS ev_enabled\n"
2391                                                                   "FROM pg_catalog.pg_rewrite r\n"
2392                                                                   "WHERE r.ev_class = '%s' ORDER BY 1;",
2393                                                                   oid);
2394                         }
2395                         result = PSQLexec(buf.data);
2396                         if (!result)
2397                                 goto error_return;
2398                         else
2399                                 tuples = PQntuples(result);
2400
2401                         if (tuples > 0)
2402                         {
2403                                 bool            have_heading;
2404                                 int                     category;
2405
2406                                 for (category = 0; category < 4; category++)
2407                                 {
2408                                         have_heading = false;
2409
2410                                         for (i = 0; i < tuples; i++)
2411                                         {
2412                                                 const char *ruledef;
2413                                                 bool            list_rule = false;
2414
2415                                                 switch (category)
2416                                                 {
2417                                                         case 0:
2418                                                                 if (*PQgetvalue(result, i, 2) == 'O')
2419                                                                         list_rule = true;
2420                                                                 break;
2421                                                         case 1:
2422                                                                 if (*PQgetvalue(result, i, 2) == 'D')
2423                                                                         list_rule = true;
2424                                                                 break;
2425                                                         case 2:
2426                                                                 if (*PQgetvalue(result, i, 2) == 'A')
2427                                                                         list_rule = true;
2428                                                                 break;
2429                                                         case 3:
2430                                                                 if (*PQgetvalue(result, i, 2) == 'R')
2431                                                                         list_rule = true;
2432                                                                 break;
2433                                                 }
2434                                                 if (!list_rule)
2435                                                         continue;
2436
2437                                                 if (!have_heading)
2438                                                 {
2439                                                         switch (category)
2440                                                         {
2441                                                                 case 0:
2442                                                                         printfPQExpBuffer(&buf, _("Rules:"));
2443                                                                         break;
2444                                                                 case 1:
2445                                                                         printfPQExpBuffer(&buf, _("Disabled rules:"));
2446                                                                         break;
2447                                                                 case 2:
2448                                                                         printfPQExpBuffer(&buf, _("Rules firing always:"));
2449                                                                         break;
2450                                                                 case 3:
2451                                                                         printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2452                                                                         break;
2453                                                         }
2454                                                         printTableAddFooter(&cont, buf.data);
2455                                                         have_heading = true;
2456                                                 }
2457
2458                                                 /* Everything after "CREATE RULE" is echoed verbatim */
2459                                                 ruledef = PQgetvalue(result, i, 1);
2460                                                 ruledef += 12;
2461                                                 printfPQExpBuffer(&buf, "    %s", ruledef);
2462                                                 printTableAddFooter(&cont, buf.data);
2463                                         }
2464                                 }
2465                         }
2466                         PQclear(result);
2467                 }
2468
2469                 /* print any publications */
2470                 if (pset.sversion >= 100000)
2471                 {
2472                         printfPQExpBuffer(&buf,
2473                                                           "SELECT pub.pubname\n"
2474                                                           " FROM pg_catalog.pg_publication pub\n"
2475                                                           " LEFT JOIN pg_catalog.pg_publication_rel pr\n"
2476                                                           "      ON (pr.prpubid = pub.oid)\n"
2477                                                           "WHERE pr.prrelid = '%s' OR pub.puballtables\n"
2478                                                           "ORDER BY 1;",
2479                                                           oid);
2480
2481                         result = PSQLexec(buf.data);
2482                         if (!result)
2483                                 goto error_return;
2484                         else
2485                                 tuples = PQntuples(result);
2486
2487                         if (tuples > 0)
2488                                 printTableAddFooter(&cont, _("Publications:"));
2489
2490                         /* Might be an empty set - that's ok */
2491                         for (i = 0; i < tuples; i++)
2492                         {
2493                                 printfPQExpBuffer(&buf, "    \"%s\"",
2494                                                                   PQgetvalue(result, i, 0));
2495
2496                                 printTableAddFooter(&cont, buf.data);
2497                         }
2498                         PQclear(result);
2499                 }
2500         }
2501
2502         if (view_def)
2503         {
2504                 PGresult   *result = NULL;
2505
2506                 /* Footer information about a view */
2507                 printTableAddFooter(&cont, _("View definition:"));
2508                 printTableAddFooter(&cont, view_def);
2509
2510                 /* print rules */
2511                 if (tableinfo.hasrules)
2512                 {
2513                         printfPQExpBuffer(&buf,
2514                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
2515                                                           "FROM pg_catalog.pg_rewrite r\n"
2516                         "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
2517                                                           oid);
2518                         result = PSQLexec(buf.data);
2519                         if (!result)
2520                                 goto error_return;
2521
2522                         if (PQntuples(result) > 0)
2523                         {
2524                                 printTableAddFooter(&cont, _("Rules:"));
2525                                 for (i = 0; i < PQntuples(result); i++)
2526                                 {
2527                                         const char *ruledef;
2528
2529                                         /* Everything after "CREATE RULE" is echoed verbatim */
2530                                         ruledef = PQgetvalue(result, i, 1);
2531                                         ruledef += 12;
2532
2533                                         printfPQExpBuffer(&buf, " %s", ruledef);
2534                                         printTableAddFooter(&cont, buf.data);
2535                                 }
2536                         }
2537                         PQclear(result);
2538                 }
2539         }
2540
2541         /*
2542          * Print triggers next, if any (but only user-defined triggers).  This
2543          * could apply to either a table or a view.
2544          */
2545         if (tableinfo.hastriggers)
2546         {
2547                 PGresult   *result;
2548                 int                     tuples;
2549
2550                 printfPQExpBuffer(&buf,
2551                                                   "SELECT t.tgname, "
2552                                                   "pg_catalog.pg_get_triggerdef(t.oid%s), "
2553                                                   "t.tgenabled, %s\n"
2554                                                   "FROM pg_catalog.pg_trigger t\n"
2555                                                   "WHERE t.tgrelid = '%s' AND ",
2556                                                   (pset.sversion >= 90000 ? ", true" : ""),
2557                                                   (pset.sversion >= 90000 ? "t.tgisinternal" :
2558                                                    pset.sversion >= 80300 ?
2559                                                    "t.tgconstraint <> 0 AS tgisinternal" :
2560                                                    "false AS tgisinternal"), oid);
2561                 if (pset.sversion >= 90000)
2562                         /* display/warn about disabled internal triggers */
2563                         appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
2564                 else if (pset.sversion >= 80300)
2565                         appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))");
2566                 else
2567                         appendPQExpBufferStr(&buf,
2568                                                                  "(NOT tgisconstraint "
2569                                                                  " OR NOT EXISTS"
2570                                                                  "  (SELECT 1 FROM pg_catalog.pg_depend d "
2571                                                                  "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
2572                                                                  "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
2573                 appendPQExpBufferStr(&buf, "\nORDER BY 1;");
2574
2575                 result = PSQLexec(buf.data);
2576                 if (!result)
2577                         goto error_return;
2578                 else
2579                         tuples = PQntuples(result);
2580
2581                 if (tuples > 0)
2582                 {
2583                         bool            have_heading;
2584                         int                     category;
2585
2586                         /*
2587                          * split the output into 4 different categories. Enabled triggers,
2588                          * disabled triggers and the two special ALWAYS and REPLICA
2589                          * configurations.
2590                          */
2591                         for (category = 0; category <= 4; category++)
2592                         {
2593                                 have_heading = false;
2594                                 for (i = 0; i < tuples; i++)
2595                                 {
2596                                         bool            list_trigger;
2597                                         const char *tgdef;
2598                                         const char *usingpos;
2599                                         const char *tgenabled;
2600                                         const char *tgisinternal;
2601
2602                                         /*
2603                                          * Check if this trigger falls into the current category
2604                                          */
2605                                         tgenabled = PQgetvalue(result, i, 2);
2606                                         tgisinternal = PQgetvalue(result, i, 3);
2607                                         list_trigger = false;
2608                                         switch (category)
2609                                         {
2610                                                 case 0:
2611                                                         if (*tgenabled == 'O' || *tgenabled == 't')
2612                                                                 list_trigger = true;
2613                                                         break;
2614                                                 case 1:
2615                                                         if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2616                                                                 *tgisinternal == 'f')
2617                                                                 list_trigger = true;
2618                                                         break;
2619                                                 case 2:
2620                                                         if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2621                                                                 *tgisinternal == 't')
2622                                                                 list_trigger = true;
2623                                                         break;
2624                                                 case 3:
2625                                                         if (*tgenabled == 'A')
2626                                                                 list_trigger = true;
2627                                                         break;
2628                                                 case 4:
2629                                                         if (*tgenabled == 'R')
2630                                                                 list_trigger = true;
2631                                                         break;
2632                                         }
2633                                         if (list_trigger == false)
2634                                                 continue;
2635
2636                                         /* Print the category heading once */
2637                                         if (have_heading == false)
2638                                         {
2639                                                 switch (category)
2640                                                 {
2641                                                         case 0:
2642                                                                 printfPQExpBuffer(&buf, _("Triggers:"));
2643                                                                 break;
2644                                                         case 1:
2645                                                                 if (pset.sversion >= 80300)
2646                                                                         printfPQExpBuffer(&buf, _("Disabled user triggers:"));
2647                                                                 else
2648                                                                         printfPQExpBuffer(&buf, _("Disabled triggers:"));
2649                                                                 break;
2650                                                         case 2:
2651                                                                 printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
2652                                                                 break;
2653                                                         case 3:
2654                                                                 printfPQExpBuffer(&buf, _("Triggers firing always:"));
2655                                                                 break;
2656                                                         case 4:
2657                                                                 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
2658                                                                 break;
2659
2660                                                 }
2661                                                 printTableAddFooter(&cont, buf.data);
2662                                                 have_heading = true;
2663                                         }
2664
2665                                         /* Everything after "TRIGGER" is echoed verbatim */
2666                                         tgdef = PQgetvalue(result, i, 1);
2667                                         usingpos = strstr(tgdef, " TRIGGER ");
2668                                         if (usingpos)
2669                                                 tgdef = usingpos + 9;
2670
2671                                         printfPQExpBuffer(&buf, "    %s", tgdef);
2672                                         printTableAddFooter(&cont, buf.data);
2673                                 }
2674                         }
2675                 }
2676                 PQclear(result);
2677         }
2678
2679         /*
2680          * Finish printing the footer information about a table.
2681          */
2682         if (tableinfo.relkind == RELKIND_RELATION ||
2683                 tableinfo.relkind == RELKIND_MATVIEW ||
2684                 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2685                 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2686         {
2687                 PGresult   *result;
2688                 int                     tuples;
2689
2690                 /* print foreign server name */
2691                 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
2692                 {
2693                         char       *ftoptions;
2694
2695                         /* Footer information about foreign table */
2696                         printfPQExpBuffer(&buf,
2697                                                           "SELECT s.srvname,\n"
2698                                                           "       array_to_string(ARRAY(SELECT "
2699                                                           "       quote_ident(option_name) ||  ' ' || "
2700                                                           "       quote_literal(option_value)  FROM "
2701                                                         "       pg_options_to_table(ftoptions)),  ', ') "
2702                                                           "FROM pg_catalog.pg_foreign_table f,\n"
2703                                                           "     pg_catalog.pg_foreign_server s\n"
2704                                                           "WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2705                                                           oid);
2706                         result = PSQLexec(buf.data);
2707                         if (!result)
2708                                 goto error_return;
2709                         else if (PQntuples(result) != 1)
2710                         {
2711                                 PQclear(result);
2712                                 goto error_return;
2713                         }
2714
2715                         /* Print server name */
2716                         printfPQExpBuffer(&buf, "Server: %s",
2717                                                           PQgetvalue(result, 0, 0));
2718                         printTableAddFooter(&cont, buf.data);
2719
2720                         /* Print per-table FDW options, if any */
2721                         ftoptions = PQgetvalue(result, 0, 1);
2722                         if (ftoptions && ftoptions[0] != '\0')
2723                         {
2724                                 printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
2725                                 printTableAddFooter(&cont, buf.data);
2726                         }
2727                         PQclear(result);
2728                 }
2729
2730                 /* print inherited tables (exclude, if parent is a partitioned table) */
2731                 printfPQExpBuffer(&buf,
2732                                 "SELECT c.oid::pg_catalog.regclass"
2733                                 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2734                                 " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'"
2735                                 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE)
2736                                 " ORDER BY inhseqno;", oid);
2737
2738                 result = PSQLexec(buf.data);
2739                 if (!result)
2740                         goto error_return;
2741                 else
2742                 {
2743                         const char *s = _("Inherits");
2744                         int                     sw = pg_wcswidth(s, strlen(s), pset.encoding);
2745
2746                         tuples = PQntuples(result);
2747
2748                         for (i = 0; i < tuples; i++)
2749                         {
2750                                 if (i == 0)
2751                                         printfPQExpBuffer(&buf, "%s: %s",
2752                                                                           s, PQgetvalue(result, i, 0));
2753                                 else
2754                                         printfPQExpBuffer(&buf, "%*s  %s",
2755                                                                           sw, "", PQgetvalue(result, i, 0));
2756                                 if (i < tuples - 1)
2757                                         appendPQExpBufferChar(&buf, ',');
2758
2759                                 printTableAddFooter(&cont, buf.data);
2760                         }
2761
2762                         PQclear(result);
2763                 }
2764
2765                 /* print child tables (with additional info if partitions) */
2766                 if (pset.sversion >= 100000)
2767                         printfPQExpBuffer(&buf,
2768                                         "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)"
2769                                         " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2770                                         " WHERE c.oid=i.inhrelid AND"
2771                                         " i.inhparent = '%s' AND"
2772                                         " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2773                                         " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid);
2774                 else if (pset.sversion >= 80300)
2775                         printfPQExpBuffer(&buf,
2776                                         "SELECT c.oid::pg_catalog.regclass"
2777                                         " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2778                                         " WHERE c.oid=i.inhrelid AND"
2779                                         " i.inhparent = '%s' AND"
2780                                         " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2781                                         " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid);
2782                 else
2783                         printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid);
2784
2785                 result = PSQLexec(buf.data);
2786                 if (!result)
2787                         goto error_return;
2788                 else
2789                         tuples = PQntuples(result);
2790
2791                 if (!verbose)
2792                 {
2793                         /* print the number of child tables, if any */
2794                         if (tuples > 0)
2795                         {
2796                                 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
2797                                         printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
2798                                 else
2799                                         printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
2800                                 printTableAddFooter(&cont, buf.data);
2801                         }
2802                 }
2803                 else
2804                 {
2805                         /* display the list of child tables */
2806                         const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ?
2807                                 _("Child tables") : _("Partitions");
2808                         int                     ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
2809
2810                         for (i = 0; i < tuples; i++)
2811                         {
2812                                 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
2813                                 {
2814                                         if (i == 0)
2815                                                 printfPQExpBuffer(&buf, "%s: %s",
2816                                                                                   ct, PQgetvalue(result, i, 0));
2817                                         else
2818                                                 printfPQExpBuffer(&buf, "%*s  %s",
2819                                                                                   ctw, "", PQgetvalue(result, i, 0));
2820                                 }
2821                                 else
2822                                 {
2823                                         if (i == 0)
2824                                                 printfPQExpBuffer(&buf, "%s: %s %s",
2825                                                                                   ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
2826                                         else
2827                                                 printfPQExpBuffer(&buf, "%*s  %s %s",
2828                                                                                   ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
2829                                 }
2830                                 if (i < tuples - 1)
2831                                         appendPQExpBufferChar(&buf, ',');
2832
2833                                 printTableAddFooter(&cont, buf.data);
2834                         }
2835                 }
2836                 PQclear(result);
2837
2838                 /* Table type */
2839                 if (tableinfo.reloftype)
2840                 {
2841                         printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
2842                         printTableAddFooter(&cont, buf.data);
2843                 }
2844
2845                 if (verbose &&
2846                         (tableinfo.relkind == RELKIND_RELATION ||
2847                          tableinfo.relkind == RELKIND_MATVIEW) &&
2848
2849                 /*
2850                  * No need to display default values; we already display a REPLICA
2851                  * IDENTITY marker on indexes.
2852                  */
2853                         tableinfo.relreplident != 'i' &&
2854                         ((strcmp(schemaname, "pg_catalog") != 0 && tableinfo.relreplident != 'd') ||
2855                          (strcmp(schemaname, "pg_catalog") == 0 && tableinfo.relreplident != 'n')))
2856                 {
2857                         const char *s = _("Replica Identity");
2858
2859                         printfPQExpBuffer(&buf, "%s: %s",
2860                                                           s,
2861                                                           tableinfo.relreplident == 'f' ? "FULL" :
2862                                                           tableinfo.relreplident == 'n' ? "NOTHING" :
2863                                                           "???");
2864
2865                         printTableAddFooter(&cont, buf.data);
2866                 }
2867
2868                 /* OIDs, if verbose and not a materialized view */
2869                 if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
2870                         printTableAddFooter(&cont, _("Has OIDs: yes"));
2871
2872                 /* Tablespace info */
2873                 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
2874                                                           true);
2875         }
2876
2877         /* reloptions, if verbose */
2878         if (verbose &&
2879                 tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
2880         {
2881                 const char *t = _("Options");
2882
2883                 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
2884                 printTableAddFooter(&cont, buf.data);
2885         }
2886
2887         printTable(&cont, pset.queryFout, false, pset.logfile);
2888
2889         retval = true;
2890
2891 error_return:
2892
2893         /* clean up */
2894         if (printTableInitialized)
2895                 printTableCleanup(&cont);
2896         termPQExpBuffer(&buf);
2897         termPQExpBuffer(&title);
2898         termPQExpBuffer(&tmpbuf);
2899
2900         if (seq_values)
2901         {
2902                 for (ptr = seq_values; *ptr; ptr++)
2903                         free(*ptr);
2904                 free(seq_values);
2905         }
2906
2907         if (view_def)
2908                 free(view_def);
2909
2910         if (res)
2911                 PQclear(res);
2912
2913         return retval;
2914 }
2915
2916 /*
2917  * Add a tablespace description to a footer.  If 'newline' is true, it is added
2918  * in a new line; otherwise it's appended to the current value of the last
2919  * footer.
2920  */
2921 static void
2922 add_tablespace_footer(printTableContent *const cont, char relkind,
2923                                           Oid tablespace, const bool newline)
2924 {
2925         /* relkinds for which we support tablespaces */
2926         if (relkind == RELKIND_RELATION ||
2927                 relkind == RELKIND_MATVIEW ||
2928                 relkind == RELKIND_INDEX ||
2929                 relkind == RELKIND_PARTITIONED_TABLE)
2930         {
2931                 /*
2932                  * We ignore the database default tablespace so that users not using
2933                  * tablespaces don't need to know about them.  This case also covers
2934                  * pre-8.0 servers, for which tablespace will always be 0.
2935                  */
2936                 if (tablespace != 0)
2937                 {
2938                         PGresult   *result = NULL;
2939                         PQExpBufferData buf;
2940
2941                         initPQExpBuffer(&buf);
2942                         printfPQExpBuffer(&buf,
2943                                                           "SELECT spcname FROM pg_catalog.pg_tablespace\n"
2944                                                           "WHERE oid = '%u';", tablespace);
2945                         result = PSQLexec(buf.data);
2946                         if (!result)
2947                                 return;
2948                         /* Should always be the case, but.... */
2949                         if (PQntuples(result) > 0)
2950                         {
2951                                 if (newline)
2952                                 {
2953                                         /* Add the tablespace as a new footer */
2954                                         printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
2955                                                                           PQgetvalue(result, 0, 0));
2956                                         printTableAddFooter(cont, buf.data);
2957                                 }
2958                                 else
2959                                 {
2960                                         /* Append the tablespace to the latest footer */
2961                                         printfPQExpBuffer(&buf, "%s", cont->footer->data);
2962
2963                                         /*-------
2964                                            translator: before this string there's an index description like
2965                                            '"foo_pkey" PRIMARY KEY, btree (a)' */
2966                                         appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
2967                                                                           PQgetvalue(result, 0, 0));
2968                                         printTableSetFooter(cont, buf.data);
2969                                 }
2970                         }
2971                         PQclear(result);
2972                         termPQExpBuffer(&buf);
2973                 }
2974         }
2975 }
2976
2977 /*
2978  * \du or \dg
2979  *
2980  * Describes roles.  Any schema portion of the pattern is ignored.
2981  */
2982 bool
2983 describeRoles(const char *pattern, bool verbose, bool showSystem)
2984 {
2985         PQExpBufferData buf;
2986         PGresult   *res;
2987         printTableContent cont;
2988         printTableOpt myopt = pset.popt.topt;
2989         int                     ncols = 3;
2990         int                     nrows = 0;
2991         int                     i;
2992         int                     conns;
2993         const char      align = 'l';
2994         char      **attr;
2995
2996         myopt.default_footer = false;
2997
2998         initPQExpBuffer(&buf);
2999
3000         if (pset.sversion >= 80100)
3001         {
3002                 printfPQExpBuffer(&buf,
3003                                                   "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
3004                                                   "  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3005                                                   "  r.rolconnlimit, r.rolvaliduntil,\n"
3006                                                   "  ARRAY(SELECT b.rolname\n"
3007                                                   "        FROM pg_catalog.pg_auth_members m\n"
3008                                  "        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
3009                                                   "        WHERE m.member = r.oid) as memberof");
3010
3011                 if (verbose && pset.sversion >= 80200)
3012                 {
3013                         appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
3014                         ncols++;
3015                 }
3016                 if (pset.sversion >= 90100)
3017                 {
3018                         appendPQExpBufferStr(&buf, "\n, r.rolreplication");
3019                 }
3020
3021                 if (pset.sversion >= 90500)
3022                 {
3023                         appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
3024                 }
3025
3026                 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
3027
3028                 if (!showSystem && !pattern)
3029                         appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
3030
3031                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3032                                                           NULL, "r.rolname", NULL, NULL);
3033         }
3034         else
3035         {
3036                 printfPQExpBuffer(&buf,
3037                                                   "SELECT u.usename AS rolname,\n"
3038                                                   "  u.usesuper AS rolsuper,\n"
3039                                                   "  true AS rolinherit, false AS rolcreaterole,\n"
3040                                          "  u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
3041                                                   "  -1 AS rolconnlimit,"
3042                                                   "  u.valuntil as rolvaliduntil,\n"
3043                                                   "  ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
3044                                                   "\nFROM pg_catalog.pg_user u\n");
3045
3046                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3047                                                           NULL, "u.usename", NULL, NULL);
3048         }
3049
3050         appendPQExpBufferStr(&buf, "ORDER BY 1;");
3051
3052         res = PSQLexec(buf.data);
3053         if (!res)
3054                 return false;
3055
3056         nrows = PQntuples(res);
3057         attr = pg_malloc0((nrows + 1) * sizeof(*attr));
3058
3059         printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
3060
3061         printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
3062         printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
3063         printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
3064
3065         if (verbose && pset.sversion >= 80200)
3066                 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
3067
3068         for (i = 0; i < nrows; i++)
3069         {
3070                 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
3071
3072                 resetPQExpBuffer(&buf);
3073                 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
3074                         add_role_attribute(&buf, _("Superuser"));
3075
3076                 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
3077                         add_role_attribute(&buf, _("No inheritance"));
3078
3079                 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
3080                         add_role_attribute(&buf, _("Create role"));
3081
3082                 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
3083                         add_role_attribute(&buf, _("Create DB"));
3084
3085                 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
3086                         add_role_attribute(&buf, _("Cannot login"));
3087
3088                 if (pset.sversion >= 90100)
3089                         if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
3090                                 add_role_attribute(&buf, _("Replication"));
3091
3092                 if (pset.sversion >= 90500)
3093                         if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
3094                                 add_role_attribute(&buf, _("Bypass RLS"));
3095
3096                 conns = atoi(PQgetvalue(res, i, 6));
3097                 if (conns >= 0)
3098                 {
3099                         if (buf.len > 0)
3100                                 appendPQExpBufferChar(&buf, '\n');
3101
3102                         if (conns == 0)
3103                                 appendPQExpBufferStr(&buf, _("No connections"));
3104                         else
3105                                 appendPQExpBuffer(&buf, ngettext("%d connection",
3106                                                                                                  "%d connections",
3107                                                                                                  conns),
3108                                                                   conns);
3109                 }
3110
3111                 if (strcmp(PQgetvalue(res, i, 7), "") != 0)
3112                 {
3113                         if (buf.len > 0)
3114                                 appendPQExpBufferStr(&buf, "\n");
3115                         appendPQExpBufferStr(&buf, _("Password valid until "));
3116                         appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
3117                 }
3118
3119                 attr[i] = pg_strdup(buf.data);
3120
3121                 printTableAddCell(&cont, attr[i], false, false);
3122
3123                 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
3124
3125                 if (verbose && pset.sversion >= 80200)
3126                         printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
3127         }
3128         termPQExpBuffer(&buf);
3129
3130         printTable(&cont, pset.queryFout, false, pset.logfile);
3131         printTableCleanup(&cont);
3132
3133         for (i = 0; i < nrows; i++)
3134                 free(attr[i]);
3135         free(attr);
3136
3137         PQclear(res);
3138         return true;
3139 }
3140
3141 static void
3142 add_role_attribute(PQExpBuffer buf, const char *const str)
3143 {
3144         if (buf->len > 0)
3145                 appendPQExpBufferStr(buf, ", ");
3146
3147         appendPQExpBufferStr(buf, str);
3148 }
3149
3150 /*
3151  * \drds
3152  */
3153 bool
3154 listDbRoleSettings(const char *pattern, const char *pattern2)
3155 {
3156         PQExpBufferData buf;
3157         PGresult   *res;
3158         printQueryOpt myopt = pset.popt;
3159
3160         initPQExpBuffer(&buf);
3161
3162         if (pset.sversion >= 90000)
3163         {
3164                 bool            havewhere;
3165
3166                 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
3167                                   "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3168                                                   "FROM pg_db_role_setting AS s\n"
3169                                    "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
3170                                                   "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
3171                                                   gettext_noop("Role"),
3172                                                   gettext_noop("Database"),
3173                                                   gettext_noop("Settings"));
3174                 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
3175                                                                            NULL, "pg_roles.rolname", NULL, NULL);
3176                 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
3177                                                           NULL, "pg_database.datname", NULL, NULL);
3178                 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3179         }
3180         else
3181         {
3182                 fprintf(pset.queryFout,
3183                 _("No per-database role settings support in this server version.\n"));
3184                 return false;
3185         }
3186
3187         res = PSQLexec(buf.data);
3188         if (!res)
3189                 return false;
3190
3191         if (PQntuples(res) == 0 && !pset.quiet)
3192         {
3193                 if (pattern)
3194                         fprintf(pset.queryFout, _("No matching settings found.\n"));
3195                 else
3196                         fprintf(pset.queryFout, _("No settings found.\n"));
3197         }
3198         else
3199         {
3200                 myopt.nullPrint = NULL;
3201                 myopt.title = _("List of settings");
3202                 myopt.translate_header = true;
3203
3204                 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3205         }
3206
3207         PQclear(res);
3208         resetPQExpBuffer(&buf);
3209         return true;
3210 }
3211
3212
3213 /*
3214  * listTables()
3215  *
3216  * handler for \dt, \di, etc.
3217  *
3218  * tabtypes is an array of characters, specifying what info is desired:
3219  * t - tables
3220  * i - indexes
3221  * v - views
3222  * m - materialized views
3223  * s - sequences
3224  * E - foreign table (Note: different from 'f', the relkind value)
3225  * (any order of the above is fine)
3226  * If tabtypes is empty, we default to \dtvsE.
3227  */
3228 bool
3229 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
3230 {
3231         bool            showTables = strchr(tabtypes, 't') != NULL;
3232         bool            showIndexes = strchr(tabtypes, 'i') != NULL;
3233         bool            showViews = strchr(tabtypes, 'v') != NULL;
3234         bool            showMatViews = strchr(tabtypes, 'm') != NULL;
3235         bool            showSeq = strchr(tabtypes, 's') != NULL;
3236         bool            showForeign = strchr(tabtypes, 'E') != NULL;
3237
3238         PQExpBufferData buf;
3239         PGresult   *res;
3240         printQueryOpt myopt = pset.popt;
3241         static const bool translate_columns[] = {false, false, true, false, false, false, false};
3242
3243         if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
3244                 showTables = showViews = showMatViews = showSeq = showForeign = true;
3245
3246         initPQExpBuffer(&buf);
3247
3248         /*
3249          * Note: as of Pg 8.2, we no longer use relkind 's' (special), but we keep
3250          * it here for backwards compatibility.
3251          */
3252         printfPQExpBuffer(&buf,
3253                                           "SELECT n.nspname as \"%s\",\n"
3254                                           "  c.relname as \"%s\",\n"
3255                                           "  CASE c.relkind"
3256                                           " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
3257                                           " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
3258                                           " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
3259                                           " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
3260                                           " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
3261                                           " WHEN 's' THEN '%s'"
3262                                           " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
3263                                           " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
3264                                           " END as \"%s\",\n"
3265                                           "  pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
3266                                           gettext_noop("Schema"),
3267                                           gettext_noop("Name"),
3268                                           gettext_noop("table"),
3269                                           gettext_noop("view"),
3270                                           gettext_noop("materialized view"),
3271                                           gettext_noop("index"),
3272                                           gettext_noop("sequence"),
3273                                           gettext_noop("special"),
3274                                           gettext_noop("foreign table"),
3275                                           gettext_noop("table"),        /* partitioned table */
3276                                           gettext_noop("Type"),
3277                                           gettext_noop("Owner"));
3278
3279         if (showIndexes)
3280                 appendPQExpBuffer(&buf,
3281                                                   ",\n c2.relname as \"%s\"",
3282                                                   gettext_noop("Table"));
3283
3284         if (verbose)
3285         {
3286                 /*
3287                  * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
3288                  * size of a table, including FSM, VM and TOAST tables.
3289                  */
3290                 if (pset.sversion >= 90000)
3291                         appendPQExpBuffer(&buf,
3292                                                           ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
3293                                                           gettext_noop("Size"));
3294                 else if (pset.sversion >= 80100)
3295                         appendPQExpBuffer(&buf,
3296                                                           ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
3297                                                           gettext_noop("Size"));
3298
3299                 appendPQExpBuffer(&buf,
3300                           ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
3301                                                   gettext_noop("Description"));
3302         }
3303
3304         appendPQExpBufferStr(&buf,
3305                                                  "\nFROM pg_catalog.pg_class c"
3306          "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
3307         if (showIndexes)
3308                 appendPQExpBufferStr(&buf,
3309                          "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
3310                    "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
3311
3312         appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
3313         if (showTables)
3314                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ","
3315                                                          CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
3316         if (showViews)
3317                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VIEW) ",");
3318         if (showMatViews)
3319                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_MATVIEW) ",");
3320         if (showIndexes)
3321                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ",");
3322         if (showSeq)
3323                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ",");
3324         if (showSystem || pattern)
3325                 appendPQExpBufferStr(&buf, "'s',");             /* was RELKIND_SPECIAL */
3326         if (showForeign)
3327                 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
3328
3329         appendPQExpBufferStr(&buf, "''");       /* dummy */
3330         appendPQExpBufferStr(&buf, ")\n");
3331
3332         if (!showSystem && !pattern)
3333                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
3334                                                          "      AND n.nspname <> 'information_schema'\n");
3335
3336         /*
3337          * TOAST objects are suppressed unconditionally.  Since we don't provide
3338          * any way to select RELKIND_TOASTVALUE above, we would never show toast
3339          * tables in any case; it seems a bit confusing to allow their indexes to
3340          * be shown.  Use plain \d if you really need to look at a TOAST
3341          * table/index.
3342          */
3343         appendPQExpBufferStr(&buf, "      AND n.nspname !~ '^pg_toast'\n");
3344
3345         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3346                                                   "n.nspname", "c.relname", NULL,
3347                                                   "pg_catalog.pg_table_is_visible(c.oid)");
3348
3349         appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
3350
3351         res = PSQLexec(buf.data);
3352         termPQExpBuffer(&buf);
3353         if (!res)
3354                 return false;
3355
3356         if (PQntuples(res) == 0 && !pset.quiet)
3357         {
3358                 if (pattern)
3359                         fprintf(pset.queryFout, _("No matching relations found.\n"));
3360                 else
3361                         fprintf(pset.queryFout, _("No relations found.\n"));
3362         }
3363         else
3364         {
3365                 myopt.nullPrint = NULL;
3366                 myopt.title = _("List of relations");
3367                 myopt.translate_header = true;
3368                 myopt.translate_columns = translate_columns;
3369                 myopt.n_translate_columns = lengthof(translate_columns);
3370
3371                 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3372         }
3373
3374         PQclear(res);
3375         return true;
3376 }
3377
3378
3379 /*
3380  * \dL
3381  *
3382  * Describes languages.
3383  */
3384 bool
3385 listLanguages(const char *pattern, bool verbose, bool showSystem)
3386 {
3387         PQExpBufferData buf;
3388         PGresult   *res;
3389         printQueryOpt myopt = pset.popt;
3390
3391         initPQExpBuffer(&buf);
3392
3393         printfPQExpBuffer(&buf,
3394                                           "SELECT l.lanname AS \"%s\",\n",
3395                                           gettext_noop("Name"));
3396         if (pset.sversion >= 80300)
3397                 appendPQExpBuffer(&buf,
3398                                 "       pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n",
3399                                                   gettext_noop("Owner"));
3400
3401         appendPQExpBuffer(&buf,
3402                                           "       l.lanpltrusted AS \"%s\"",
3403                                           gettext_noop("Trusted"));
3404
3405         if (verbose)
3406         {
3407                 appendPQExpBuffer(&buf,
3408                                                   ",\n       NOT l.lanispl AS \"%s\",\n"
3409                                                   "       l.lanplcallfoid::regprocedure AS \"%s\",\n"
3410                                    "       l.lanvalidator::regprocedure AS \"%s\",\n       ",
3411                                                   gettext_noop("Internal Language"),
3412                                                   gettext_noop("Call Handler"),
3413                                                   gettext_noop("Validator"));
3414                 if (pset.sversion >= 90000)
3415                         appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n       ",
3416                                                           gettext_noop("Inline Handler"));
3417                 printACLColumn(&buf, "l.lanacl");
3418         }
3419
3420         appendPQExpBuffer(&buf,
3421                                           ",\n       d.description AS \"%s\""
3422                                           "\nFROM pg_catalog.pg_language l\n"
3423                                           "LEFT JOIN pg_catalog.pg_description d\n"
3424                                           "  ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
3425                                           "  AND d.objsubid = 0\n",
3426                                           gettext_noop("Description"));
3427
3428         if (pattern)
3429                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3430                                                           NULL, "l.lanname", NULL, NULL);
3431
3432         if (!showSystem && !pattern)
3433                 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
3434
3435
3436         appendPQExpBufferStr(&buf, "ORDER BY 1;");
3437
3438         res = PSQLexec(buf.data);
3439         termPQExpBuffer(&buf);
3440         if (!res)
3441                 return false;
3442
3443         myopt.nullPrint = NULL;
3444         myopt.title = _("List of languages");
3445         myopt.translate_header = true;
3446
3447         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3448
3449         PQclear(res);
3450         return true;
3451 }
3452
3453
3454 /*
3455  * \dD
3456  *
3457  * Describes domains.
3458  */
3459 bool
3460 listDomains(const char *pattern, bool verbose, bool showSystem)
3461 {
3462         PQExpBufferData buf;
3463         PGresult   *res;
3464         printQueryOpt myopt = pset.popt;
3465
3466         initPQExpBuffer(&buf);
3467
3468         printfPQExpBuffer(&buf,
3469                                           "SELECT n.nspname as \"%s\",\n"
3470                                           "       t.typname as \"%s\",\n"
3471                                           "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n",
3472                                           gettext_noop("Schema"),
3473                                           gettext_noop("Name"),
3474                                           gettext_noop("Type"));
3475
3476         if (pset.sversion >= 90100)
3477                 appendPQExpBuffer(&buf,
3478                                                   "       (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
3479                                                   "        WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n",
3480                                                   gettext_noop("Collation"));
3481         appendPQExpBuffer(&buf,
3482                                           "       CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
3483                                           "       t.typdefault as \"%s\",\n"
3484                                           "       pg_catalog.array_to_string(ARRAY(\n"
3485                                           "         SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
3486                                           "       ), ' ') as \"%s\"",
3487                                           gettext_noop("Nullable"),
3488                                           gettext_noop("Default"),
3489                                           gettext_noop("Check"));
3490
3491         if (verbose)
3492         {
3493                 if (pset.sversion >= 90200)
3494                 {
3495                         appendPQExpBufferStr(&buf, ",\n  ");
3496                         printACLColumn(&buf, "t.typacl");
3497                 }
3498                 appendPQExpBuffer(&buf,
3499                                                   ",\n       d.description as \"%s\"",
3500                                                   gettext_noop("Description"));
3501         }
3502
3503         appendPQExpBufferStr(&buf,
3504                                                  "\nFROM pg_catalog.pg_type t\n"
3505          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
3506
3507         if (verbose)
3508                 appendPQExpBufferStr(&buf,
3509                                                          "     LEFT JOIN pg_catalog.pg_description d "
3510                                                    "ON d.classoid = t.tableoid AND d.objoid = t.oid "
3511                                                          "AND d.objsubid = 0\n");
3512
3513         appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
3514
3515         if (!showSystem && !pattern)
3516                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
3517                                                          "      AND n.nspname <> 'information_schema'\n");
3518
3519         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3520                                                   "n.nspname", "t.typname", NULL,
3521                                                   "pg_catalog.pg_type_is_visible(t.oid)");
3522
3523         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3524
3525         res = PSQLexec(buf.data);
3526         termPQExpBuffer(&buf);
3527         if (!res)
3528                 return false;
3529
3530         myopt.nullPrint = NULL;
3531         myopt.title = _("List of domains");
3532         myopt.translate_header = true;
3533
3534         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3535
3536         PQclear(res);
3537         return true;
3538 }
3539
3540 /*
3541  * \dc
3542  *
3543  * Describes conversions.
3544  */
3545 bool
3546 listConversions(const char *pattern, bool verbose, bool showSystem)
3547 {
3548         PQExpBufferData buf;
3549         PGresult   *res;
3550         printQueryOpt myopt = pset.popt;
3551         static const bool translate_columns[] =
3552         {false, false, false, false, true, false};
3553
3554         initPQExpBuffer(&buf);
3555
3556         printfPQExpBuffer(&buf,
3557                                           "SELECT n.nspname AS \"%s\",\n"
3558                                           "       c.conname AS \"%s\",\n"
3559            "       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
3560                 "       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
3561                                           "       CASE WHEN c.condefault THEN '%s'\n"
3562                                           "       ELSE '%s' END AS \"%s\"",
3563                                           gettext_noop("Schema"),
3564                                           gettext_noop("Name"),
3565                                           gettext_noop("Source"),
3566                                           gettext_noop("Destination"),
3567                                           gettext_noop("yes"), gettext_noop("no"),
3568                                           gettext_noop("Default?"));
3569
3570         if (verbose)
3571                 appendPQExpBuffer(&buf,
3572                                                   ",\n       d.description AS \"%s\"",
3573                                                   gettext_noop("Description"));
3574
3575         appendPQExpBufferStr(&buf,
3576                                                  "\nFROM pg_catalog.pg_conversion c\n"
3577                                                  "     JOIN pg_catalog.pg_namespace n "
3578                                                  "ON n.oid = c.connamespace\n");
3579
3580         if (verbose)
3581                 appendPQExpBufferStr(&buf,
3582                                                          "LEFT JOIN pg_catalog.pg_description d "
3583                                                          "ON d.classoid = c.tableoid\n"
3584                                                          "          AND d.objoid = c.oid "
3585                                                          "AND d.objsubid = 0\n");
3586
3587         appendPQExpBufferStr(&buf, "WHERE true\n");
3588
3589         if (!showSystem && !pattern)
3590                 appendPQExpBufferStr(&buf, "  AND n.nspname <> 'pg_catalog'\n"
3591                                                          "  AND n.nspname <> 'information_schema'\n");
3592
3593         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3594                                                   "n.nspname", "c.conname", NULL,
3595                                                   "pg_catalog.pg_conversion_is_visible(c.oid)");
3596
3597         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3598
3599         res = PSQLexec(buf.data);
3600         termPQExpBuffer(&buf);
3601         if (!res)
3602                 return false;
3603
3604         myopt.nullPrint = NULL;
3605         myopt.title = _("List of conversions");
3606         myopt.translate_header = true;
3607         myopt.translate_columns = translate_columns;
3608         myopt.n_translate_columns = lengthof(translate_columns);
3609
3610         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3611
3612         PQclear(res);
3613         return true;
3614 }
3615
3616 /*
3617  * \dy
3618  *
3619  * Describes Event Triggers.
3620  */
3621 bool
3622 listEventTriggers(const char *pattern, bool verbose)
3623 {
3624         PQExpBufferData buf;
3625         PGresult   *res;
3626         printQueryOpt myopt = pset.popt;
3627         static const bool translate_columns[] =
3628         {false, false, false, true, false, false, false};
3629
3630         initPQExpBuffer(&buf);
3631
3632         printfPQExpBuffer(&buf,
3633                                           "SELECT evtname as \"%s\", "
3634                                           "evtevent as \"%s\", "
3635                                           "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
3636                                           " case evtenabled when 'O' then '%s'"
3637                                           "  when 'R' then '%s'"
3638                                           "  when 'A' then '%s'"
3639                                           "  when 'D' then '%s' end as \"%s\",\n"
3640                                           " e.evtfoid::pg_catalog.regproc as \"%s\", "
3641                                           "pg_catalog.array_to_string(array(select x"
3642                                 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
3643                                           gettext_noop("Name"),
3644                                           gettext_noop("Event"),
3645                                           gettext_noop("Owner"),
3646                                           gettext_noop("enabled"),
3647                                           gettext_noop("replica"),
3648                                           gettext_noop("always"),
3649                                           gettext_noop("disabled"),
3650                                           gettext_noop("Enabled"),
3651                                           gettext_noop("Procedure"),
3652                                           gettext_noop("Tags"));
3653         if (verbose)
3654                 appendPQExpBuffer(&buf,
3655                 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
3656                                                   gettext_noop("Description"));
3657         appendPQExpBufferStr(&buf,
3658                                                  "\nFROM pg_catalog.pg_event_trigger e ");
3659
3660         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3661                                                   NULL, "evtname", NULL, NULL);
3662
3663         appendPQExpBufferStr(&buf, "ORDER BY 1");
3664
3665         res = PSQLexec(buf.data);
3666         termPQExpBuffer(&buf);
3667         if (!res)
3668                 return false;
3669
3670         myopt.nullPrint = NULL;
3671         myopt.title = _("List of event triggers");
3672         myopt.translate_header = true;
3673         myopt.translate_columns = translate_columns;
3674         myopt.n_translate_columns = lengthof(translate_columns);
3675
3676         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3677
3678         PQclear(res);
3679         return true;
3680 }
3681
3682 /*
3683  * \dC
3684  *
3685  * Describes casts.
3686  */
3687 bool
3688 listCasts(const char *pattern, bool verbose)
3689 {
3690         PQExpBufferData buf;
3691         PGresult   *res;
3692         printQueryOpt myopt = pset.popt;
3693         static const bool translate_columns[] = {false, false, false, true, false};
3694
3695         initPQExpBuffer(&buf);
3696
3697         /*
3698          * We need a left join to pg_proc for binary casts; the others are just
3699          * paranoia.  Also note that we don't attempt to localize '(binary
3700          * coercible)', because there's too much risk of gettext translating a
3701          * function name that happens to match some string in the PO database.
3702          */
3703         printfPQExpBuffer(&buf,
3704                            "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
3705                            "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
3706                                   "       CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
3707                                           "            ELSE p.proname\n"
3708                                           "       END as \"%s\",\n"
3709                                           "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
3710                                           "            WHEN c.castcontext = 'a' THEN '%s'\n"
3711                                           "            ELSE '%s'\n"
3712                                           "       END as \"%s\"",
3713                                           gettext_noop("Source type"),
3714                                           gettext_noop("Target type"),
3715                                           gettext_noop("Function"),
3716                                           gettext_noop("no"),
3717                                           gettext_noop("in assignment"),
3718                                           gettext_noop("yes"),
3719                                           gettext_noop("Implicit?"));
3720
3721         if (verbose)
3722                 appendPQExpBuffer(&buf,
3723                                                   ",\n       d.description AS \"%s\"\n",
3724                                                   gettext_noop("Description"));
3725
3726         appendPQExpBufferStr(&buf,
3727                                  "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
3728                                                  "     ON c.castfunc = p.oid\n"
3729                                                  "     LEFT JOIN pg_catalog.pg_type ts\n"
3730                                                  "     ON c.castsource = ts.oid\n"
3731                                                  "     LEFT JOIN pg_catalog.pg_namespace ns\n"
3732                                                  "     ON ns.oid = ts.typnamespace\n"
3733                                                  "     LEFT JOIN pg_catalog.pg_type tt\n"
3734                                                  "     ON c.casttarget = tt.oid\n"
3735                                                  "     LEFT JOIN pg_catalog.pg_namespace nt\n"
3736                                                  "     ON nt.oid = tt.typnamespace\n");
3737
3738         if (verbose)
3739                 appendPQExpBufferStr(&buf,
3740                                                          "     LEFT JOIN pg_catalog.pg_description d\n"
3741                                                          "     ON d.classoid = c.tableoid AND d.objoid = "
3742                                                          "c.oid AND d.objsubid = 0\n");
3743
3744         appendPQExpBufferStr(&buf, "WHERE ( (true");
3745
3746         /*
3747          * Match name pattern against either internal or external name of either
3748          * castsource or casttarget
3749          */
3750         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3751                                                   "ns.nspname", "ts.typname",
3752                                                   "pg_catalog.format_type(ts.oid, NULL)",
3753                                                   "pg_catalog.pg_type_is_visible(ts.oid)");
3754
3755         appendPQExpBufferStr(&buf, ") OR (true");
3756
3757         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3758                                                   "nt.nspname", "tt.typname",
3759                                                   "pg_catalog.format_type(tt.oid, NULL)",
3760                                                   "pg_catalog.pg_type_is_visible(tt.oid)");
3761
3762         appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
3763
3764         res = PSQLexec(buf.data);
3765         termPQExpBuffer(&buf);
3766         if (!res)
3767                 return false;
3768
3769         myopt.nullPrint = NULL;
3770         myopt.title = _("List of casts");
3771         myopt.translate_header = true;
3772         myopt.translate_columns = translate_columns;
3773         myopt.n_translate_columns = lengthof(translate_columns);
3774
3775         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3776
3777         PQclear(res);
3778         return true;
3779 }
3780
3781 /*
3782  * \dO
3783  *
3784  * Describes collations.
3785  */
3786 bool
3787 listCollations(const char *pattern, bool verbose, bool showSystem)
3788 {
3789         PQExpBufferData buf;
3790         PGresult   *res;
3791         printQueryOpt myopt = pset.popt;
3792         static const bool translate_columns[] = {false, false, false, false, false, false};
3793
3794         if (pset.sversion < 90100)
3795         {
3796                 char            sverbuf[32];
3797
3798                 psql_error("The server (version %s) does not support collations.\n",
3799                                    formatPGVersionNumber(pset.sversion, false,
3800                                                                                  sverbuf, sizeof(sverbuf)));
3801                 return true;
3802         }
3803
3804         initPQExpBuffer(&buf);
3805
3806         printfPQExpBuffer(&buf,
3807                                           "SELECT n.nspname AS \"%s\",\n"
3808                                           "       c.collname AS \"%s\",\n"
3809                                           "       c.collcollate AS \"%s\",\n"
3810                                           "       c.collctype AS \"%s\"",
3811                                           gettext_noop("Schema"),
3812                                           gettext_noop("Name"),
3813                                           gettext_noop("Collate"),
3814                                           gettext_noop("Ctype"));
3815
3816         if (pset.sversion >= 100000)
3817                 appendPQExpBuffer(&buf,
3818                                                   ",\n       CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\"",
3819                                                   gettext_noop("Provider"));
3820
3821         if (verbose)
3822                 appendPQExpBuffer(&buf,
3823                                                   ",\n       pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
3824                                                   gettext_noop("Description"));
3825
3826         appendPQExpBufferStr(&buf,
3827                           "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
3828                                                  "WHERE n.oid = c.collnamespace\n");
3829
3830         if (!showSystem && !pattern)
3831                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
3832                                                          "      AND n.nspname <> 'information_schema'\n");
3833
3834         /*
3835          * Hide collations that aren't usable in the current database's encoding.
3836          * If you think to change this, note that pg_collation_is_visible rejects
3837          * unusable collations, so you will need to hack name pattern processing
3838          * somehow to avoid inconsistent behavior.
3839          */
3840         appendPQExpBufferStr(&buf, "      AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
3841
3842         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3843                                                   "n.nspname", "c.collname", NULL,
3844                                                   "pg_catalog.pg_collation_is_visible(c.oid)");
3845
3846         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3847
3848         res = PSQLexec(buf.data);
3849         termPQExpBuffer(&buf);
3850         if (!res)
3851                 return false;
3852
3853         myopt.nullPrint = NULL;
3854         myopt.title = _("List of collations");
3855         myopt.translate_header = true;
3856         myopt.translate_columns = translate_columns;
3857         myopt.n_translate_columns = lengthof(translate_columns);
3858
3859         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3860
3861         PQclear(res);
3862         return true;
3863 }
3864
3865 /*
3866  * \dn
3867  *
3868  * Describes schemas (namespaces)
3869  */
3870 bool
3871 listSchemas(const char *pattern, bool verbose, bool showSystem)
3872 {
3873         PQExpBufferData buf;
3874         PGresult   *res;
3875         printQueryOpt myopt = pset.popt;
3876
3877         initPQExpBuffer(&buf);
3878         printfPQExpBuffer(&buf,
3879                                           "SELECT n.nspname AS \"%s\",\n"
3880                                           "  pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
3881                                           gettext_noop("Name"),
3882                                           gettext_noop("Owner"));
3883
3884         if (verbose)
3885         {
3886                 appendPQExpBufferStr(&buf, ",\n  ");
3887                 printACLColumn(&buf, "n.nspacl");
3888                 appendPQExpBuffer(&buf,
3889                   ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
3890                                                   gettext_noop("Description"));
3891         }
3892
3893         appendPQExpBuffer(&buf,
3894                                           "\nFROM pg_catalog.pg_namespace n\n");
3895
3896         if (!showSystem && !pattern)
3897                 appendPQExpBufferStr(&buf,
3898                 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
3899
3900         processSQLNamePattern(pset.db, &buf, pattern,
3901                                                   !showSystem && !pattern, false,
3902                                                   NULL, "n.nspname", NULL,
3903                                                   NULL);
3904
3905         appendPQExpBufferStr(&buf, "ORDER BY 1;");
3906
3907         res = PSQLexec(buf.data);
3908         termPQExpBuffer(&buf);
3909         if (!res)
3910                 return false;
3911
3912         myopt.nullPrint = NULL;
3913         myopt.title = _("List of schemas");
3914         myopt.translate_header = true;
3915
3916         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3917
3918         PQclear(res);
3919         return true;
3920 }
3921
3922
3923 /*
3924  * \dFp
3925  * list text search parsers
3926  */
3927 bool
3928 listTSParsers(const char *pattern, bool verbose)
3929 {
3930         PQExpBufferData buf;
3931         PGresult   *res;
3932         printQueryOpt myopt = pset.popt;
3933
3934         if (pset.sversion < 80300)
3935         {
3936                 char            sverbuf[32];
3937
3938                 psql_error("The server (version %s) does not support full text search.\n",
3939                                    formatPGVersionNumber(pset.sversion, false,
3940                                                                                  sverbuf, sizeof(sverbuf)));
3941                 return true;
3942         }
3943
3944         if (verbose)
3945                 return listTSParsersVerbose(pattern);
3946
3947         initPQExpBuffer(&buf);
3948
3949         printfPQExpBuffer(&buf,
3950                                           "SELECT \n"
3951                                           "  n.nspname as \"%s\",\n"
3952                                           "  p.prsname as \"%s\",\n"
3953                         "  pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
3954                                           "FROM pg_catalog.pg_ts_parser p \n"
3955                    "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
3956                                           gettext_noop("Schema"),
3957                                           gettext_noop("Name"),
3958                                           gettext_noop("Description")
3959                 );
3960
3961         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3962                                                   "n.nspname", "p.prsname", NULL,
3963                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
3964
3965         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3966
3967         res = PSQLexec(buf.data);
3968         termPQExpBuffer(&buf);
3969         if (!res)
3970                 return false;
3971
3972         myopt.nullPrint = NULL;
3973         myopt.title = _("List of text search parsers");
3974         myopt.translate_header = true;
3975
3976         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3977
3978         PQclear(res);
3979         return true;
3980 }
3981
3982 /*
3983  * full description of parsers
3984  */
3985 static bool
3986 listTSParsersVerbose(const char *pattern)
3987 {
3988         PQExpBufferData buf;
3989         PGresult   *res;
3990         int                     i;
3991
3992         initPQExpBuffer(&buf);
3993
3994         printfPQExpBuffer(&buf,
3995                                           "SELECT p.oid, \n"
3996                                           "  n.nspname, \n"
3997                                           "  p.prsname \n"
3998                                           "FROM pg_catalog.pg_ts_parser p\n"
3999                         "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
4000                 );
4001
4002         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4003                                                   "n.nspname", "p.prsname", NULL,
4004                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
4005
4006         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4007
4008         res = PSQLexec(buf.data);
4009         termPQExpBuffer(&buf);
4010         if (!res)
4011                 return false;
4012
4013         if (PQntuples(res) == 0)
4014         {
4015                 if (!pset.quiet)
4016                         psql_error("Did not find any text search parser named \"%s\".\n",
4017                                            pattern);
4018                 PQclear(res);
4019                 return false;
4020         }
4021
4022         for (i = 0; i < PQntuples(res); i++)
4023         {
4024                 const char *oid;
4025                 const char *nspname = NULL;
4026                 const char *prsname;
4027
4028                 oid = PQgetvalue(res, i, 0);
4029                 if (!PQgetisnull(res, i, 1))
4030                         nspname = PQgetvalue(res, i, 1);
4031                 prsname = PQgetvalue(res, i, 2);
4032
4033                 if (!describeOneTSParser(oid, nspname, prsname))
4034                 {
4035                         PQclear(res);
4036                         return false;
4037                 }
4038
4039                 if (cancel_pressed)
4040                 {
4041                         PQclear(res);
4042                         return false;
4043                 }
4044         }
4045
4046         PQclear(res);
4047         return true;
4048 }
4049
4050 static bool
4051 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
4052 {
4053         PQExpBufferData buf;
4054         PGresult   *res;
4055         char            title[1024];
4056         printQueryOpt myopt = pset.popt;
4057         static const bool translate_columns[] = {true, false, false};
4058
4059         initPQExpBuffer(&buf);
4060
4061         printfPQExpBuffer(&buf,
4062                                           "SELECT '%s' AS \"%s\", \n"
4063                                           "   p.prsstart::pg_catalog.regproc AS \"%s\", \n"
4064                   "   pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
4065                                           " FROM pg_catalog.pg_ts_parser p \n"
4066                                           " WHERE p.oid = '%s' \n"
4067                                           "UNION ALL \n"
4068                                           "SELECT '%s', \n"
4069                                           "   p.prstoken::pg_catalog.regproc, \n"
4070                                         "   pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
4071                                           " FROM pg_catalog.pg_ts_parser p \n"
4072                                           " WHERE p.oid = '%s' \n"
4073                                           "UNION ALL \n"
4074                                           "SELECT '%s', \n"
4075                                           "   p.prsend::pg_catalog.regproc, \n"
4076                                           "   pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
4077                                           " FROM pg_catalog.pg_ts_parser p \n"
4078                                           " WHERE p.oid = '%s' \n"
4079                                           "UNION ALL \n"
4080                                           "SELECT '%s', \n"
4081                                           "   p.prsheadline::pg_catalog.regproc, \n"
4082                                  "   pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
4083                                           " FROM pg_catalog.pg_ts_parser p \n"
4084                                           " WHERE p.oid = '%s' \n"
4085                                           "UNION ALL \n"
4086                                           "SELECT '%s', \n"
4087                                           "   p.prslextype::pg_catalog.regproc, \n"
4088                                   "   pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
4089                                           " FROM pg_catalog.pg_ts_parser p \n"
4090                                           " WHERE p.oid = '%s';",
4091                                           gettext_noop("Start parse"),
4092                                           gettext_noop("Method"),
4093                                           gettext_noop("Function"),
4094                                           gettext_noop("Description"),
4095                                           oid,
4096                                           gettext_noop("Get next token"),
4097                                           oid,
4098                                           gettext_noop("End parse"),
4099                                           oid,
4100                                           gettext_noop("Get headline"),
4101                                           oid,
4102                                           gettext_noop("Get token types"),
4103                                           oid);
4104
4105         res = PSQLexec(buf.data);
4106         termPQExpBuffer(&buf);
4107         if (!res)
4108                 return false;
4109
4110         myopt.nullPrint = NULL;
4111         if (nspname)
4112                 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
4113         else
4114                 sprintf(title, _("Text search parser \"%s\""), prsname);
4115         myopt.title = title;
4116         myopt.footers = NULL;
4117         myopt.topt.default_footer = false;
4118         myopt.translate_header = true;
4119         myopt.translate_columns = translate_columns;
4120         myopt.n_translate_columns = lengthof(translate_columns);
4121
4122         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4123
4124         PQclear(res);
4125
4126         initPQExpBuffer(&buf);
4127
4128         printfPQExpBuffer(&buf,
4129                                           "SELECT t.alias as \"%s\", \n"
4130                                           "  t.description as \"%s\" \n"
4131                           "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
4132                                           "ORDER BY 1;",
4133                                           gettext_noop("Token name"),
4134                                           gettext_noop("Description"),
4135                                           oid);
4136
4137         res = PSQLexec(buf.data);
4138         termPQExpBuffer(&buf);
4139         if (!res)
4140                 return false;
4141
4142         myopt.nullPrint = NULL;
4143         if (nspname)
4144                 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
4145         else
4146                 sprintf(title, _("Token types for parser \"%s\""), prsname);
4147         myopt.title = title;
4148         myopt.footers = NULL;
4149         myopt.topt.default_footer = true;
4150         myopt.translate_header = true;
4151         myopt.translate_columns = NULL;
4152         myopt.n_translate_columns = 0;
4153
4154         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4155
4156         PQclear(res);
4157         return true;
4158 }
4159
4160
4161 /*
4162  * \dFd
4163  * list text search dictionaries
4164  */
4165 bool
4166 listTSDictionaries(const char *pattern, bool verbose)
4167 {
4168         PQExpBufferData buf;
4169         PGresult   *res;
4170         printQueryOpt myopt = pset.popt;
4171
4172         if (pset.sversion < 80300)
4173         {
4174                 char            sverbuf[32];
4175
4176                 psql_error("The server (version %s) does not support full text search.\n",
4177                                    formatPGVersionNumber(pset.sversion, false,
4178                                                                                  sverbuf, sizeof(sverbuf)));
4179                 return true;
4180         }
4181
4182         initPQExpBuffer(&buf);
4183
4184         printfPQExpBuffer(&buf,
4185                                           "SELECT \n"
4186                                           "  n.nspname as \"%s\",\n"
4187                                           "  d.dictname as \"%s\",\n",
4188                                           gettext_noop("Schema"),
4189                                           gettext_noop("Name"));
4190
4191         if (verbose)
4192         {
4193                 appendPQExpBuffer(&buf,
4194                                                   "  ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
4195                                                   "    pg_catalog.pg_ts_template t \n"
4196                                                   "                      LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
4197                                                   "                      WHERE d.dicttemplate = t.oid ) AS  \"%s\", \n"
4198                                                   "  d.dictinitoption as \"%s\", \n",
4199                                                   gettext_noop("Template"),
4200                                                   gettext_noop("Init options"));
4201         }
4202
4203         appendPQExpBuffer(&buf,
4204                          "  pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
4205                                           gettext_noop("Description"));
4206
4207         appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n"
4208                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
4209
4210         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4211                                                   "n.nspname", "d.dictname", NULL,
4212                                                   "pg_catalog.pg_ts_dict_is_visible(d.oid)");
4213
4214         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4215
4216         res = PSQLexec(buf.data);
4217         termPQExpBuffer(&buf);
4218         if (!res)
4219                 return false;
4220
4221         myopt.nullPrint = NULL;
4222         myopt.title = _("List of text search dictionaries");
4223         myopt.translate_header = true;
4224
4225         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4226
4227         PQclear(res);
4228         return true;
4229 }
4230
4231
4232 /*
4233  * \dFt
4234  * list text search templates
4235  */
4236 bool
4237 listTSTemplates(const char *pattern, bool verbose)
4238 {
4239         PQExpBufferData buf;
4240         PGresult   *res;
4241         printQueryOpt myopt = pset.popt;
4242
4243         if (pset.sversion < 80300)
4244         {
4245                 char            sverbuf[32];
4246
4247                 psql_error("The server (version %s) does not support full text search.\n",
4248                                    formatPGVersionNumber(pset.sversion, false,
4249                                                                                  sverbuf, sizeof(sverbuf)));
4250                 return true;
4251         }
4252
4253         initPQExpBuffer(&buf);
4254
4255         if (verbose)
4256                 printfPQExpBuffer(&buf,
4257                                                   "SELECT \n"
4258                                                   "  n.nspname AS \"%s\",\n"
4259                                                   "  t.tmplname AS \"%s\",\n"
4260                                                   "  t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
4261                                                   "  t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
4262                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4263                                                   gettext_noop("Schema"),
4264                                                   gettext_noop("Name"),
4265                                                   gettext_noop("Init"),
4266                                                   gettext_noop("Lexize"),
4267                                                   gettext_noop("Description"));
4268         else
4269                 printfPQExpBuffer(&buf,
4270                                                   "SELECT \n"
4271                                                   "  n.nspname AS \"%s\",\n"
4272                                                   "  t.tmplname AS \"%s\",\n"
4273                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4274                                                   gettext_noop("Schema"),
4275                                                   gettext_noop("Name"),
4276                                                   gettext_noop("Description"));
4277
4278         appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n"
4279                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
4280
4281         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4282                                                   "n.nspname", "t.tmplname", NULL,
4283                                                   "pg_catalog.pg_ts_template_is_visible(t.oid)");
4284
4285         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4286
4287         res = PSQLexec(buf.data);
4288         termPQExpBuffer(&buf);
4289         if (!res)
4290                 return false;
4291
4292         myopt.nullPrint = NULL;
4293         myopt.title = _("List of text search templates");
4294         myopt.translate_header = true;
4295
4296         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4297
4298         PQclear(res);
4299         return true;
4300 }
4301
4302
4303 /*
4304  * \dF
4305  * list text search configurations
4306  */
4307 bool
4308 listTSConfigs(const char *pattern, bool verbose)
4309 {
4310         PQExpBufferData buf;
4311         PGresult   *res;
4312         printQueryOpt myopt = pset.popt;
4313
4314         if (pset.sversion < 80300)
4315         {
4316                 char            sverbuf[32];
4317
4318                 psql_error("The server (version %s) does not support full text search.\n",
4319                                    formatPGVersionNumber(pset.sversion, false,
4320                                                                                  sverbuf, sizeof(sverbuf)));
4321                 return true;
4322         }
4323
4324         if (verbose)
4325                 return listTSConfigsVerbose(pattern);
4326
4327         initPQExpBuffer(&buf);
4328
4329         printfPQExpBuffer(&buf,
4330                                           "SELECT \n"
4331                                           "   n.nspname as \"%s\",\n"
4332                                           "   c.cfgname as \"%s\",\n"
4333                    "   pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
4334                                           "FROM pg_catalog.pg_ts_config c\n"
4335                   "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
4336                                           gettext_noop("Schema"),
4337                                           gettext_noop("Name"),
4338                                           gettext_noop("Description")
4339                 );
4340
4341         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4342                                                   "n.nspname", "c.cfgname", NULL,
4343                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
4344
4345         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4346
4347         res = PSQLexec(buf.data);
4348         termPQExpBuffer(&buf);
4349         if (!res)
4350                 return false;
4351
4352         myopt.nullPrint = NULL;
4353         myopt.title = _("List of text search configurations");
4354         myopt.translate_header = true;
4355
4356         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4357
4358         PQclear(res);
4359         return true;
4360 }
4361
4362 static bool
4363 listTSConfigsVerbose(const char *pattern)
4364 {
4365         PQExpBufferData buf;
4366         PGresult   *res;
4367         int                     i;
4368
4369         initPQExpBuffer(&buf);
4370
4371         printfPQExpBuffer(&buf,
4372                                           "SELECT c.oid, c.cfgname,\n"
4373                                           "   n.nspname, \n"
4374                                           "   p.prsname, \n"
4375                                           "   np.nspname as pnspname \n"
4376                                           "FROM pg_catalog.pg_ts_config c \n"
4377            "   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
4378                                           " pg_catalog.pg_ts_parser p \n"
4379           "   LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
4380                                           "WHERE  p.oid = c.cfgparser\n"
4381                 );
4382
4383         processSQLNamePattern(pset.db, &buf, pattern, true, false,
4384                                                   "n.nspname", "c.cfgname", NULL,
4385                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
4386
4387         appendPQExpBufferStr(&buf, "ORDER BY 3, 2;");
4388
4389         res = PSQLexec(buf.data);
4390         termPQExpBuffer(&buf);
4391         if (!res)
4392                 return false;
4393
4394         if (PQntuples(res) == 0)
4395         {
4396                 if (!pset.quiet)
4397                         psql_error("Did not find any text search configuration named \"%s\".\n",
4398                                            pattern);
4399                 PQclear(res);
4400                 return false;
4401         }
4402
4403         for (i = 0; i < PQntuples(res); i++)
4404         {
4405                 const char *oid;
4406                 const char *cfgname;
4407                 const char *nspname = NULL;
4408                 const char *prsname;
4409                 const char *pnspname = NULL;
4410
4411                 oid = PQgetvalue(res, i, 0);
4412                 cfgname = PQgetvalue(res, i, 1);
4413                 if (!PQgetisnull(res, i, 2))
4414                         nspname = PQgetvalue(res, i, 2);
4415                 prsname = PQgetvalue(res, i, 3);
4416                 if (!PQgetisnull(res, i, 4))
4417                         pnspname = PQgetvalue(res, i, 4);
4418
4419                 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
4420                 {
4421                         PQclear(res);
4422                         return false;
4423                 }
4424
4425                 if (cancel_pressed)
4426                 {
4427                         PQclear(res);
4428                         return false;
4429                 }
4430         }
4431
4432         PQclear(res);
4433         return true;
4434 }
4435
4436 static bool
4437 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
4438                                         const char *pnspname, const char *prsname)
4439 {
4440         PQExpBufferData buf,
4441                                 title;
4442         PGresult   *res;
4443         printQueryOpt myopt = pset.popt;
4444
4445         initPQExpBuffer(&buf);
4446
4447         printfPQExpBuffer(&buf,
4448                                           "SELECT \n"
4449                                           "  ( SELECT t.alias FROM \n"
4450                                           "    pg_catalog.ts_token_type(c.cfgparser) AS t \n"
4451                                           "    WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
4452                                           "  pg_catalog.btrim( \n"
4453                                   "    ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
4454                                           "           FROM pg_catalog.pg_ts_config_map AS mm \n"
4455                                           "           WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
4456                                           "           ORDER BY mapcfg, maptokentype, mapseqno \n"
4457                                           "    ) :: pg_catalog.text , \n"
4458                                           "  '{}') AS \"%s\" \n"
4459          "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
4460                                           "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
4461                                           "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
4462                                           "ORDER BY 1;",
4463                                           gettext_noop("Token"),
4464                                           gettext_noop("Dictionaries"),
4465                                           oid);
4466
4467         res = PSQLexec(buf.data);
4468         termPQExpBuffer(&buf);
4469         if (!res)
4470                 return false;
4471
4472         initPQExpBuffer(&title);
4473
4474         if (nspname)
4475                 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
4476                                                   nspname, cfgname);
4477         else
4478                 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
4479                                                   cfgname);
4480
4481         if (pnspname)
4482                 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
4483                                                   pnspname, prsname);
4484         else
4485                 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
4486                                                   prsname);
4487
4488         myopt.nullPrint = NULL;
4489         myopt.title = title.data;
4490         myopt.footers = NULL;
4491         myopt.topt.default_footer = false;
4492         myopt.translate_header = true;
4493
4494         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4495
4496         termPQExpBuffer(&title);
4497
4498         PQclear(res);
4499         return true;
4500 }
4501
4502
4503 /*
4504  * \dew
4505  *
4506  * Describes foreign-data wrappers
4507  */
4508 bool
4509 listForeignDataWrappers(const char *pattern, bool verbose)
4510 {
4511         PQExpBufferData buf;
4512         PGresult   *res;
4513         printQueryOpt myopt = pset.popt;
4514
4515         if (pset.sversion < 80400)
4516         {
4517                 char            sverbuf[32];
4518
4519                 psql_error("The server (version %s) does not support foreign-data wrappers.\n",
4520                                    formatPGVersionNumber(pset.sversion, false,
4521                                                                                  sverbuf, sizeof(sverbuf)));
4522                 return true;
4523         }
4524
4525         initPQExpBuffer(&buf);
4526         printfPQExpBuffer(&buf,
4527                                           "SELECT fdw.fdwname AS \"%s\",\n"
4528                                    "  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n",
4529                                           gettext_noop("Name"),
4530                                           gettext_noop("Owner"));
4531         if (pset.sversion >= 90100)
4532                 appendPQExpBuffer(&buf,
4533                                                   "  fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n",
4534                                                   gettext_noop("Handler"));
4535         appendPQExpBuffer(&buf,
4536                                           "  fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
4537                                           gettext_noop("Validator"));
4538
4539         if (verbose)
4540         {
4541                 appendPQExpBufferStr(&buf, ",\n  ");
4542                 printACLColumn(&buf, "fdwacl");
4543                 appendPQExpBuffer(&buf,
4544                                                   ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4545                                                   "  '(' || array_to_string(ARRAY(SELECT "
4546                                                   "  quote_ident(option_name) ||  ' ' || "
4547                                                   "  quote_literal(option_value)  FROM "
4548                                                   "  pg_options_to_table(fdwoptions)),  ', ') || ')' "
4549                                                   "  END AS \"%s\"",
4550                                                   gettext_noop("FDW Options"));
4551
4552                 if (pset.sversion >= 90100)
4553                         appendPQExpBuffer(&buf,
4554                                                           ",\n  d.description AS \"%s\" ",
4555                                                           gettext_noop("Description"));
4556         }
4557
4558         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
4559
4560         if (verbose && pset.sversion >= 90100)
4561                 appendPQExpBufferStr(&buf,
4562                                                          "LEFT JOIN pg_catalog.pg_description d\n"
4563                                                          "       ON d.classoid = fdw.tableoid "
4564                                                          "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
4565
4566         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4567                                                   NULL, "fdwname", NULL, NULL);
4568
4569         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4570
4571         res = PSQLexec(buf.data);
4572         termPQExpBuffer(&buf);
4573         if (!res)
4574                 return false;
4575
4576         myopt.nullPrint = NULL;
4577         myopt.title = _("List of foreign-data wrappers");
4578         myopt.translate_header = true;
4579
4580         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4581
4582         PQclear(res);
4583         return true;
4584 }
4585
4586 /*
4587  * \des
4588  *
4589  * Describes foreign servers.
4590  */
4591 bool
4592 listForeignServers(const char *pattern, bool verbose)
4593 {
4594         PQExpBufferData buf;
4595         PGresult   *res;
4596         printQueryOpt myopt = pset.popt;
4597
4598         if (pset.sversion < 80400)
4599         {
4600                 char            sverbuf[32];
4601
4602                 psql_error("The server (version %s) does not support foreign servers.\n",
4603                                    formatPGVersionNumber(pset.sversion, false,
4604                                                                                  sverbuf, sizeof(sverbuf)));
4605                 return true;
4606         }
4607
4608         initPQExpBuffer(&buf);
4609         printfPQExpBuffer(&buf,
4610                                           "SELECT s.srvname AS \"%s\",\n"
4611                                           "  pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
4612                                           "  f.fdwname AS \"%s\"",
4613                                           gettext_noop("Name"),
4614                                           gettext_noop("Owner"),
4615                                           gettext_noop("Foreign-data wrapper"));
4616
4617         if (verbose)
4618         {
4619                 appendPQExpBufferStr(&buf, ",\n  ");
4620                 printACLColumn(&buf, "s.srvacl");
4621                 appendPQExpBuffer(&buf,
4622                                                   ",\n"
4623                                                   "  s.srvtype AS \"%s\",\n"
4624                                                   "  s.srvversion AS \"%s\",\n"
4625                                                   "  CASE WHEN srvoptions IS NULL THEN '' ELSE "
4626                                                   "  '(' || array_to_string(ARRAY(SELECT "
4627                                                   "  quote_ident(option_name) ||  ' ' || "
4628                                                   "  quote_literal(option_value)  FROM "
4629                                                   "  pg_options_to_table(srvoptions)),  ', ') || ')' "
4630                                                   "  END AS \"%s\",\n"
4631                                                   "  d.description AS \"%s\"",
4632                                                   gettext_noop("Type"),
4633                                                   gettext_noop("Version"),
4634                                                   gettext_noop("FDW Options"),
4635                                                   gettext_noop("Description"));
4636         }
4637
4638         appendPQExpBufferStr(&buf,
4639                                                  "\nFROM pg_catalog.pg_foreign_server s\n"
4640            "     JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
4641
4642         if (verbose)
4643                 appendPQExpBufferStr(&buf,
4644                                                          "LEFT JOIN pg_description d\n       "
4645                                                    "ON d.classoid = s.tableoid AND d.objoid = s.oid "
4646                                                          "AND d.objsubid = 0\n");
4647
4648         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4649                                                   NULL, "s.srvname", NULL, NULL);
4650
4651         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4652
4653         res = PSQLexec(buf.data);
4654         termPQExpBuffer(&buf);
4655         if (!res)
4656                 return false;
4657
4658         myopt.nullPrint = NULL;
4659         myopt.title = _("List of foreign servers");
4660         myopt.translate_header = true;
4661
4662         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4663
4664         PQclear(res);
4665         return true;
4666 }
4667
4668 /*
4669  * \deu
4670  *
4671  * Describes user mappings.
4672  */
4673 bool
4674 listUserMappings(const char *pattern, bool verbose)
4675 {
4676         PQExpBufferData buf;
4677         PGresult   *res;
4678         printQueryOpt myopt = pset.popt;
4679
4680         if (pset.sversion < 80400)
4681         {
4682                 char            sverbuf[32];
4683
4684                 psql_error("The server (version %s) does not support user mappings.\n",
4685                                    formatPGVersionNumber(pset.sversion, false,
4686                                                                                  sverbuf, sizeof(sverbuf)));
4687                 return true;
4688         }
4689
4690         initPQExpBuffer(&buf);
4691         printfPQExpBuffer(&buf,
4692                                           "SELECT um.srvname AS \"%s\",\n"
4693                                           "  um.usename AS \"%s\"",
4694                                           gettext_noop("Server"),
4695                                           gettext_noop("User name"));
4696
4697         if (verbose)
4698                 appendPQExpBuffer(&buf,
4699                                                   ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4700                                                   "  '(' || array_to_string(ARRAY(SELECT "
4701                                                   "  quote_ident(option_name) ||  ' ' || "
4702                                                   "  quote_literal(option_value)  FROM "
4703                                                   "  pg_options_to_table(umoptions)),  ', ') || ')' "
4704                                                   "  END AS \"%s\"",
4705                                                   gettext_noop("FDW Options"));
4706
4707         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
4708
4709         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4710                                                   NULL, "um.srvname", "um.usename", NULL);
4711
4712         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4713
4714         res = PSQLexec(buf.data);
4715         termPQExpBuffer(&buf);
4716         if (!res)
4717                 return false;
4718
4719         myopt.nullPrint = NULL;
4720         myopt.title = _("List of user mappings");
4721         myopt.translate_header = true;
4722
4723         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4724
4725         PQclear(res);
4726         return true;
4727 }
4728
4729 /*
4730  * \det
4731  *
4732  * Describes foreign tables.
4733  */
4734 bool
4735 listForeignTables(const char *pattern, bool verbose)
4736 {
4737         PQExpBufferData buf;
4738         PGresult   *res;
4739         printQueryOpt myopt = pset.popt;
4740
4741         if (pset.sversion < 90100)
4742         {
4743                 char            sverbuf[32];
4744
4745                 psql_error("The server (version %s) does not support foreign tables.\n",
4746                                    formatPGVersionNumber(pset.sversion, false,
4747                                                                                  sverbuf, sizeof(sverbuf)));
4748                 return true;
4749         }
4750
4751         initPQExpBuffer(&buf);
4752         printfPQExpBuffer(&buf,
4753                                           "SELECT n.nspname AS \"%s\",\n"
4754                                           "  c.relname AS \"%s\",\n"
4755                                           "  s.srvname AS \"%s\"",
4756                                           gettext_noop("Schema"),
4757                                           gettext_noop("Table"),
4758                                           gettext_noop("Server"));
4759
4760         if (verbose)
4761                 appendPQExpBuffer(&buf,
4762                                                   ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4763                                                   "  '(' || array_to_string(ARRAY(SELECT "
4764                                                   "  quote_ident(option_name) ||  ' ' || "
4765                                                   "  quote_literal(option_value)  FROM "
4766                                                   "  pg_options_to_table(ftoptions)),  ', ') || ')' "
4767                                                   "  END AS \"%s\",\n"
4768                                                   "  d.description AS \"%s\"",
4769                                                   gettext_noop("FDW Options"),
4770                                                   gettext_noop("Description"));
4771
4772         appendPQExpBufferStr(&buf,
4773                                                  "\nFROM pg_catalog.pg_foreign_table ft\n"
4774                                                  "  INNER JOIN pg_catalog.pg_class c"
4775                                                  " ON c.oid = ft.ftrelid\n"
4776                                                  "  INNER JOIN pg_catalog.pg_namespace n"
4777                                                  " ON n.oid = c.relnamespace\n"
4778                                                  "  INNER JOIN pg_catalog.pg_foreign_server s"
4779                                                  " ON s.oid = ft.ftserver\n");
4780         if (verbose)
4781                 appendPQExpBufferStr(&buf,
4782                                                          "   LEFT JOIN pg_catalog.pg_description d\n"
4783                                                          "          ON d.classoid = c.tableoid AND "
4784                                                          "d.objoid = c.oid AND d.objsubid = 0\n");
4785
4786         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4787                                                   "n.nspname", "c.relname", NULL,
4788                                                   "pg_catalog.pg_table_is_visible(c.oid)");
4789
4790         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4791
4792         res = PSQLexec(buf.data);
4793         termPQExpBuffer(&buf);
4794         if (!res)
4795                 return false;
4796
4797         myopt.nullPrint = NULL;
4798         myopt.title = _("List of foreign tables");
4799         myopt.translate_header = true;
4800
4801         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4802
4803         PQclear(res);
4804         return true;
4805 }
4806
4807 /*
4808  * \dx
4809  *
4810  * Briefly describes installed extensions.
4811  */
4812 bool
4813 listExtensions(const char *pattern)
4814 {
4815         PQExpBufferData buf;
4816         PGresult   *res;
4817         printQueryOpt myopt = pset.popt;
4818
4819         if (pset.sversion < 90100)
4820         {
4821                 char            sverbuf[32];
4822
4823                 psql_error("The server (version %s) does not support extensions.\n",
4824                                    formatPGVersionNumber(pset.sversion, false,
4825                                                                                  sverbuf, sizeof(sverbuf)));
4826                 return true;
4827         }
4828
4829         initPQExpBuffer(&buf);
4830         printfPQExpBuffer(&buf,
4831                                           "SELECT e.extname AS \"%s\", "
4832          "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
4833                                           "FROM pg_catalog.pg_extension e "
4834                          "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
4835                                  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
4836                  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
4837                                           gettext_noop("Name"),
4838                                           gettext_noop("Version"),
4839                                           gettext_noop("Schema"),
4840                                           gettext_noop("Description"));
4841
4842         processSQLNamePattern(pset.db, &buf, pattern,
4843                                                   false, false,
4844                                                   NULL, "e.extname", NULL,
4845                                                   NULL);
4846
4847         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4848
4849         res = PSQLexec(buf.data);
4850         termPQExpBuffer(&buf);
4851         if (!res)
4852                 return false;
4853
4854         myopt.nullPrint = NULL;
4855         myopt.title = _("List of installed extensions");
4856         myopt.translate_header = true;
4857
4858         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4859
4860         PQclear(res);
4861         return true;
4862 }
4863
4864 /*
4865  * \dx+
4866  *
4867  * List contents of installed extensions.
4868  */
4869 bool
4870 listExtensionContents(const char *pattern)
4871 {
4872         PQExpBufferData buf;
4873         PGresult   *res;
4874         int                     i;
4875
4876         if (pset.sversion < 90100)
4877         {
4878                 char            sverbuf[32];
4879
4880                 psql_error("The server (version %s) does not support extensions.\n",
4881                                    formatPGVersionNumber(pset.sversion, false,
4882                                                                                  sverbuf, sizeof(sverbuf)));
4883                 return true;
4884         }
4885
4886         initPQExpBuffer(&buf);
4887         printfPQExpBuffer(&buf,
4888                                           "SELECT e.extname, e.oid\n"
4889                                           "FROM pg_catalog.pg_extension e\n");
4890
4891         processSQLNamePattern(pset.db, &buf, pattern,
4892                                                   false, false,
4893                                                   NULL, "e.extname", NULL,
4894                                                   NULL);
4895
4896         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4897
4898         res = PSQLexec(buf.data);
4899         termPQExpBuffer(&buf);
4900         if (!res)
4901                 return false;
4902
4903         if (PQntuples(res) == 0)
4904         {
4905                 if (!pset.quiet)
4906                 {
4907                         if (pattern)
4908                                 psql_error("Did not find any extension named \"%s\".\n",
4909                                                    pattern);
4910                         else
4911                                 psql_error("Did not find any extensions.\n");
4912                 }
4913                 PQclear(res);
4914                 return false;
4915         }
4916
4917         for (i = 0; i < PQntuples(res); i++)
4918         {
4919                 const char *extname;
4920                 const char *oid;
4921
4922                 extname = PQgetvalue(res, i, 0);
4923                 oid = PQgetvalue(res, i, 1);
4924
4925                 if (!listOneExtensionContents(extname, oid))
4926                 {
4927                         PQclear(res);
4928                         return false;
4929                 }
4930                 if (cancel_pressed)
4931                 {
4932                         PQclear(res);
4933                         return false;
4934                 }
4935         }
4936
4937         PQclear(res);
4938         return true;
4939 }
4940
4941 static bool
4942 listOneExtensionContents(const char *extname, const char *oid)
4943 {
4944         PQExpBufferData buf;
4945         PGresult   *res;
4946         char            title[1024];
4947         printQueryOpt myopt = pset.popt;
4948
4949         initPQExpBuffer(&buf);
4950         printfPQExpBuffer(&buf,
4951                 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
4952                                           "FROM pg_catalog.pg_depend\n"
4953                                           "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
4954                                           "ORDER BY 1;",
4955                                           gettext_noop("Object Description"),
4956                                           oid);
4957
4958         res = PSQLexec(buf.data);
4959         termPQExpBuffer(&buf);
4960         if (!res)
4961                 return false;
4962
4963         myopt.nullPrint = NULL;
4964         snprintf(title, sizeof(title), _("Objects in extension \"%s\""), extname);
4965         myopt.title = title;
4966         myopt.translate_header = true;
4967
4968         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4969
4970         PQclear(res);
4971         return true;
4972 }
4973
4974 /* \dRp
4975  * Lists publications.
4976  *
4977  * Takes an optional regexp to select particular publications
4978  */
4979 bool
4980 listPublications(const char *pattern)
4981 {
4982         PQExpBufferData buf;
4983         PGresult   *res;
4984         printQueryOpt myopt = pset.popt;
4985         static const bool translate_columns[] = {false, false, false, false, false};
4986
4987         if (pset.sversion < 100000)
4988         {
4989                 char            sverbuf[32];
4990                 psql_error("The server (version %s) does not support publications.\n",
4991                                    formatPGVersionNumber(pset.sversion, false,
4992                                                                                  sverbuf, sizeof(sverbuf)));
4993                 return true;
4994         }
4995
4996         initPQExpBuffer(&buf);
4997
4998         printfPQExpBuffer(&buf,
4999                                           "SELECT pubname AS \"%s\",\n"
5000                                           "  pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
5001                                           "  pubinsert AS \"%s\",\n"
5002                                           "  pubupdate AS \"%s\",\n"
5003                                           "  pubdelete AS \"%s\"\n",
5004                                           gettext_noop("Name"),
5005                                           gettext_noop("Owner"),
5006                                           gettext_noop("Inserts"),
5007                                           gettext_noop("Updates"),
5008                                           gettext_noop("Deletes"));
5009
5010         appendPQExpBufferStr(&buf,
5011                                                  "\nFROM pg_catalog.pg_publication\n");
5012
5013         processSQLNamePattern(pset.db, &buf, pattern, false, false,
5014                                                   NULL, "pubname", NULL,
5015                                                   NULL);
5016
5017         appendPQExpBufferStr(&buf, "ORDER BY 1;");
5018
5019         res = PSQLexec(buf.data);
5020         termPQExpBuffer(&buf);
5021         if (!res)
5022                 return false;
5023
5024         myopt.nullPrint = NULL;
5025         myopt.title = _("List of publications");
5026         myopt.translate_header = true;
5027         myopt.translate_columns = translate_columns;
5028         myopt.n_translate_columns = lengthof(translate_columns);
5029
5030         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5031
5032         PQclear(res);
5033
5034         return true;
5035 }
5036
5037 /* \dRp+
5038  * Describes publications including the contents.
5039  *
5040  * Takes an optional regexp to select particular publications
5041  */
5042 bool
5043 describePublications(const char *pattern)
5044 {
5045         PQExpBufferData buf;
5046         int                             i;
5047         PGresult           *res;
5048
5049         if (pset.sversion < 100000)
5050         {
5051                 char            sverbuf[32];
5052                 psql_error("The server (version %s) does not support publications.\n",
5053                                    formatPGVersionNumber(pset.sversion, false,
5054                                                                                  sverbuf, sizeof(sverbuf)));
5055                 return true;
5056         }
5057
5058         initPQExpBuffer(&buf);
5059
5060         printfPQExpBuffer(&buf,
5061                                           "SELECT oid, pubname, puballtables, pubinsert,\n"
5062                                           "  pubupdate, pubdelete\n"
5063                                           "FROM pg_catalog.pg_publication\n");
5064
5065         processSQLNamePattern(pset.db, &buf, pattern, false, false,
5066                                                   NULL, "pubname", NULL,
5067                                                   NULL);
5068
5069         appendPQExpBufferStr(&buf, "ORDER BY 2;");
5070
5071         res = PSQLexec(buf.data);
5072         if (!res)
5073         {
5074                 termPQExpBuffer(&buf);
5075                 return false;
5076         }
5077
5078         for (i = 0; i < PQntuples(res); i++)
5079         {
5080                 const char      align = 'l';
5081                 int                     ncols = 3;
5082                 int                     nrows = 1;
5083                 int                     tables = 0;
5084                 PGresult   *tabres;
5085                 char       *pubid = PQgetvalue(res, i, 0);
5086                 char       *pubname = PQgetvalue(res, i, 1);
5087                 bool            puballtables = strcmp(PQgetvalue(res, i, 2), "t") == 0;
5088                 int                     j;
5089                 PQExpBufferData title;
5090                 printTableOpt myopt = pset.popt.topt;
5091                 printTableContent cont;
5092
5093                 initPQExpBuffer(&title);
5094                 printfPQExpBuffer(&title, _("Publication %s"), pubname);
5095                 printTableInit(&cont, &myopt, title.data, ncols, nrows);
5096
5097                 printTableAddHeader(&cont, gettext_noop("Inserts"), true, align);
5098                 printTableAddHeader(&cont, gettext_noop("Updates"), true, align);
5099                 printTableAddHeader(&cont, gettext_noop("Deletes"), true, align);
5100
5101                 printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
5102                 printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
5103                 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
5104
5105                 if (puballtables)
5106                         printfPQExpBuffer(&buf,
5107                                                           "SELECT n.nspname, c.relname\n"
5108                                                           "FROM pg_catalog.pg_class c,\n"
5109                                                           "     pg_catalog.pg_namespace n\n"
5110                                                           "WHERE c.relnamespace = n.oid\n"
5111                                                           "  AND c.relkind = " CppAsString2(RELKIND_RELATION) "\n"
5112                                                           "  AND n.nspname <> 'pg_catalog'\n"
5113                                                           "  AND n.nspname <> 'information_schema'\n"
5114                                                           "ORDER BY 1,2");
5115                 else
5116                         printfPQExpBuffer(&buf,
5117                                                           "SELECT n.nspname, c.relname\n"
5118                                                           "FROM pg_catalog.pg_class c,\n"
5119                                                           "     pg_catalog.pg_namespace n,\n"
5120                                                           "     pg_catalog.pg_publication_rel pr\n"
5121                                                           "WHERE c.relnamespace = n.oid\n"
5122                                                           "  AND c.oid = pr.prrelid\n"
5123                                                           "  AND pr.prpubid = '%s'\n"
5124                                                           "ORDER BY 1,2", pubid);
5125
5126                 tabres = PSQLexec(buf.data);
5127                 if (!tabres)
5128                 {
5129                         printTableCleanup(&cont);
5130                         PQclear(res);
5131                         termPQExpBuffer(&buf);
5132                         termPQExpBuffer(&title);
5133                         return false;
5134                 }
5135                 else
5136                         tables = PQntuples(tabres);
5137
5138                 if (tables > 0)
5139                         printTableAddFooter(&cont, _("Tables:"));
5140
5141                 for (j = 0; j < tables; j++)
5142                 {
5143                         printfPQExpBuffer(&buf, "    \"%s.%s\"",
5144                                                           PQgetvalue(tabres, j, 0),
5145                                                           PQgetvalue(tabres, j, 1));
5146
5147                         printTableAddFooter(&cont, buf.data);
5148                 }
5149                 PQclear(tabres);
5150
5151                 printTable(&cont, pset.queryFout, false, pset.logfile);
5152                 printTableCleanup(&cont);
5153
5154                 termPQExpBuffer(&title);
5155         }
5156
5157         termPQExpBuffer(&buf);
5158         PQclear(res);
5159
5160         return true;
5161 }
5162
5163 /* \dRs
5164  * Describes subscriptions.
5165  *
5166  * Takes an optional regexp to select particular subscriptions
5167  */
5168 bool
5169 describeSubscriptions(const char *pattern, bool verbose)
5170 {
5171         PQExpBufferData buf;
5172         PGresult   *res;
5173         printQueryOpt myopt = pset.popt;
5174         static const bool translate_columns[] = {false, false, false, false, false};
5175
5176         if (pset.sversion < 100000)
5177         {
5178                 char            sverbuf[32];
5179                 psql_error("The server (version %s) does not support subscriptions.\n",
5180                                    formatPGVersionNumber(pset.sversion, false,
5181                                                                                  sverbuf, sizeof(sverbuf)));
5182                 return true;
5183         }
5184
5185         initPQExpBuffer(&buf);
5186
5187         printfPQExpBuffer(&buf,
5188                                           "SELECT subname AS \"%s\"\n"
5189                                           ",  pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n"
5190                                           ",  subenabled AS \"%s\"\n"
5191                                           ",  subpublications AS \"%s\"\n",
5192                                           gettext_noop("Name"),
5193                                           gettext_noop("Owner"),
5194                                           gettext_noop("Enabled"),
5195                                           gettext_noop("Publication"));
5196
5197         if (verbose)
5198         {
5199                 appendPQExpBuffer(&buf,
5200                                                   ",  subconninfo AS \"%s\"\n",
5201                                                   gettext_noop("Conninfo"));
5202         }
5203
5204         /* Only display subscriptions in current database. */
5205         appendPQExpBufferStr(&buf,
5206                                                  "FROM pg_catalog.pg_subscription\n"
5207                                                  "WHERE subdbid = (SELECT oid\n"
5208                                                  "                 FROM pg_catalog.pg_database\n"
5209                                                  "                 WHERE datname = current_database())");
5210
5211         processSQLNamePattern(pset.db, &buf, pattern, true, false,
5212                                                   NULL, "subname", NULL,
5213                                                   NULL);
5214
5215         appendPQExpBufferStr(&buf, "ORDER BY 1;");
5216
5217         res = PSQLexec(buf.data);
5218         termPQExpBuffer(&buf);
5219         if (!res)
5220                 return false;
5221
5222         myopt.nullPrint = NULL;
5223         myopt.title = _("List of subscriptions");
5224         myopt.translate_header = true;
5225         myopt.translate_columns = translate_columns;
5226         myopt.n_translate_columns = lengthof(translate_columns);
5227
5228         printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5229
5230         PQclear(res);
5231         return true;
5232 }
5233
5234 /*
5235  * printACLColumn
5236  *
5237  * Helper function for consistently formatting ACL (privilege) columns.
5238  * The proper targetlist entry is appended to buf.  Note lack of any
5239  * whitespace or comma decoration.
5240  */
5241 static void
5242 printACLColumn(PQExpBuffer buf, const char *colname)
5243 {
5244         if (pset.sversion >= 80100)
5245                 appendPQExpBuffer(buf,
5246                                                   "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
5247                                                   colname, gettext_noop("Access privileges"));
5248         else
5249                 appendPQExpBuffer(buf,
5250                                                   "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
5251                                                   colname, gettext_noop("Access privileges"));
5252 }