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

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