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

   1 /*
   2  * psql - the PostgreSQL interactive terminal
   3  *
   4  * Copyright (c) 2000-2007, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.149 2007/01/05 22:19:49 momjian Exp $
   7  */
   8 #include "postgres_fe.h"
   9 #include "describe.h"
  10
  11 #include "dumputils.h"
  12
  13 #include "common.h"
  14 #include "settings.h"
  15 #include "print.h"
  16 #include "variables.h"
  17
  18 #include <ctype.h>
  19
  20 #ifdef WIN32
  21 /*
  22  * mbvalidate() is used in function describeOneTableDetails() to make sure
  23  * all characters of the cells will be printed to the DOS console in a
  24  * correct way
  25  */
  26 #include "mbprint.h"
  27 #endif
  28
  29
  30 static bool describeOneTableDetails(const char *schemaname,
  31                                                 const char *relationname,
  32                                                 const char *oid,
  33                                                 bool verbose);
  34 static bool add_tablespace_footer(char relkind, Oid tablespace, char **footers,
  35                                           int *count, PQExpBufferData buf, bool newline);
  36
  37 /*----------------
  38  * Handlers for various slash commands displaying some sort of list
  39  * of things in the database.
  40  *
  41  * If you add something here, try to format the query to look nice in -E output.
  42  *----------------
  43  */
  44
  45
  46 /* \da
  47  * Takes an optional regexp to select particular aggregates
  48  */
  49 bool
  50 describeAggregates(const char *pattern, bool verbose)
  51 {
  52         PQExpBufferData buf;
  53         PGresult   *res;
  54         printQueryOpt myopt = pset.popt;
  55
  56         initPQExpBuffer(&buf);
  57
  58         /*
  59          * There are two kinds of aggregates: ones that work on particular types
  60          * and ones that work on all (denoted by input type = "any")
  61          */
  62         printfPQExpBuffer(&buf,
  63                                           "SELECT n.nspname as \"%s\",\n"
  64                                           "  p.proname AS \"%s\",\n"
  65                                           "  CASE WHEN p.pronargs = 0\n"
  66                                           "    THEN CAST('*' AS pg_catalog.text)\n"
  67                                           "    ELSE\n"
  68                                           "    pg_catalog.array_to_string(ARRAY(\n"
  69                                           "      SELECT\n"
  70                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
  71                                           "      FROM\n"
  72                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
  73                                           "    ), ', ')\n"
  74                                           "  END AS \"%s\",\n"
  75                                  "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
  76                                           "FROM pg_catalog.pg_proc p\n"
  77            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  78                                           "WHERE p.proisagg\n",
  79                                           _("Schema"), _("Name"),
  80                                           _("Argument data types"), _("Description"));
  81
  82         processSQLNamePattern(pset.db, &buf, pattern, true, false,
  83                                                   "n.nspname", "p.proname", NULL,
  84                                                   "pg_catalog.pg_function_is_visible(p.oid)");
  85
  86         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
  87
  88         res = PSQLexec(buf.data, false);
  89         termPQExpBuffer(&buf);
  90         if (!res)
  91                 return false;
  92
  93         myopt.nullPrint = NULL;
  94         myopt.title = _("List of aggregate functions");
  95
  96         printQuery(res, &myopt, pset.queryFout, pset.logfile);
  97
  98         PQclear(res);
  99         return true;
 100 }
 101
 102 /* \db
 103  * Takes an optional regexp to select particular tablespaces
 104  */
 105 bool
 106 describeTablespaces(const char *pattern, bool verbose)
 107 {
 108         PQExpBufferData buf;
 109         PGresult   *res;
 110         printQueryOpt myopt = pset.popt;
 111
 112         if (pset.sversion < 80000)
 113         {
 114                 fprintf(stderr, _("The server version (%d) does not support tablespaces.\n"),
 115                                 pset.sversion);
 116                 return true;
 117         }
 118
 119         initPQExpBuffer(&buf);
 120
 121         printfPQExpBuffer(&buf,
 122                                           "SELECT spcname AS \"%s\",\n"
 123                                           "  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
 124                                           "  spclocation AS \"%s\"",
 125                                           _("Name"), _("Owner"), _("Location"));
 126
 127         if (verbose)
 128                 appendPQExpBuffer(&buf,
 129                                                   ",\n  spcacl as \"%s\""
 130                  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 131                                                   _("Access privileges"), _("Description"));
 132
 133         appendPQExpBuffer(&buf,
 134                                           "\nFROM pg_catalog.pg_tablespace\n");
 135
 136         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 137                                                   NULL, "spcname", NULL,
 138                                                   NULL);
 139
 140         appendPQExpBuffer(&buf, "ORDER BY 1;");
 141
 142         res = PSQLexec(buf.data, false);
 143         termPQExpBuffer(&buf);
 144         if (!res)
 145                 return false;
 146
 147         myopt.nullPrint = NULL;
 148         myopt.title = _("List of tablespaces");
 149
 150         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 151
 152         PQclear(res);
 153         return true;
 154 }
 155
 156
 157 /* \df
 158  * Takes an optional regexp to select particular functions
 159  */
 160 bool
 161 describeFunctions(const char *pattern, bool verbose)
 162 {
 163         PQExpBufferData buf;
 164         PGresult   *res;
 165         printQueryOpt myopt = pset.popt;
 166
 167         initPQExpBuffer(&buf);
 168
 169         printfPQExpBuffer(&buf,
 170                                           "SELECT n.nspname as \"%s\",\n"
 171                                           "  p.proname as \"%s\",\n"
 172                                           "  CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||\n"
 173                                   "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
 174                                           "  CASE WHEN proallargtypes IS NOT NULL THEN\n"
 175                                           "    pg_catalog.array_to_string(ARRAY(\n"
 176                                           "      SELECT\n"
 177                                           "        CASE\n"
 178                                           "          WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
 179                                           "          WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
 180                                         "          WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
 181                                           "        END ||\n"
 182                                           "        CASE\n"
 183                          "          WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
 184                                           "          ELSE p.proargnames[s.i] || ' ' \n"
 185                                           "        END ||\n"
 186                           "        pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
 187                                           "      FROM\n"
 188                                           "        pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
 189                                           "    ), ', ')\n"
 190                                           "  ELSE\n"
 191                                           "    pg_catalog.array_to_string(ARRAY(\n"
 192                                           "      SELECT\n"
 193                                           "        CASE\n"
 194                    "          WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
 195                                           "          ELSE p.proargnames[s.i+1] || ' '\n"
 196                                           "          END ||\n"
 197                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
 198                                           "      FROM\n"
 199                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
 200                                           "    ), ', ')\n"
 201                                           "  END AS \"%s\"",
 202                                           _("Schema"), _("Name"), _("Result data type"),
 203                                           _("Argument data types"));
 204
 205         if (verbose)
 206                 appendPQExpBuffer(&buf,
 207                                                   ",\n  r.rolname as \"%s\",\n"
 208                                                   "  l.lanname as \"%s\",\n"
 209                                                   "  p.prosrc as \"%s\",\n"
 210                                   "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
 211                                                   _("Owner"), _("Language"),
 212                                                   _("Source code"), _("Description"));
 213
 214         if (!verbose)
 215                 appendPQExpBuffer(&buf,
 216                                                   "\nFROM pg_catalog.pg_proc p"
 217                                                   "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
 218         else
 219                 appendPQExpBuffer(&buf,
 220                                                   "\nFROM pg_catalog.pg_proc p"
 221                 "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
 222                          "\n     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
 223                                 "\n     JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
 224
 225         /*
 226          * we skip in/out funcs by excluding functions that take or return cstring
 227          */
 228         appendPQExpBuffer(&buf,
 229                    "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 230                                           "      AND (p.proargtypes[0] IS NULL\n"
 231                                           "      OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
 232                                           "      AND NOT p.proisagg\n");
 233
 234         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 235                                                   "n.nspname", "p.proname", NULL,
 236                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 237
 238         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
 239
 240         res = PSQLexec(buf.data, false);
 241         termPQExpBuffer(&buf);
 242         if (!res)
 243                 return false;
 244
 245         myopt.nullPrint = NULL;
 246         myopt.title = _("List of functions");
 247
 248         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 249
 250         PQclear(res);
 251         return true;
 252 }
 253
 254
 255
 256 /*
 257  * \dT
 258  * describe types
 259  */
 260 bool
 261 describeTypes(const char *pattern, bool verbose)
 262 {
 263         PQExpBufferData buf;
 264         PGresult   *res;
 265         printQueryOpt myopt = pset.popt;
 266
 267         initPQExpBuffer(&buf);
 268
 269         printfPQExpBuffer(&buf,
 270                                           "SELECT n.nspname as \"%s\",\n"
 271                                           "  pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
 272                                           _("Schema"), _("Name"));
 273         if (verbose)
 274                 appendPQExpBuffer(&buf,
 275                                                   "  t.typname AS \"%s\",\n"
 276                                                   "  CASE WHEN t.typrelid != 0\n"
 277                                                   "      THEN CAST('tuple' AS pg_catalog.text)\n"
 278                                                   "    WHEN t.typlen < 0\n"
 279                                                   "      THEN CAST('var' AS pg_catalog.text)\n"
 280                                                   "    ELSE CAST(t.typlen AS pg_catalog.text)\n"
 281                                                   "  END AS \"%s\",\n",
 282                                                   _("Internal name"), _("Size"));
 283         appendPQExpBuffer(&buf,
 284                                 "  pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
 285                                           _("Description"));
 286
 287         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
 288          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
 289
 290         /*
 291          * do not include array types (start with underscore); do not include
 292          * complex types (typrelid!=0) unless they are standalone composite types
 293          */
 294         appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
 295         appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
 296                                           "WHERE c.oid = t.typrelid)) ");
 297         appendPQExpBuffer(&buf, "AND t.typname !~ '^_'\n");
 298
 299         /* Match name pattern against either internal or external name */
 300         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 301                                                   "n.nspname", "t.typname",
 302                                                   "pg_catalog.format_type(t.oid, NULL)",
 303                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 304
 305         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 306
 307         res = PSQLexec(buf.data, false);
 308         termPQExpBuffer(&buf);
 309         if (!res)
 310                 return false;
 311
 312         myopt.nullPrint = NULL;
 313         myopt.title = _("List of data types");
 314
 315         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 316
 317         PQclear(res);
 318         return true;
 319 }
 320
 321
 322
 323 /* \do
 324  */
 325 bool
 326 describeOperators(const char *pattern)
 327 {
 328         PQExpBufferData buf;
 329         PGresult   *res;
 330         printQueryOpt myopt = pset.popt;
 331
 332         initPQExpBuffer(&buf);
 333
 334         printfPQExpBuffer(&buf,
 335                                           "SELECT n.nspname as \"%s\",\n"
 336                                           "  o.oprname AS \"%s\",\n"
 337                                           "  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
 338                                           "  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
 339                                    "  pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
 340                          "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
 341         "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
 342                                           "FROM pg_catalog.pg_operator o\n"
 343           "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 344                                           _("Schema"), _("Name"),
 345                                           _("Left arg type"), _("Right arg type"),
 346                                           _("Result type"), _("Description"));
 347
 348         processSQLNamePattern(pset.db, &buf, pattern, false, true,
 349                                                   "n.nspname", "o.oprname", NULL,
 350                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 351
 352         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
 353
 354         res = PSQLexec(buf.data, false);
 355         termPQExpBuffer(&buf);
 356         if (!res)
 357                 return false;
 358
 359         myopt.nullPrint = NULL;
 360         myopt.title = _("List of operators");
 361
 362         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 363
 364         PQclear(res);
 365         return true;
 366 }
 367
 368
 369 /*
 370  * listAllDbs
 371  *
 372  * for \l, \list, and -l switch
 373  */
 374 bool
 375 listAllDbs(bool verbose)
 376 {
 377         PGresult   *res;
 378         PQExpBufferData buf;
 379         printQueryOpt myopt = pset.popt;
 380
 381         initPQExpBuffer(&buf);
 382
 383         printfPQExpBuffer(&buf,
 384                                           "SELECT d.datname as \"%s\",\n"
 385                                           "       r.rolname as \"%s\"",
 386                                           _("Name"), _("Owner"));
 387         appendPQExpBuffer(&buf,
 388                         ",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
 389                                           _("Encoding"));
 390         if (verbose)
 391         {
 392                 appendPQExpBuffer(&buf,
 393                                                   ",\n       t.spcname as \"%s\"",
 394                                                   _("Tablespace"));
 395                 appendPQExpBuffer(&buf,
 396                                                   ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
 397                                                   _("Description"));
 398         }
 399         appendPQExpBuffer(&buf,
 400                                           "\nFROM pg_catalog.pg_database d"
 401                                           "\n  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n");
 402         if (verbose)
 403                 appendPQExpBuffer(&buf,
 404                    "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 405         appendPQExpBuffer(&buf, "ORDER BY 1;");
 406         res = PSQLexec(buf.data, false);
 407         termPQExpBuffer(&buf);
 408         if (!res)
 409                 return false;
 410
 411         myopt.nullPrint = NULL;
 412         myopt.title = _("List of databases");
 413
 414         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 415
 416         PQclear(res);
 417         return true;
 418 }
 419
 420
 421 /*
 422  * List Tables Grant/Revoke Permissions
 423  * \z (now also \dp -- perhaps more mnemonic)
 424  */
 425 bool
 426 permissionsList(const char *pattern)
 427 {
 428         PQExpBufferData buf;
 429         PGresult   *res;
 430         printQueryOpt myopt = pset.popt;
 431
 432         initPQExpBuffer(&buf);
 433
 434         /*
 435          * we ignore indexes and toast tables since they have no meaningful rights
 436          */
 437         printfPQExpBuffer(&buf,
 438                                           "SELECT n.nspname as \"%s\",\n"
 439                                           "  c.relname as \"%s\",\n"
 440                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
 441                                           "  c.relacl as \"%s\"\n"
 442                                           "FROM pg_catalog.pg_class c\n"
 443            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 444                                           "WHERE c.relkind IN ('r', 'v', 'S')\n",
 445                                           _("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
 446
 447         /*
 448          * Unless a schema pattern is specified, we suppress system and temp
 449          * tables, since they normally aren't very interesting from a permissions
 450          * point of view.  You can see 'em by explicit request though, eg with \z
 451          * pg_catalog.*
 452          */
 453         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 454                                                   "n.nspname", "c.relname", NULL,
 455                         "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
 456
 457         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 458
 459         res = PSQLexec(buf.data, false);
 460         if (!res)
 461         {
 462                 termPQExpBuffer(&buf);
 463                 return false;
 464         }
 465
 466         myopt.nullPrint = NULL;
 467         printfPQExpBuffer(&buf, _("Access privileges for database \"%s\""), PQdb(pset.db));
 468         myopt.title = buf.data;
 469
 470         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 471
 472         termPQExpBuffer(&buf);
 473         PQclear(res);
 474         return true;
 475 }
 476
 477
 478
 479 /*
 480  * Get object comments
 481  *
 482  * \dd [foo]
 483  *
 484  * Note: This only lists things that actually have a description. For complete
 485  * lists of things, there are other \d? commands.
 486  */
 487 bool
 488 objectDescription(const char *pattern)
 489 {
 490         PQExpBufferData buf;
 491         PGresult   *res;
 492         printQueryOpt myopt = pset.popt;
 493
 494         initPQExpBuffer(&buf);
 495
 496         appendPQExpBuffer(&buf,
 497                                           "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
 498                                           "FROM (\n",
 499                                           _("Schema"), _("Name"), _("Object"), _("Description"));
 500
 501         /* Aggregate descriptions */
 502         appendPQExpBuffer(&buf,
 503                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 504                                           "  n.nspname as nspname,\n"
 505                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 506                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 507                                           "  FROM pg_catalog.pg_proc p\n"
 508          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 509                                           "  WHERE p.proisagg\n",
 510                                           _("aggregate"));
 511         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 512                                                   "n.nspname", "p.proname", NULL,
 513                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 514
 515         /* Function descriptions (except in/outs for datatypes) */
 516         appendPQExpBuffer(&buf,
 517                                           "UNION ALL\n"
 518                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 519                                           "  n.nspname as nspname,\n"
 520                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 521                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 522                                           "  FROM pg_catalog.pg_proc p\n"
 523          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 524
 525                  "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 526                                           "      AND (p.proargtypes[0] IS NULL\n"
 527                                           "      OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
 528                                           "      AND NOT p.proisagg\n",
 529                                           _("function"));
 530         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 531                                                   "n.nspname", "p.proname", NULL,
 532                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 533
 534         /* Operator descriptions (only if operator has its own comment) */
 535         appendPQExpBuffer(&buf,
 536                                           "UNION ALL\n"
 537                                           "  SELECT o.oid as oid, o.tableoid as tableoid,\n"
 538                                           "  n.nspname as nspname,\n"
 539                                           "  CAST(o.oprname AS pg_catalog.text) as name,"
 540                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 541                                           "  FROM pg_catalog.pg_operator o\n"
 542         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 543                                           _("operator"));
 544         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 545                                                   "n.nspname", "o.oprname", NULL,
 546                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 547
 548         /* Type description */
 549         appendPQExpBuffer(&buf,
 550                                           "UNION ALL\n"
 551                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 552                                           "  n.nspname as nspname,\n"
 553                                           "  pg_catalog.format_type(t.oid, NULL) as name,"
 554                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 555                                           "  FROM pg_catalog.pg_type t\n"
 556         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
 557                                           _("data type"));
 558         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 559                                                   "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
 560                                                   NULL,
 561                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 562
 563         /* Relation (tables, views, indexes, sequences) descriptions */
 564         appendPQExpBuffer(&buf,
 565                                           "UNION ALL\n"
 566                                           "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
 567                                           "  n.nspname as nspname,\n"
 568                                           "  CAST(c.relname AS pg_catalog.text) as name,\n"
 569                                           "  CAST(\n"
 570                                           "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
 571                                           "  AS pg_catalog.text) as object\n"
 572                                           "  FROM pg_catalog.pg_class c\n"
 573          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 574                                           "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
 575                                           _("table"), _("view"), _("index"), _("sequence"));
 576         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 577                                                   "n.nspname", "c.relname", NULL,
 578                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 579
 580         /* Rule description (ignore rules for views) */
 581         appendPQExpBuffer(&buf,
 582                                           "UNION ALL\n"
 583                                           "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
 584                                           "  n.nspname as nspname,\n"
 585                                           "  CAST(r.rulename AS pg_catalog.text) as name,"
 586                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 587                                           "  FROM pg_catalog.pg_rewrite r\n"
 588                                   "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
 589          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 590                                           "  WHERE r.rulename != '_RETURN'\n",
 591                                           _("rule"));
 592         /* XXX not sure what to do about visibility rule here? */
 593         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 594                                                   "n.nspname", "r.rulename", NULL,
 595                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 596
 597         /* Trigger description */
 598         appendPQExpBuffer(&buf,
 599                                           "UNION ALL\n"
 600                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 601                                           "  n.nspname as nspname,\n"
 602                                           "  CAST(t.tgname AS pg_catalog.text) as name,"
 603                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 604                                           "  FROM pg_catalog.pg_trigger t\n"
 605                                    "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
 606         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
 607                                           _("trigger"));
 608         /* XXX not sure what to do about visibility rule here? */
 609         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 610                                                   "n.nspname", "t.tgname", NULL,
 611                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 612
 613         appendPQExpBuffer(&buf,
 614                                           ") AS tt\n"
 615                                           "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
 616
 617         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
 618
 619         res = PSQLexec(buf.data, false);
 620         termPQExpBuffer(&buf);
 621         if (!res)
 622                 return false;
 623
 624         myopt.nullPrint = NULL;
 625         myopt.title = _("Object descriptions");
 626
 627         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 628
 629         PQclear(res);
 630         return true;
 631 }
 632
 633
 634
 635 /*
 636  * describeTableDetails (for \d)
 637  *
 638  * This routine finds the tables to be displayed, and calls
 639  * describeOneTableDetails for each one.
 640  *
 641  * verbose: if true, this is \d+
 642  */
 643 bool
 644 describeTableDetails(const char *pattern, bool verbose)
 645 {
 646         PQExpBufferData buf;
 647         PGresult   *res;
 648         int                     i;
 649
 650         initPQExpBuffer(&buf);
 651
 652         printfPQExpBuffer(&buf,
 653                                           "SELECT c.oid,\n"
 654                                           "  n.nspname,\n"
 655                                           "  c.relname\n"
 656                                           "FROM pg_catalog.pg_class c\n"
 657          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
 658
 659         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 660                                                   "n.nspname", "c.relname", NULL,
 661                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 662
 663         appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
 664
 665         res = PSQLexec(buf.data, false);
 666         termPQExpBuffer(&buf);
 667         if (!res)
 668                 return false;
 669
 670         if (PQntuples(res) == 0)
 671         {
 672                 if (!pset.quiet)
 673                         fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
 674                                         pattern);
 675                 PQclear(res);
 676                 return false;
 677         }
 678
 679         for (i = 0; i < PQntuples(res); i++)
 680         {
 681                 const char *oid;
 682                 const char *nspname;
 683                 const char *relname;
 684
 685                 oid = PQgetvalue(res, i, 0);
 686                 nspname = PQgetvalue(res, i, 1);
 687                 relname = PQgetvalue(res, i, 2);
 688
 689                 if (!describeOneTableDetails(nspname, relname, oid, verbose))
 690                 {
 691                         PQclear(res);
 692                         return false;
 693                 }
 694                 if (cancel_pressed)
 695                 {
 696                         PQclear(res);
 697                         return false;
 698                 }
 699         }
 700
 701         PQclear(res);
 702         return true;
 703 }
 704
 705 /*
 706  * describeOneTableDetails (for \d)
 707  *
 708  * Unfortunately, the information presented here is so complicated that it
 709  * cannot be done in a single query. So we have to assemble the printed table
 710  * by hand and pass it to the underlying printTable() function.
 711  */
 712 static bool
 713 describeOneTableDetails(const char *schemaname,
 714                                                 const char *relationname,
 715                                                 const char *oid,
 716                                                 bool verbose)
 717 {
 718         PQExpBufferData buf;
 719         PGresult   *res = NULL;
 720         printTableOpt myopt = pset.popt.topt;
 721         int                     i;
 722         char       *view_def = NULL;
 723         const char *headers[5];
 724         char      **cells = NULL;
 725         char      **footers = NULL;
 726         char      **ptr;
 727         PQExpBufferData title;
 728         PQExpBufferData tmpbuf;
 729         int                     cols = 0;
 730         int                     numrows = 0;
 731         struct
 732         {
 733                 int16           checks;
 734                 int16           triggers;
 735                 char            relkind;
 736                 bool            hasindex;
 737                 bool            hasrules;
 738                 bool            hasoids;
 739                 Oid                     tablespace;
 740         }                       tableinfo;
 741         bool            show_modifiers = false;
 742         bool            retval;
 743
 744         retval = false;
 745
 746         /* This output looks confusing in expanded mode. */
 747         myopt.expanded = false;
 748
 749         initPQExpBuffer(&buf);
 750         initPQExpBuffer(&title);
 751         initPQExpBuffer(&tmpbuf);
 752
 753         /* Get general table info */
 754         printfPQExpBuffer(&buf,
 755            "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n"
 756                                           "relhasoids %s \n"
 757                                           "FROM pg_catalog.pg_class WHERE oid = '%s'",
 758                                           pset.sversion >= 80000 ? ", reltablespace" : "",
 759                                           oid);
 760         res = PSQLexec(buf.data, false);
 761         if (!res)
 762                 goto error_return;
 763
 764         /* Did we get anything? */
 765         if (PQntuples(res) == 0)
 766         {
 767                 if (!pset.quiet)
 768                         fprintf(stderr, _("Did not find any relation with OID %s.\n"),
 769                                         oid);
 770                 goto error_return;
 771         }
 772
 773         /* FIXME: check for null pointers here? */
 774         tableinfo.checks = atoi(PQgetvalue(res, 0, 2));
 775         tableinfo.triggers = atoi(PQgetvalue(res, 0, 3));
 776         tableinfo.relkind = *(PQgetvalue(res, 0, 1));
 777         tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
 778         tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
 779         tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
 780         tableinfo.tablespace = (pset.sversion >= 80000) ?
 781                 atooid(PQgetvalue(res, 0, 6)) : 0;
 782         PQclear(res);
 783
 784         headers[0] = _("Column");
 785         headers[1] = _("Type");
 786         cols = 2;
 787
 788         if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
 789         {
 790                 show_modifiers = true;
 791                 cols++;
 792                 headers[cols - 1] = _("Modifiers");
 793         }
 794
 795         if (verbose)
 796         {
 797                 cols++;
 798                 headers[cols - 1] = _("Description");
 799         }
 800
 801         headers[cols] = NULL;
 802
 803         /* Get column info (index requires additional checks) */
 804         printfPQExpBuffer(&buf, "SELECT a.attname,");
 805         appendPQExpBuffer(&buf, "\n  pg_catalog.format_type(a.atttypid, a.atttypmod),"
 806                                           "\n  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
 807                                           "\n   FROM pg_catalog.pg_attrdef d"
 808                                           "\n   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
 809                                           "\n  a.attnotnull, a.attnum");
 810         if (verbose)
 811                 appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
 812         appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
 813         if (tableinfo.relkind == 'i')
 814                 appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
 815         appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
 816         if (tableinfo.relkind == 'i')
 817                 appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
 818         appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
 819
 820         res = PSQLexec(buf.data, false);
 821         if (!res)
 822                 goto error_return;
 823         numrows = PQntuples(res);
 824
 825         /* Check if table is a view */
 826         if (tableinfo.relkind == 'v')
 827         {
 828                 PGresult   *result;
 829
 830                 printfPQExpBuffer(&buf, "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)", oid);
 831                 result = PSQLexec(buf.data, false);
 832                 if (!result)
 833                         goto error_return;
 834
 835                 if (PQntuples(result) > 0)
 836                         view_def = pg_strdup(PQgetvalue(result, 0, 0));
 837
 838                 PQclear(result);
 839         }
 840
 841         /* Generate table cells to be printed */
 842         /* note: initialize all cells[] to NULL in case of error exit */
 843         cells = pg_malloc_zero((numrows * cols + 1) * sizeof(*cells));
 844
 845         for (i = 0; i < numrows; i++)
 846         {
 847                 /* Name */
 848 #ifdef WIN32
 849                 cells[i * cols + 0] = mbvalidate(PQgetvalue(res, i, 0), myopt.encoding);
 850 #else
 851                 cells[i * cols + 0] = PQgetvalue(res, i, 0);    /* don't free this
 852                                                                                                                  * afterwards */
 853 #endif
 854
 855                 /* Type */
 856 #ifdef WIN32
 857                 cells[i * cols + 1] = mbvalidate(PQgetvalue(res, i, 1), myopt.encoding);
 858 #else
 859                 cells[i * cols + 1] = PQgetvalue(res, i, 1);    /* don't free this
 860                                                                                                                  * either */
 861 #endif
 862
 863                 /* Extra: not null and default */
 864                 if (show_modifiers)
 865                 {
 866                         resetPQExpBuffer(&tmpbuf);
 867                         if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
 868                                 appendPQExpBufferStr(&tmpbuf, "not null");
 869
 870                         /* handle "default" here */
 871                         /* (note: above we cut off the 'default' string at 128) */
 872                         if (strlen(PQgetvalue(res, i, 2)) != 0)
 873                         {
 874                                 if (tmpbuf.len > 0)
 875                                         appendPQExpBufferStr(&tmpbuf, " ");
 876                                 appendPQExpBuffer(&tmpbuf, "default %s",
 877                                                                   PQgetvalue(res, i, 2));
 878                         }
 879
 880 #ifdef WIN32
 881                         cells[i * cols + 2] = pg_strdup(mbvalidate(tmpbuf.data, myopt.encoding));
 882 #else
 883                         cells[i * cols + 2] = pg_strdup(tmpbuf.data);
 884 #endif
 885                 }
 886
 887                 /* Description */
 888                 if (verbose)
 889 #ifdef WIN32
 890                         cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res, i, 5), myopt.encoding);
 891 #else
 892                         cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
 893 #endif
 894         }
 895
 896         /* Make title */
 897         switch (tableinfo.relkind)
 898         {
 899                 case 'r':
 900                         printfPQExpBuffer(&title, _("Table \"%s.%s\""),
 901                                                           schemaname, relationname);
 902                         break;
 903                 case 'v':
 904                         printfPQExpBuffer(&title, _("View \"%s.%s\""),
 905                                                           schemaname, relationname);
 906                         break;
 907                 case 'S':
 908                         printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
 909                                                           schemaname, relationname);
 910                         break;
 911                 case 'i':
 912                         printfPQExpBuffer(&title, _("Index \"%s.%s\""),
 913                                                           schemaname, relationname);
 914                         break;
 915                 case 's':
 916                         /* not used as of 8.2, but keep it for backwards compatibility */
 917                         printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
 918                                                           schemaname, relationname);
 919                         break;
 920                 case 't':
 921                         printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
 922                                                           schemaname, relationname);
 923                         break;
 924                 case 'c':
 925                         printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
 926                                                           schemaname, relationname);
 927                         break;
 928                 default:
 929                         printfPQExpBuffer(&title, _("?%c? \"%s.%s\""),
 930                                                           tableinfo.relkind, schemaname, relationname);
 931                         break;
 932         }
 933
 934         /* Make footers */
 935         if (tableinfo.relkind == 'i')
 936         {
 937                 /* Footer information about an index */
 938                 PGresult   *result;
 939
 940                 printfPQExpBuffer(&buf,
 941                                                   "SELECT i.indisunique, i.indisprimary, i.indisclustered, i.indisvalid, a.amname, c2.relname,\n"
 942                                         "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
 943                                                   "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
 944                   "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
 945                                                   "AND i.indrelid = c2.oid",
 946                                                   oid);
 947
 948                 result = PSQLexec(buf.data, false);
 949                 if (!result)
 950                         goto error_return;
 951                 else if (PQntuples(result) != 1)
 952                 {
 953                         PQclear(result);
 954                         goto error_return;
 955                 }
 956                 else
 957                 {
 958                         char       *indisunique = PQgetvalue(result, 0, 0);
 959                         char       *indisprimary = PQgetvalue(result, 0, 1);
 960                         char       *indisclustered = PQgetvalue(result, 0, 2);
 961                         char       *indisvalid = PQgetvalue(result, 0, 3);
 962                         char       *indamname = PQgetvalue(result, 0, 4);
 963                         char       *indtable = PQgetvalue(result, 0, 5);
 964                         char       *indpred = PQgetvalue(result, 0, 6);
 965                         int                     count_footers = 0;
 966
 967                         if (strcmp(indisprimary, "t") == 0)
 968                                 printfPQExpBuffer(&tmpbuf, _("primary key, "));
 969                         else if (strcmp(indisunique, "t") == 0)
 970                                 printfPQExpBuffer(&tmpbuf, _("unique, "));
 971                         else
 972                                 resetPQExpBuffer(&tmpbuf);
 973                         appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
 974
 975                         /* we assume here that index and table are in same schema */
 976                         appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
 977                                                           schemaname, indtable);
 978
 979                         if (strlen(indpred))
 980                                 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
 981
 982                         if (strcmp(indisclustered, "t") == 0)
 983                                 appendPQExpBuffer(&tmpbuf, _(", clustered"));
 984
 985                         if (strcmp(indisvalid, "t") != 0)
 986                                 appendPQExpBuffer(&tmpbuf, _(", invalid"));
 987
 988                         footers = pg_malloc_zero(4 * sizeof(*footers));
 989                         footers[count_footers++] = pg_strdup(tmpbuf.data);
 990                         add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
 991                                                                   footers, &count_footers, tmpbuf, true);
 992                         footers[count_footers] = NULL;
 993
 994                 }
 995
 996                 PQclear(result);
 997         }
 998         else if (view_def)
 999         {
1000                 PGresult   *result = NULL;
1001                 int                     rule_count = 0;
1002                 int                     count_footers = 0;
1003
1004                 /* count rules other than the view rule */
1005                 if (tableinfo.hasrules)
1006                 {
1007                         printfPQExpBuffer(&buf,
1008                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1009                                                           "FROM pg_catalog.pg_rewrite r\n"
1010                         "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1011                                                           oid);
1012                         result = PSQLexec(buf.data, false);
1013                         if (!result)
1014                                 goto error_return;
1015                         else
1016                                 rule_count = PQntuples(result);
1017                 }
1018
1019                 /* Footer information about a view */
1020                 footers = pg_malloc_zero((rule_count + 3) * sizeof(*footers));
1021                 footers[count_footers] = pg_malloc(64 + strlen(view_def));
1022                 snprintf(footers[count_footers], 64 + strlen(view_def),
1023                                  _("View definition:\n%s"), view_def);
1024                 count_footers++;
1025
1026                 /* print rules */
1027                 if (rule_count > 0)
1028                 {
1029                         printfPQExpBuffer(&buf, _("Rules:"));
1030                         footers[count_footers++] = pg_strdup(buf.data);
1031                         for (i = 0; i < rule_count; i++)
1032                         {
1033                                 const char *ruledef;
1034
1035                                 /* Everything after "CREATE RULE" is echoed verbatim */
1036                                 ruledef = PQgetvalue(result, i, 1);
1037                                 ruledef += 12;
1038
1039                                 printfPQExpBuffer(&buf, " %s", ruledef);
1040
1041                                 footers[count_footers++] = pg_strdup(buf.data);
1042                         }
1043                         PQclear(result);
1044                 }
1045
1046                 footers[count_footers] = NULL;
1047
1048         }
1049         else if (tableinfo.relkind == 'r')
1050         {
1051                 /* Footer information about a table */
1052                 PGresult   *result1 = NULL,
1053                                    *result2 = NULL,
1054                                    *result3 = NULL,
1055                                    *result4 = NULL,
1056                                    *result5 = NULL,
1057                                    *result6 = NULL;
1058                 int                     check_count = 0,
1059                                         index_count = 0,
1060                                         foreignkey_count = 0,
1061                                         rule_count = 0,
1062                                         trigger_count = 0,
1063                                         inherits_count = 0;
1064                 int                     count_footers = 0;
1065
1066                 /* count indexes */
1067                 if (tableinfo.hasindex)
1068                 {
1069                         printfPQExpBuffer(&buf,
1070                                                           "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, "
1071                                                           "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace\n"
1072                                                           "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
1073                                                           "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1074                           "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1075                                                           oid);
1076                         result1 = PSQLexec(buf.data, false);
1077                         if (!result1)
1078                                 goto error_return;
1079                         else
1080                                 index_count = PQntuples(result1);
1081                 }
1082
1083                 /* count table (and column) check constraints */
1084                 if (tableinfo.checks)
1085                 {
1086                         printfPQExpBuffer(&buf,
1087                                                           "SELECT r.conname, "
1088                                                           "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1089                                                           "FROM pg_catalog.pg_constraint r\n"
1090                                         "WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1",
1091                                                           oid);
1092                         result2 = PSQLexec(buf.data, false);
1093                         if (!result2)
1094                         {
1095                                 PQclear(result1);
1096                                 goto error_return;
1097                         }
1098                         else
1099                                 check_count = PQntuples(result2);
1100                 }
1101
1102                 /* count rules */
1103                 if (tableinfo.hasrules)
1104                 {
1105                         printfPQExpBuffer(&buf,
1106                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1107                                                           "FROM pg_catalog.pg_rewrite r\n"
1108                                                           "WHERE r.ev_class = '%s' ORDER BY 1",
1109                                                           oid);
1110                         result3 = PSQLexec(buf.data, false);
1111                         if (!result3)
1112                         {
1113                                 PQclear(result1);
1114                                 PQclear(result2);
1115                                 goto error_return;
1116                         }
1117                         else
1118                                 rule_count = PQntuples(result3);
1119                 }
1120
1121                 /* count triggers (but ignore foreign-key triggers) */
1122                 if (tableinfo.triggers)
1123                 {
1124                         printfPQExpBuffer(&buf,
1125                                          "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
1126                                                           "FROM pg_catalog.pg_trigger t\n"
1127                                                           "WHERE t.tgrelid = '%s' "
1128                                                           "AND (not tgisconstraint "
1129                                                           " OR NOT EXISTS"
1130                                                           "  (SELECT 1 FROM pg_catalog.pg_depend d "
1131                                                           "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
1132                                                           "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))"
1133                                                           "   ORDER BY 1",
1134                                                           oid);
1135                         result4 = PSQLexec(buf.data, false);
1136                         if (!result4)
1137                         {
1138                                 PQclear(result1);
1139                                 PQclear(result2);
1140                                 PQclear(result3);
1141                                 goto error_return;
1142                         }
1143                         else
1144                                 trigger_count = PQntuples(result4);
1145                 }
1146
1147                 /* count foreign-key constraints (there are none if no triggers) */
1148                 if (tableinfo.triggers)
1149                 {
1150                         printfPQExpBuffer(&buf,
1151                                                           "SELECT conname,\n"
1152                                    "  pg_catalog.pg_get_constraintdef(oid, true) as condef\n"
1153                                                           "FROM pg_catalog.pg_constraint r\n"
1154                                         "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1155                                                           oid);
1156                         result5 = PSQLexec(buf.data, false);
1157                         if (!result5)
1158                         {
1159                                 PQclear(result1);
1160                                 PQclear(result2);
1161                                 PQclear(result3);
1162                                 PQclear(result4);
1163                                 goto error_return;
1164                         }
1165                         else
1166                                 foreignkey_count = PQntuples(result5);
1167                 }
1168
1169                 /* count inherited tables */
1170                 printfPQExpBuffer(&buf, "SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno ASC", oid);
1171
1172                 result6 = PSQLexec(buf.data, false);
1173                 if (!result6)
1174                 {
1175                         PQclear(result1);
1176                         PQclear(result2);
1177                         PQclear(result3);
1178                         PQclear(result4);
1179                         PQclear(result5);
1180                         goto error_return;
1181                 }
1182                 else
1183                         inherits_count = PQntuples(result6);
1184
1185                 footers = pg_malloc_zero((index_count + check_count + rule_count + trigger_count + foreignkey_count + inherits_count + 7 + 1)
1186                                                                  * sizeof(*footers));
1187
1188                 /* print indexes */
1189                 if (index_count > 0)
1190                 {
1191                         printfPQExpBuffer(&buf, _("Indexes:"));
1192                         footers[count_footers++] = pg_strdup(buf.data);
1193                         for (i = 0; i < index_count; i++)
1194                         {
1195                                 const char *indexdef;
1196                                 const char *usingpos;
1197                                 PQExpBufferData tmpbuf;
1198
1199                                 /* Output index name */
1200                                 printfPQExpBuffer(&buf, _("    \"%s\""),
1201                                                                   PQgetvalue(result1, i, 0));
1202
1203                                 /* Label as primary key or unique (but not both) */
1204                                 appendPQExpBuffer(&buf,
1205                                                                   strcmp(PQgetvalue(result1, i, 1), "t") == 0
1206                                                                   ? " PRIMARY KEY," :
1207                                                                   (strcmp(PQgetvalue(result1, i, 2), "t") == 0
1208                                                                    ? " UNIQUE,"
1209                                                                    : ""));
1210                                 /* Everything after "USING" is echoed verbatim */
1211                                 indexdef = PQgetvalue(result1, i, 5);
1212                                 usingpos = strstr(indexdef, " USING ");
1213                                 if (usingpos)
1214                                         indexdef = usingpos + 7;
1215
1216                                 appendPQExpBuffer(&buf, " %s", indexdef);
1217
1218                                 if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
1219                                         appendPQExpBuffer(&buf, " CLUSTER");
1220
1221                                 if (strcmp(PQgetvalue(result1, i, 4), "t") != 0)
1222                                         appendPQExpBuffer(&buf, " INVALID");
1223
1224                                 /* Print tablespace of the index on the same line */
1225                                 count_footers += 1;
1226                                 initPQExpBuffer(&tmpbuf);
1227                                 if (add_tablespace_footer('i',
1228                                                                                   atooid(PQgetvalue(result1, i, 6)),
1229                                                                          footers, &count_footers, tmpbuf, false))
1230                                 {
1231                                         appendPQExpBuffer(&buf, ", ");
1232                                         appendPQExpBuffer(&buf, tmpbuf.data);
1233
1234                                         count_footers -= 2;
1235                                 }
1236                                 else
1237                                         count_footers -= 1;
1238                                 termPQExpBuffer(&tmpbuf);
1239
1240                                 footers[count_footers++] = pg_strdup(buf.data);
1241                         }
1242                 }
1243
1244                 /* print check constraints */
1245                 if (check_count > 0)
1246                 {
1247                         printfPQExpBuffer(&buf, _("Check constraints:"));
1248                         footers[count_footers++] = pg_strdup(buf.data);
1249                         for (i = 0; i < check_count; i++)
1250                         {
1251                                 printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1252                                                                   PQgetvalue(result2, i, 0),
1253                                                                   PQgetvalue(result2, i, 1));
1254
1255                                 footers[count_footers++] = pg_strdup(buf.data);
1256                         }
1257                 }
1258
1259                 /* print foreign key constraints */
1260                 if (foreignkey_count > 0)
1261                 {
1262                         printfPQExpBuffer(&buf, _("Foreign-key constraints:"));
1263                         footers[count_footers++] = pg_strdup(buf.data);
1264                         for (i = 0; i < foreignkey_count; i++)
1265                         {
1266                                 printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1267                                                                   PQgetvalue(result5, i, 0),
1268                                                                   PQgetvalue(result5, i, 1));
1269
1270                                 footers[count_footers++] = pg_strdup(buf.data);
1271                         }
1272                 }
1273
1274                 /* print rules */
1275                 if (rule_count > 0)
1276                 {
1277                         printfPQExpBuffer(&buf, _("Rules:"));
1278                         footers[count_footers++] = pg_strdup(buf.data);
1279                         for (i = 0; i < rule_count; i++)
1280                         {
1281                                 const char *ruledef;
1282
1283                                 /* Everything after "CREATE RULE" is echoed verbatim */
1284                                 ruledef = PQgetvalue(result3, i, 1);
1285                                 ruledef += 12;
1286
1287                                 printfPQExpBuffer(&buf, "    %s", ruledef);
1288
1289                                 footers[count_footers++] = pg_strdup(buf.data);
1290                         }
1291                 }
1292
1293                 /* print triggers */
1294                 if (trigger_count > 0)
1295                 {
1296                         printfPQExpBuffer(&buf, _("Triggers:"));
1297                         footers[count_footers++] = pg_strdup(buf.data);
1298                         for (i = 0; i < trigger_count; i++)
1299                         {
1300                                 const char *tgdef;
1301                                 const char *usingpos;
1302
1303                                 /* Everything after "TRIGGER" is echoed verbatim */
1304                                 tgdef = PQgetvalue(result4, i, 1);
1305                                 usingpos = strstr(tgdef, " TRIGGER ");
1306                                 if (usingpos)
1307                                         tgdef = usingpos + 9;
1308
1309                                 printfPQExpBuffer(&buf, "    %s", tgdef);
1310
1311                                 footers[count_footers++] = pg_strdup(buf.data);
1312                         }
1313                 }
1314
1315                 /* print inherits */
1316                 for (i = 0; i < inherits_count; i++)
1317                 {
1318                         char       *s = _("Inherits");
1319
1320                         if (i == 0)
1321                                 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result6, i, 0));
1322                         else
1323                                 printfPQExpBuffer(&buf, "%*s  %s", (int) strlen(s), "", PQgetvalue(result6, i, 0));
1324                         if (i < inherits_count - 1)
1325                                 appendPQExpBuffer(&buf, ",");
1326
1327                         footers[count_footers++] = pg_strdup(buf.data);
1328                 }
1329
1330                 if (verbose)
1331                 {
1332                         char       *s = _("Has OIDs");
1333
1334                         printfPQExpBuffer(&buf, "%s: %s", s,
1335                                                           (tableinfo.hasoids ? _("yes") : _("no")));
1336                         footers[count_footers++] = pg_strdup(buf.data);
1337                 }
1338
1339                 add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
1340                                                           footers, &count_footers, buf, true);
1341                 /* end of list marker */
1342                 footers[count_footers] = NULL;
1343
1344                 PQclear(result1);
1345                 PQclear(result2);
1346                 PQclear(result3);
1347                 PQclear(result4);
1348                 PQclear(result5);
1349                 PQclear(result6);
1350         }
1351
1352         printTable(title.data, headers,
1353                            (const char **) cells, (const char **) footers,
1354                            "llll", &myopt, pset.queryFout, pset.logfile);
1355
1356         retval = true;
1357
1358 error_return:
1359
1360         /* clean up */
1361         termPQExpBuffer(&buf);
1362         termPQExpBuffer(&title);
1363         termPQExpBuffer(&tmpbuf);
1364
1365         if (cells)
1366         {
1367                 for (i = 0; i < numrows; i++)
1368                 {
1369                         if (show_modifiers)
1370                                 free(cells[i * cols + 2]);
1371                 }
1372                 free(cells);
1373         }
1374
1375         if (footers)
1376         {
1377                 for (ptr = footers; *ptr; ptr++)
1378                         free(*ptr);
1379                 free(footers);
1380         }
1381
1382         if (view_def)
1383                 free(view_def);
1384
1385         if (res)
1386                 PQclear(res);
1387
1388         return retval;
1389 }
1390
1391
1392 /*
1393  * Return true if the relation uses non default tablespace;
1394  * otherwise return false
1395  */
1396 static bool
1397 add_tablespace_footer(char relkind, Oid tablespace, char **footers,
1398                                           int *count, PQExpBufferData buf, bool newline)
1399 {
1400         /* relkinds for which we support tablespaces */
1401         if (relkind == 'r' || relkind == 'i')
1402         {
1403                 /*
1404                  * We ignore the database default tablespace so that users not using
1405                  * tablespaces don't need to know about them.
1406                  */
1407                 if (tablespace != 0)
1408                 {
1409                         PGresult   *result1 = NULL;
1410
1411                         printfPQExpBuffer(&buf, "SELECT spcname FROM pg_tablespace \n"
1412                                                           "WHERE oid = '%u';", tablespace);
1413                         result1 = PSQLexec(buf.data, false);
1414                         if (!result1)
1415                                 return false;
1416                         /* Should always be the case, but.... */
1417                         if (PQntuples(result1) > 0)
1418                         {
1419                                 printfPQExpBuffer(&buf,
1420                                   newline ? _("Tablespace: \"%s\"") : _("tablespace \"%s\""),
1421                                                                   PQgetvalue(result1, 0, 0));
1422
1423                                 footers[(*count)++] = pg_strdup(buf.data);
1424                         }
1425                         PQclear(result1);
1426
1427                         return true;
1428                 }
1429         }
1430
1431         return false;
1432 }
1433
1434 /*
1435  * \du or \dg
1436  *
1437  * Describes roles.  Any schema portion of the pattern is ignored.
1438  */
1439 bool
1440 describeRoles(const char *pattern, bool verbose)
1441 {
1442         PQExpBufferData buf;
1443         PGresult   *res;
1444         printQueryOpt myopt = pset.popt;
1445
1446         initPQExpBuffer(&buf);
1447
1448         printfPQExpBuffer(&buf,
1449                                           "SELECT r.rolname AS \"%s\",\n"
1450                                 "  CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END AS \"%s\",\n"
1451                    "  CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS \"%s\",\n"
1452                          "  CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS \"%s\",\n"
1453                 "  CASE WHEN r.rolconnlimit < 0 THEN CAST('%s' AS pg_catalog.text)\n"
1454                                           "       ELSE CAST(r.rolconnlimit AS pg_catalog.text)\n"
1455                                           "  END AS \"%s\", \n"
1456                                           "  ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as \"%s\"",
1457                                           _("Role name"),
1458                                           _("yes"), _("no"), _("Superuser"),
1459                                           _("yes"), _("no"), _("Create role"),
1460                                           _("yes"), _("no"), _("Create DB"),
1461                                           _("no limit"), _("Connections"),
1462                                           _("Member of"));
1463
1464         if (verbose)
1465                 appendPQExpBuffer(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS \"%s\"",
1466                                                   _("Description"));
1467
1468         appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_roles r\n");
1469
1470         processSQLNamePattern(pset.db, &buf, pattern, false, false,
1471                                                   NULL, "r.rolname", NULL, NULL);
1472
1473         appendPQExpBuffer(&buf, "ORDER BY 1;");
1474
1475         res = PSQLexec(buf.data, false);
1476         termPQExpBuffer(&buf);
1477         if (!res)
1478                 return false;
1479
1480         myopt.nullPrint = NULL;
1481         myopt.title = _("List of roles");
1482
1483         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1484
1485         PQclear(res);
1486         return true;
1487 }
1488
1489
1490 /*
1491  * listTables()
1492  *
1493  * handler for \d, \dt, etc.
1494  *
1495  * tabtypes is an array of characters, specifying what info is desired:
1496  * t - tables
1497  * i - indexes
1498  * v - views
1499  * s - sequences
1500  * S - system tables (pg_catalog)
1501  * (any order of the above is fine)
1502  */
1503 bool
1504 listTables(const char *tabtypes, const char *pattern, bool verbose)
1505 {
1506         bool            showTables = strchr(tabtypes, 't') != NULL;
1507         bool            showIndexes = strchr(tabtypes, 'i') != NULL;
1508         bool            showViews = strchr(tabtypes, 'v') != NULL;
1509         bool            showSeq = strchr(tabtypes, 's') != NULL;
1510         bool            showSystem = strchr(tabtypes, 'S') != NULL;
1511
1512         PQExpBufferData buf;
1513         PGresult   *res;
1514         printQueryOpt myopt = pset.popt;
1515
1516         if (!(showTables || showIndexes || showViews || showSeq))
1517                 showTables = showViews = showSeq = true;
1518
1519         initPQExpBuffer(&buf);
1520
1521         /*
1522          * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
1523          * for backwards compatibility.
1524          */
1525         printfPQExpBuffer(&buf,
1526                                           "SELECT n.nspname as \"%s\",\n"
1527                                           "  c.relname as \"%s\",\n"
1528                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' END as \"%s\",\n"
1529                                           "  r.rolname as \"%s\"",
1530                                           _("Schema"), _("Name"),
1531                                           _("table"), _("view"), _("index"), _("sequence"),
1532                                           _("special"), _("Type"), _("Owner"));
1533
1534         if (showIndexes)
1535                 appendPQExpBuffer(&buf,
1536                                                   ",\n c2.relname as \"%s\"",
1537                                                   _("Table"));
1538
1539         if (verbose)
1540                 appendPQExpBuffer(&buf,
1541                           ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
1542                                                   _("Description"));
1543
1544         appendPQExpBuffer(&buf,
1545                                           "\nFROM pg_catalog.pg_class c"
1546                                         "\n     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
1547          "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
1548         if (showIndexes)
1549                 appendPQExpBuffer(&buf,
1550                          "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
1551                    "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
1552
1553         appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
1554         if (showTables)
1555                 appendPQExpBuffer(&buf, "'r',");
1556         if (showViews)
1557                 appendPQExpBuffer(&buf, "'v',");
1558         if (showIndexes)
1559                 appendPQExpBuffer(&buf, "'i',");
1560         if (showSeq)
1561                 appendPQExpBuffer(&buf, "'S',");
1562         if (showSystem && showTables)
1563                 appendPQExpBuffer(&buf, "'s',");
1564         appendPQExpBuffer(&buf, "''");          /* dummy */
1565         appendPQExpBuffer(&buf, ")\n");
1566
1567         /*
1568          * If showSystem is specified, show only system objects (those in
1569          * pg_catalog).  Otherwise, suppress system objects, including those in
1570          * pg_catalog and pg_toast.  (We don't want to hide temp tables though.)
1571          */
1572         if (showSystem)
1573                 appendPQExpBuffer(&buf, "      AND n.nspname = 'pg_catalog'\n");
1574         else
1575                 appendPQExpBuffer(&buf, "      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n");
1576
1577         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1578                                                   "n.nspname", "c.relname", NULL,
1579                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1580
1581         appendPQExpBuffer(&buf, "ORDER BY 1,2;");
1582
1583         res = PSQLexec(buf.data, false);
1584         termPQExpBuffer(&buf);
1585         if (!res)
1586                 return false;
1587
1588         if (PQntuples(res) == 0 && !pset.quiet)
1589         {
1590                 if (pattern)
1591                         fprintf(pset.queryFout, _("No matching relations found.\n"));
1592                 else
1593                         fprintf(pset.queryFout, _("No relations found.\n"));
1594         }
1595         else
1596         {
1597                 myopt.nullPrint = NULL;
1598                 myopt.title = _("List of relations");
1599
1600                 printQuery(res, &myopt, pset.queryFout, pset.logfile);
1601         }
1602
1603         PQclear(res);
1604         return true;
1605 }
1606
1607
1608 /*
1609  * \dD
1610  *
1611  * Describes domains.
1612  */
1613 bool
1614 listDomains(const char *pattern)
1615 {
1616         PQExpBufferData buf;
1617         PGresult   *res;
1618         printQueryOpt myopt = pset.popt;
1619
1620         initPQExpBuffer(&buf);
1621
1622         printfPQExpBuffer(&buf,
1623                                           "SELECT n.nspname as \"%s\",\n"
1624                                           "       t.typname as \"%s\",\n"
1625          "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
1626                                           "       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
1627         "            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
1628                                           "            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
1629                                           "            ELSE ''\n"
1630                                           "       END as \"%s\",\n"
1631                         "       pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
1632                                           "FROM pg_catalog.pg_type t\n"
1633            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
1634                   "     LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
1635                                           "WHERE t.typtype = 'd'\n",
1636                                           _("Schema"),
1637                                           _("Name"),
1638                                           _("Type"),
1639                                           _("Modifier"),
1640                                           _("Check"));
1641
1642         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1643                                                   "n.nspname", "t.typname", NULL,
1644                                                   "pg_catalog.pg_type_is_visible(t.oid)");
1645
1646         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1647
1648         res = PSQLexec(buf.data, false);
1649         termPQExpBuffer(&buf);
1650         if (!res)
1651                 return false;
1652
1653         myopt.nullPrint = NULL;
1654         myopt.title = _("List of domains");
1655
1656         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1657
1658         PQclear(res);
1659         return true;
1660 }
1661
1662 /*
1663  * \dc
1664  *
1665  * Describes conversions.
1666  */
1667 bool
1668 listConversions(const char *pattern)
1669 {
1670         PQExpBufferData buf;
1671         PGresult   *res;
1672         printQueryOpt myopt = pset.popt;
1673
1674         initPQExpBuffer(&buf);
1675
1676         printfPQExpBuffer(&buf,
1677                                           "SELECT n.nspname AS \"%s\",\n"
1678                                           "       c.conname AS \"%s\",\n"
1679            "       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
1680                 "       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
1681                                           "       CASE WHEN c.condefault THEN '%s'\n"
1682                                           "       ELSE '%s' END AS \"%s\"\n"
1683                            "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
1684                                           "WHERE n.oid = c.connamespace\n",
1685                                           _("Schema"),
1686                                           _("Name"),
1687                                           _("Source"),
1688                                           _("Destination"),
1689                                           _("yes"),
1690                                           _("no"),
1691                                           _("Default?"));
1692
1693         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1694                                                   "n.nspname", "c.conname", NULL,
1695                                                   "pg_catalog.pg_conversion_is_visible(c.oid)");
1696
1697         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1698
1699         res = PSQLexec(buf.data, false);
1700         termPQExpBuffer(&buf);
1701         if (!res)
1702                 return false;
1703
1704         myopt.nullPrint = NULL;
1705         myopt.title = _("List of conversions");
1706
1707         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1708
1709         PQclear(res);
1710         return true;
1711 }
1712
1713 /*
1714  * \dC
1715  *
1716  * Describes casts.
1717  */
1718 bool
1719 listCasts(const char *pattern)
1720 {
1721         PQExpBufferData buf;
1722         PGresult   *res;
1723         printQueryOpt myopt = pset.popt;
1724
1725         initPQExpBuffer(&buf);
1726 /* NEED LEFT JOIN FOR BINARY CASTS */
1727         printfPQExpBuffer(&buf,
1728                            "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
1729                            "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
1730                                           "       CASE WHEN castfunc = 0 THEN '%s'\n"
1731                                           "            ELSE p.proname\n"
1732                                           "       END as \"%s\",\n"
1733                                           "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
1734                                           "            WHEN c.castcontext = 'a' THEN '%s'\n"
1735                                           "            ELSE '%s'\n"
1736                                           "       END as \"%s\"\n"
1737                                  "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
1738                                           "     ON c.castfunc = p.oid\n"
1739                                           "ORDER BY 1, 2",
1740                                           _("Source type"),
1741                                           _("Target type"),
1742                                           _("(binary compatible)"),
1743                                           _("Function"),
1744                                           _("no"),
1745                                           _("in assignment"),
1746                                           _("yes"),
1747                                           _("Implicit?"));
1748
1749         res = PSQLexec(buf.data, false);
1750         termPQExpBuffer(&buf);
1751         if (!res)
1752                 return false;
1753
1754         myopt.nullPrint = NULL;
1755         myopt.title = _("List of casts");
1756
1757         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1758
1759         PQclear(res);
1760         return true;
1761 }
1762
1763 /*
1764  * \dn
1765  *
1766  * Describes schemas (namespaces)
1767  */
1768 bool
1769 listSchemas(const char *pattern, bool verbose)
1770 {
1771         PQExpBufferData buf;
1772         PGresult   *res;
1773         printQueryOpt myopt = pset.popt;
1774
1775         initPQExpBuffer(&buf);
1776         printfPQExpBuffer(&buf,
1777                                           "SELECT n.nspname AS \"%s\",\n"
1778                                           "       r.rolname AS \"%s\"",
1779                                           _("Name"), _("Owner"));
1780
1781         if (verbose)
1782                 appendPQExpBuffer(&buf,
1783                                                   ",\n  n.nspacl as \"%s\","
1784                          "  pg_catalog.obj_description(n.oid, 'pg_namespace') as \"%s\"",
1785                                                   _("Access privileges"), _("Description"));
1786
1787         appendPQExpBuffer(&buf,
1788                           "\nFROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_roles r\n"
1789                                           "       ON n.nspowner=r.oid\n"
1790                                           "WHERE        (n.nspname !~ '^pg_temp_' OR\n"
1791                    "             n.nspname = (pg_catalog.current_schemas(true))[1])\n");                /* temp schema is first */
1792
1793         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1794                                                   NULL, "n.nspname", NULL,
1795                                                   NULL);
1796
1797         appendPQExpBuffer(&buf, "ORDER BY 1;");
1798
1799         res = PSQLexec(buf.data, false);
1800         termPQExpBuffer(&buf);
1801         if (!res)
1802                 return false;
1803
1804         myopt.nullPrint = NULL;
1805         myopt.title = _("List of schemas");
1806
1807         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1808
1809         PQclear(res);
1810         return true;
1811 }