Attached is an updated version of my psql patch that makes the \d
backslash commands perform in an intuitive, consistent way.
Specifically, the following objects will be treated as first class
citizens (as tables and indexes currently are) by showing all the
non-system objects by default and requiring a "S" to see the system
ones.
aggregates
conversions
comments
domains
operators
functions
types
Currently, there is no way to view all the non-system functions in a
database using backslash commands, as you can with \dt, unless all of
the functions happen to be in a single schema ("\df myschema."). With
this patch, it would be as simple as "\df", and the current behavior
would be done with "\dfS".
This patch also adds a few new things to the tab-completion table, such
as comments and conversions.
Comments welcome. Last time the patch withered on the vine, despite
strong support from -general, hence I'm going to try again, as I really
want a way to view my functions without querying the pg_proc tables
directly. :)
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200711042003
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.196
diff -c -r1.196 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 29 Oct 2007 17:29:59 -0000 1.196
--- doc/src/sgml/ref/psql-ref.sgml 5 Nov 2007 00:44:49 -0000
***************
*** 874,885 ****
<varlistentry>
<term><literal>\da [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
! Lists all available aggregate functions, together with their
! return type and the data types they operate on. If <replaceable
! class="parameter">pattern</replaceable>
is specified, only aggregates whose names match the pattern are shown.
</para>
</listitem>
--- 874,887 ----
<varlistentry>
<term><literal>\da [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\daS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
! Lists all available aggregate functions, together with the data
! types they operate on. The letter <literal>S</literal> restricts
! the listing to system aggregates; without <literal>S</literal>,
! only non-system aggregates are shown. If <replaceable class="parameter">pattern</replaceable>
is specified, only aggregates whose names match the pattern are shown.
</para>
</listitem>
***************
*** 904,912 ****
--- 906,917 ----
<varlistentry>
<term><literal>\dc [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\dcS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Lists all available conversions between character-set encodings.
+ The letter <literal>S</literal> restricts the listing to system conversions;
+ without <literal>S</literal>, only non-system conversions are shown.
If <replaceable class="parameter">pattern</replaceable>
is specified, only conversions whose names match the pattern are
listed.
***************
*** 927,943 ****
<varlistentry>
<term><literal>\dd [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Shows the descriptions of objects matching the <replaceable
class="parameter">pattern</replaceable>, or of all visible objects if
no argument is given. But in either case, only objects that have
! a description are listed.
(<quote>Object</quote> covers aggregates, functions, operators,
types, relations (tables, views, indexes, sequences, large
objects), rules, and triggers.) For example:
<programlisting>
! => <userinput>\dd version</userinput>
Object descriptions
Schema | Name | Object | Description
------------+---------+----------+---------------------------
--- 932,951 ----
<varlistentry>
<term><literal>\dd [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\ddS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Shows the descriptions of objects matching the <replaceable
class="parameter">pattern</replaceable>, or of all visible objects if
no argument is given. But in either case, only objects that have
! a description are listed. The letter <literal>S</literal> restricts
! the listing to system objects; without <literal>S</literal>,
! only non-system objects are shown.
(<quote>Object</quote> covers aggregates, functions, operators,
types, relations (tables, views, indexes, sequences, large
objects), rules, and triggers.) For example:
<programlisting>
! => <userinput>\ddS version</userinput>
Object descriptions
Schema | Name | Object | Description
------------+---------+----------+---------------------------
***************
*** 957,966 ****
<varlistentry>
<term><literal>\dD [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
! Lists all available domains. If <replaceable
! class="parameter">pattern</replaceable>
is specified, only matching domains are shown.
</para>
</listitem>
--- 965,976 ----
<varlistentry>
<term><literal>\dD [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\dDS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
! Lists all available domains. The letter <literal>S</literal> restricts
! the listing to system domains; without <literal>S</literal>,
! only non-system domains are shown. If <replaceable class="parameter">pattern</replaceable>
is specified, only matching domains are shown.
</para>
</listitem>
***************
*** 970,984 ****
<varlistentry>
<term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<term><literal>\df+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Lists available functions, together with their argument and
! return types. If <replaceable
class="parameter">pattern</replaceable>
is specified, only functions whose names match the pattern are shown.
! If the form <literal>\df+</literal> is used, additional information about
! each function, including volatility, language, source code and description, is shown.
</para>
<note>
--- 980,997 ----
<varlistentry>
<term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<term><literal>\df+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\dfS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\dfS+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Lists available functions, together with their argument and
! return types. The letter <literal>S</literal> restricts the listing to system functions;
! without <literal>S</literal>, only non-system functions are shown. If <replaceable
class="parameter">pattern</replaceable>
is specified, only functions whose names match the pattern are shown.
! If the form <literal>\df+</literal> or <literal>\dfS+</literal> is used, additional
! information about each function, including language and description, is shown.
</para>
<note>
***************
*** 1128,1136 ****
--- 1141,1152 ----
<varlistentry>
<term><literal>\do [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <term><literal>\doS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
<listitem>
<para>
Lists available operators with their operand and return types.
+ The letter <literal>S</literal> restricts the listing to system operators; without
+ <literal>S</literal>, only non-system operators are shown.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
</para>
***************
*** 1163,1170 ****
<listitem>
<para>
Lists all data types or only those that match <replaceable
! class="parameter">pattern</replaceable>. The command form
! <literal>\dT+</literal> shows extra information.
</para>
</listitem>
</varlistentry>
--- 1179,1188 ----
<listitem>
<para>
Lists all data types or only those that match <replaceable
! class="parameter">pattern</replaceable>.
! The letter <literal>S</literal> restricts the listing to system types; without
! <literal>S</literal>, only non-system data types are shown.
! The command form <literal>\dT+</literal> or <literal>\dTS+</literal> shows extra information.
</para>
</listitem>
</varlistentry>
Index: src/bin/psql/command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.182
diff -c -r1.182 command.c
*** src/bin/psql/command.c 13 Oct 2007 20:18:41 -0000 1.182
--- src/bin/psql/command.c 5 Nov 2007 00:44:50 -0000
***************
*** 336,348 ****
else if (cmd[0] == 'd')
{
char *pattern;
! bool show_verbose;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
switch (cmd[1])
{
--- 336,349 ----
else if (cmd[0] == 'd')
{
char *pattern;
! bool show_verbose, show_system;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
+ show_system = strchr(cmd, 'S') ? true: false;
switch (cmd[1])
{
***************
*** 352,379 ****
success = describeTableDetails(pattern, show_verbose);
else
/* standard listing of interesting things */
! success = listTables("tvs", NULL, show_verbose);
break;
case 'a':
! success = describeAggregates(pattern, show_verbose);
break;
case 'b':
success = describeTablespaces(pattern, show_verbose);
break;
case 'c':
! success = listConversions(pattern);
break;
case 'C':
success = listCasts(pattern);
break;
case 'd':
! success = objectDescription(pattern);
break;
case 'D':
! success = listDomains(pattern);
break;
case 'f':
! success = describeFunctions(pattern, show_verbose);
break;
case 'g':
/* no longer distinct from \du */
--- 353,380 ----
success = describeTableDetails(pattern, show_verbose);
else
/* standard listing of interesting things */
! success = listTables("tvs", NULL, show_verbose, show_system);
break;
case 'a':
! success = describeAggregates(pattern, show_verbose, show_system);
break;
case 'b':
success = describeTablespaces(pattern, show_verbose);
break;
case 'c':
! success = listConversions(pattern, show_system);
break;
case 'C':
success = listCasts(pattern);
break;
case 'd':
! success = objectDescription(pattern, show_system);
break;
case 'D':
! success = listDomains(pattern, show_system);
break;
case 'f':
! success = describeFunctions(pattern, show_verbose, show_system);
break;
case 'g':
/* no longer distinct from \du */
***************
*** 386,405 ****
success = listSchemas(pattern, show_verbose);
break;
case 'o':
! success = describeOperators(pattern);
break;
case 'p':
success = permissionsList(pattern);
break;
case 'T':
! success = describeTypes(pattern, show_verbose);
break;
case 't':
case 'v':
case 'i':
case 's':
! case 'S':
! success = listTables(&cmd[1], pattern, show_verbose);
break;
case 'u':
success = describeRoles(pattern, show_verbose);
--- 387,406 ----
success = listSchemas(pattern, show_verbose);
break;
case 'o':
! success = describeOperators(pattern, show_system);
break;
case 'p':
success = permissionsList(pattern);
break;
case 'T':
! success = describeTypes(pattern, show_verbose, show_system);
break;
case 't':
case 'v':
case 'i':
case 's':
! case 'S': /* backwards compatible */
! success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
success = describeRoles(pattern, show_verbose);
Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.160
diff -c -r1.160 describe.c
*** src/bin/psql/describe.c 16 Oct 2007 18:02:26 -0000 1.160
--- src/bin/psql/describe.c 5 Nov 2007 00:44:50 -0000
***************
*** 55,61 ****
* Takes an optional regexp to select particular aggregates
*/
bool
! describeAggregates(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 55,61 ----
* Takes an optional regexp to select particular aggregates
*/
bool
! describeAggregates(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 83,93 ****
--- 83,99 ----
" END AS \"%s\",\n"
" pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
"FROM pg_catalog.pg_proc p\n"
+ <<<<<<< describe.c
+ "JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
+ =======
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
+ >>>>>>> 1.160
"WHERE p.proisagg\n",
_("Schema"), _("Name"), _("Result data type"),
_("Argument data types"), _("Description"));
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 167,173 ****
* Takes an optional regexp to select particular functions
*/
bool
! describeFunctions(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 173,179 ----
* Takes an optional regexp to select particular functions
*/
bool
! describeFunctions(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 228,240 ****
if (!verbose)
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_proc p"
! "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
else
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_proc p"
! "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
! "\n LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
! "\n JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
/*
* we skip in/out funcs by excluding functions that take or return cstring
--- 234,246 ----
if (!verbose)
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_proc p"
! "\nJOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
else
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_proc p"
! "\n JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
! "\n LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
! "\n JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
/*
* we skip in/out funcs by excluding functions that take or return cstring
***************
*** 245,250 ****
--- 251,258 ----
" OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
" AND NOT p.proisagg\n");
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 272,278 ****
* describe types
*/
bool
! describeTypes(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 280,286 ----
* describe types
*/
bool
! describeTypes(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 299,305 ****
_("Description"));
appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
/*
* do not include array types (start with underscore); do not include
--- 307,313 ----
_("Description"));
appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
/*
* do not include array types (start with underscore); do not include
***************
*** 310,315 ****
--- 318,325 ----
"WHERE c.oid = t.typrelid)) ");
appendPQExpBuffer(&buf, "AND t.typname !~ '^_'\n");
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname",
***************
*** 336,342 ****
/* \do
*/
bool
! describeOperators(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 346,352 ----
/* \do
*/
bool
! describeOperators(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 349,363 ****
" o.oprname AS \"%s\",\n"
" CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
" CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
! " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
! " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
! " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
"FROM pg_catalog.pg_operator o\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
_("Schema"), _("Name"),
_("Left arg type"), _("Right arg type"),
_("Result type"), _("Description"));
processSQLNamePattern(pset.db, &buf, pattern, false, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 359,375 ----
" o.oprname AS \"%s\",\n"
" CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
" CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
! " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
! " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
! " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
"FROM pg_catalog.pg_operator o\n"
! "JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
_("Schema"), _("Name"),
_("Left arg type"), _("Right arg type"),
_("Result type"), _("Description"));
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, false, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 453,459 ****
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
" c.relacl as \"%s\"\n"
"FROM pg_catalog.pg_class c\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
"WHERE c.relkind IN ('r', 'v', 'S')\n",
_("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
--- 465,471 ----
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
" c.relacl as \"%s\"\n"
"FROM pg_catalog.pg_class c\n"
! "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
"WHERE c.relkind IN ('r', 'v', 'S')\n",
_("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
***************
*** 498,504 ****
* lists of things, there are other \d? commands.
*/
bool
! objectDescription(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 510,516 ----
* lists of things, there are other \d? commands.
*/
bool
! objectDescription(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 518,526 ****
" CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_proc p\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
" WHERE p.proisagg\n",
_("aggregate"));
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
--- 530,541 ----
" CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_proc p\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
" WHERE p.proisagg\n",
_("aggregate"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 533,545 ****
" CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_proc p\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
!
! " WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
" AND (p.proargtypes[0] IS NULL\n"
" OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
" AND NOT p.proisagg\n",
_("function"));
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
--- 548,562 ----
" CAST(p.proname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_proc p\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
! " WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
" AND (p.proargtypes[0] IS NULL\n"
" OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
" AND NOT p.proisagg\n",
_("function"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 552,559 ****
" CAST(o.oprname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_operator o\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
_("operator"));
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 569,579 ----
" CAST(o.oprname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_operator o\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
_("operator"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 566,573 ****
" pg_catalog.format_type(t.oid, NULL) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_type t\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
_("data type"));
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
--- 586,596 ----
" pg_catalog.format_type(t.oid, NULL) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_type t\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
_("data type"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
***************
*** 583,591 ****
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
" AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_class c\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
_("table"), _("view"), _("index"), _("sequence"));
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 606,617 ----
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
" AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_class c\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
_("table"), _("view"), _("index"), _("sequence"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 598,607 ****
" CAST(r.rulename AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_rewrite r\n"
! " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" WHERE r.rulename != '_RETURN'\n",
_("rule"));
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "r.rulename", NULL,
--- 624,636 ----
" CAST(r.rulename AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_rewrite r\n"
! " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" WHERE r.rulename != '_RETURN'\n",
_("rule"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "r.rulename", NULL,
***************
*** 615,625 ****
" CAST(t.tgname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_trigger t\n"
! " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
_("trigger"));
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 644,657 ----
" CAST(t.tgname AS pg_catalog.text) as name,"
" CAST('%s' AS pg_catalog.text) as object\n"
" FROM pg_catalog.pg_trigger t\n"
! " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
_("trigger"));
+
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 667,673 ****
" n.nspname,\n"
" c.relname\n"
"FROM pg_catalog.pg_class c\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "c.relname", NULL,
--- 699,705 ----
" n.nspname,\n"
" c.relname\n"
"FROM pg_catalog.pg_class c\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "c.relname", NULL,
***************
*** 1626,1638 ****
* (any order of the above is fine)
*/
bool
! listTables(const char *tabtypes, const char *pattern, bool verbose)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
bool showViews = strchr(tabtypes, 'v') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
- bool showSystem = strchr(tabtypes, 'S') != NULL;
PQExpBufferData buf;
PGresult *res;
--- 1658,1669 ----
* (any order of the above is fine)
*/
bool
! listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
bool showViews = strchr(tabtypes, 'v') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
PQExpBufferData buf;
PGresult *res;
***************
*** 1669,1675 ****
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c"
"\n JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
! "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
if (showIndexes)
appendPQExpBuffer(&buf,
"\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
--- 1700,1706 ----
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c"
"\n JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
! "\n JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
if (showIndexes)
appendPQExpBuffer(&buf,
"\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
***************
*** 1739,1745 ****
* Describes domains.
*/
bool
! listDomains(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 1770,1776 ----
* Describes domains.
*/
bool
! listDomains(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 1758,1764 ****
" END as \"%s\",\n"
" pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
"FROM pg_catalog.pg_type t\n"
! " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
" LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
"WHERE t.typtype = 'd'\n",
_("Schema"),
--- 1789,1795 ----
" END as \"%s\",\n"
" pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
"FROM pg_catalog.pg_type t\n"
! " JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
" LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
"WHERE t.typtype = 'd'\n",
_("Schema"),
***************
*** 1767,1772 ****
--- 1798,1805 ----
_("Modifier"),
_("Check"));
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname", NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
***************
*** 1793,1799 ****
* Describes conversions.
*/
bool
! listConversions(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 1826,1832 ----
* Describes conversions.
*/
bool
! listConversions(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 1818,1823 ****
--- 1851,1858 ----
_("no"),
_("Default?"));
+ appendPQExpBuffer(&buf, " AND n.nspname %s 'pg_catalog'\n", showSystem ? "=" : "<>");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.conname", NULL,
"pg_catalog.pg_conversion_is_visible(c.oid)");
Index: src/bin/psql/describe.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.34
diff -c -r1.34 describe.h
*** src/bin/psql/describe.h 21 Aug 2007 01:11:22 -0000 1.34
--- src/bin/psql/describe.h 5 Nov 2007 00:44:50 -0000
***************
*** 10,28 ****
/* \da */
! extern bool describeAggregates(const char *pattern, bool verbose);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df */
! extern bool describeFunctions(const char *pattern, bool verbose);
/* \dT */
! extern bool describeTypes(const char *pattern, bool verbose);
/* \do */
! extern bool describeOperators(const char *pattern);
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
--- 10,28 ----
/* \da */
! extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df */
! extern bool describeFunctions(const char *pattern, bool verbose, bool showSystem);
/* \dT */
! extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
/* \do */
! extern bool describeOperators(const char *pattern, bool showSystem);
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
***************
*** 31,37 ****
extern bool permissionsList(const char *pattern);
/* \dd */
! extern bool objectDescription(const char *pattern);
/* \d foo */
extern bool describeTableDetails(const char *pattern, bool verbose);
--- 31,37 ----
extern bool permissionsList(const char *pattern);
/* \dd */
! extern bool objectDescription(const char *pattern, bool showSystem);
/* \d foo */
extern bool describeTableDetails(const char *pattern, bool verbose);
***************
*** 52,64 ****
extern bool listAllDbs(bool verbose);
/* \dt, \di, \ds, \dS, etc. */
! extern bool listTables(const char *tabtypes, const char *pattern, bool verbose);
/* \dD */
! extern bool listDomains(const char *pattern);
/* \dc */
! extern bool listConversions(const char *pattern);
/* \dC */
extern bool listCasts(const char *pattern);
--- 52,64 ----
extern bool listAllDbs(bool verbose);
/* \dt, \di, \ds, \dS, etc. */
! extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
/* \dD */
! extern bool listDomains(const char *pattern, bool showSystem);
/* \dc */
! extern bool listConversions(const char *pattern, bool showSystem);
/* \dC */
extern bool listCasts(const char *pattern);
Index: src/bin/psql/help.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.118
diff -c -r1.118 help.c
*** src/bin/psql/help.c 21 Aug 2007 01:11:22 -0000 1.118
--- src/bin/psql/help.c 5 Nov 2007 00:44:50 -0000
***************
*** 209,214 ****
--- 209,239 ----
fprintf(output, "\n");
fprintf(output, _("Informational\n"));
+ <<<<<<< help.c
+ fprintf(output, _(" Modifiers: S = show system objects + = Additional detail\n"));
+ fprintf(output, _(" \\d[S] list tables, indexes, sequences, and views\n"));
+ fprintf(output, _(" \\d[S] NAME describe table, index, sequence, or view\n"));
+ fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
+ fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
+ fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\df[S+] [PATTERN] list functions\n"));
+ fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
+ fprintf(output, _(" \\dd[S] [PATTERN] show comments for object\n"));
+ fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
+ fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
+ fprintf(output, _(" \\da[S] [PATTERN] list aggregate functions\n"));
+ fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
+ fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
+ fprintf(output, _(" \\dn[+] [PATTERN] list schemas\n"));
+ fprintf(output, _(" \\dC list casts\n"));
+ fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
+ fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
+ fprintf(output, _(" \\dT[+] [PATTERN] list data types\n"));
+ fprintf(output, _(" \\du [PATTERN] list users\n"));
+ fprintf(output, _(" \\l[+] list all databases\n"));
+ fprintf(output, _(" \\z [PATTERN] list table, view, and sequence access privileges (same as \\dp)\n"));
+ =======
fprintf(output, _(" \\d [NAME] describe table, index, sequence, or view\n"));
fprintf(output, _(" \\d{t|i|s|v|S} [PATTERN] (add \"+\" for more detail)\n"
" list tables/indexes/sequences/views/system tables\n"));
***************
*** 232,237 ****
--- 257,263 ----
fprintf(output, _(" \\du [PATTERN] list users\n"));
fprintf(output, _(" \\l list all databases (add \"+\" for more detail)\n"));
fprintf(output, _(" \\z [PATTERN] list table, view, and sequence access privileges (same as \\dp)\n"));
+ >>>>>>> 1.118
fprintf(output, "\n");
fprintf(output, _("Formatting\n"));
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.167
diff -c -r1.167 tab-complete.c
*** src/bin/psql/tab-complete.c 14 Sep 2007 04:25:24 -0000 1.167
--- src/bin/psql/tab-complete.c 5 Nov 2007 00:44:50 -0000
***************
*** 156,162 ****
/* catname */
"pg_catalog.pg_proc p",
/* selcondition */
! "p.proisagg",
/* viscondition */
"pg_catalog.pg_function_is_visible(p.oid)",
/* namespace */
--- 156,162 ----
/* catname */
"pg_catalog.pg_proc p",
/* selcondition */
! "p.proisagg AND p.pronamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
/* viscondition */
"pg_catalog.pg_function_is_visible(p.oid)",
/* namespace */
***************
*** 167,179 ****
NULL
};
static const SchemaQuery Query_for_list_of_datatypes = {
/* catname */
"pg_catalog.pg_type t",
/* selcondition --- ignore table rowtypes and array types */
"(t.typrelid = 0 "
" OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "
! "AND t.typname !~ '^_'",
/* viscondition */
"pg_catalog.pg_type_is_visible(t.oid)",
/* namespace */
--- 167,232 ----
NULL
};
+ static const SchemaQuery Query_for_list_of_comments = {
+ /* catname */
+ "(SELECT c.name, c.namesp FROM ( SELECT * FROM ("
+ " SELECT oid, relname as name, relnamespace as namesp FROM pg_catalog.pg_class c"
+ " WHERE pg_catalog.pg_table_is_visible(oid)"
+ " UNION ALL"
+ " SELECT oid, proname::pg_catalog.text as name, pronamespace AS namesp FROM pg_catalog.pg_proc"
+ " WHERE pg_catalog.pg_function_is_visible(oid) AND (proisagg OR proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)"
+ " UNION ALL"
+ " SELECT oid, pg_catalog.format_type(oid, NULL) as name, typnamespace AS namesp FROM pg_catalog.pg_type"
+ " WHERE pg_catalog.pg_type_is_visible(oid)"
+ " UNION ALL"
+ " SELECT oid, oprname::pg_catalog.text AS name, oprnamespace AS namesp FROM pg_catalog.pg_operator"
+ " WHERE pg_catalog.pg_operator_is_visible(oid)"
+ " UNION ALL"
+ " SELECT r.oid, r.rulename::pg_catalog.text AS name, c.relnamespace AS namesp FROM pg_catalog.pg_rewrite r"
+ " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class"
+ " WHERE r.rulename != '_RETURN' AND pg_catalog.pg_table_is_visible(c.oid)"
+ " UNION ALL"
+ " SELECT t.oid, t.tgname::pg_catalog.text AS name, c.relnamespace AS namesp FROM pg_catalog.pg_trigger t"
+ " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid"
+ " WHERE pg_catalog.pg_table_is_visible(c.oid)"
+ ") b WHERE namesp <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"
+ ") c JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)) AS x",
+ /* selcondition */
+ NULL, // "x.namesp <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
+ /* viscondition */
+ "1=1", /* Always true, since we have to call *_is_visible functions separately above */
+ /* namespace */
+ "x.namesp",
+ /* result */
+ "pg_catalog.quote_ident(x.name)",
+ /* qualresult */
+ NULL
+ };
+
+
+ static const SchemaQuery Query_for_list_of_conversions = {
+ /* catname */
+ "pg_catalog.pg_conversion c",
+ /* selcondition */
+ "c.connamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
+ /* viscondition */
+ "pg_catalog.pg_conversion_is_visible(c.oid)",
+ /* namespace */
+ "c.connamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.conname)",
+ /* qualresult */
+ NULL
+ };
+
static const SchemaQuery Query_for_list_of_datatypes = {
/* catname */
"pg_catalog.pg_type t",
/* selcondition --- ignore table rowtypes and array types */
"(t.typrelid = 0 "
" OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "
! "AND t.typname !~ '^_'"
! "AND t.typnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
/* viscondition */
"pg_catalog.pg_type_is_visible(t.oid)",
/* namespace */
***************
*** 188,194 ****
/* catname */
"pg_catalog.pg_type t",
/* selcondition */
! "t.typtype = 'd'",
/* viscondition */
"pg_catalog.pg_type_is_visible(t.oid)",
/* namespace */
--- 241,247 ----
/* catname */
"pg_catalog.pg_type t",
/* selcondition */
! "t.typtype = 'd' AND t.typnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
/* viscondition */
"pg_catalog.pg_type_is_visible(t.oid)",
/* namespace */
***************
*** 203,209 ****
/* catname */
"pg_catalog.pg_proc p",
/* selcondition */
! NULL,
/* viscondition */
"pg_catalog.pg_function_is_visible(p.oid)",
/* namespace */
--- 256,262 ----
/* catname */
"pg_catalog.pg_proc p",
/* selcondition */
! "p.pronamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
/* viscondition */
"pg_catalog.pg_function_is_visible(p.oid)",
/* namespace */
***************
*** 229,234 ****
--- 282,302 ----
NULL
};
+ static const SchemaQuery Query_for_list_of_operators = {
+ /* catname */
+ "pg_catalog.pg_operator o",
+ /* selcondition */
+ "p.oprnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')",
+ /* viscondition */
+ "1=1",
+ /* namespace */
+ "o.oprnamespace",
+ /* result */
+ "pg_catalog.quote_ident(o.oprname)",
+ /* qualresult */
+ NULL
+ };
+
static const SchemaQuery Query_for_list_of_sequences = {
/* catname */
"pg_catalog.pg_class c",
***************
*** 328,360 ****
" AND pg_catalog.quote_ident(relname)='%s' "\
" AND pg_catalog.pg_table_is_visible(c.oid)"
#define Query_for_list_of_template_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s' and datistemplate IS TRUE"
#define Query_for_list_of_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s'"
! #define Query_for_list_of_tablespaces \
! "SELECT pg_catalog.quote_ident(spcname) FROM pg_catalog.pg_tablespace "\
! " WHERE substring(pg_catalog.quote_ident(spcname),1,%d)='%s'"
#define Query_for_list_of_encodings \
" SELECT DISTINCT pg_catalog.pg_encoding_to_char(conforencoding) "\
" FROM pg_catalog.pg_conversion "\
" WHERE substring(pg_catalog.pg_encoding_to_char(conforencoding),1,%d)=UPPER('%s')"
#define Query_for_list_of_languages \
"SELECT pg_catalog.quote_ident(lanname) "\
" FROM pg_language "\
" WHERE lanname != 'internal' "\
" AND substring(pg_catalog.quote_ident(lanname),1,%d)='%s' "
#define Query_for_list_of_schemas \
"SELECT pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace "\
" WHERE substring(pg_catalog.quote_ident(nspname),1,%d)='%s'"
#define Query_for_list_of_set_vars \
"SELECT name FROM "\
" (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings "\
--- 396,492 ----
" AND pg_catalog.quote_ident(relname)='%s' "\
" AND pg_catalog.pg_table_is_visible(c.oid)"
+ <<<<<<< tab-complete.c
+ #define Query_for_list_of_system_aggregates \
+ "SELECT pg_catalog.quote_ident(proname) FROM pg_catalog.pg_proc "\
+ " WHERE proisagg AND pg_catalog.pg_function_is_visible(oid)"\
+ " AND p.pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
+ " AND substring(pg_catalog.quote_ident(proname),1,%d)='%s'"
+
+ #define Query_for_list_of_system_comments \
+ "SELECT pg_catalog.quote_ident(name) FROM "\
+ "(SELECT c.name, c.namesp FROM ( SELECT * FROM ("\
+ " SELECT oid, relname as name, relnamespace as namesp FROM pg_catalog.pg_class c"\
+ " WHERE pg_catalog.pg_table_is_visible(oid)"\
+ " UNION ALL"\
+ " SELECT oid, proname::pg_catalog.text as name, pronamespace AS namesp FROM pg_catalog.pg_proc"\
+ " WHERE pg_catalog.pg_function_is_visible(oid) AND (proisagg OR proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)"\
+ " UNION ALL"\
+ " SELECT oid, pg_catalog.format_type(oid, NULL) as name, typnamespace AS namesp FROM pg_catalog.pg_type"\
+ " WHERE pg_catalog.pg_type_is_visible(oid)"\
+ " UNION ALL"\
+ " SELECT oid, oprname::pg_catalog.text AS name, oprnamespace AS namesp FROM pg_catalog.pg_operator"\
+ " WHERE pg_catalog.pg_operator_is_visible(oid)"\
+ " UNION ALL"\
+ " SELECT r.oid, r.rulename::pg_catalog.text AS name, c.relnamespace AS namesp FROM pg_catalog.pg_rewrite r"\
+ " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class"\
+ " WHERE r.rulename != '_RETURN' AND pg_catalog.pg_table_is_visible(c.oid)"\
+ " UNION ALL"\
+ " SELECT t.oid, t.tgname::pg_catalog.text AS name, c.relnamespace AS namesp FROM pg_catalog.pg_trigger t"\
+ " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid"\
+ " WHERE pg_catalog.pg_table_is_visible(c.oid)"\
+ ") b WHERE namesp = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
+ ") c JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)) AS x",\
+ " WHERE substring(pg_catalog.quote_ident(cname),1,%d)='%s'"
+
+ #define Query_for_list_of_system_conversions \
+ "SELECT pg_catalog.quote_ident(conname) FROM pg_catalog.pg_conversion"\
+ " WHERE pg_catalog.pg_conversion_is_visible(oid)"\
+ " AND connamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
+ " AND substring(pg_catalog.quote_ident(conname),1,%d)='%s'"
+
+ =======
#define Query_for_list_of_template_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s' and datistemplate IS TRUE"
+ >>>>>>> 1.167
#define Query_for_list_of_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s'"
! #define Query_for_list_of_system_datatypes \
! "SELECT pg_catalog.quote_ident(t.typname) FROM pg_catalog.pg_type t"\
! " WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "\
! " AND t.typname !~ '^_'"\
! " AND t.typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
! " AND substring(pg_catalog.quote_ident(typname),1,%d)='%s'"
!
! #define Query_for_list_of_system_domains \
! "SELECT pg_catalog.quote_ident(typname) FROM pg_catalog.pg_type"\
! " WHERE pg_catalog.pg_domain_is_visible(oid)"\
! " AND typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
! " AND substring(pg_catalog.quote_ident(typname),1,%d)='%s'"
#define Query_for_list_of_encodings \
" SELECT DISTINCT pg_catalog.pg_encoding_to_char(conforencoding) "\
" FROM pg_catalog.pg_conversion "\
" WHERE substring(pg_catalog.pg_encoding_to_char(conforencoding),1,%d)=UPPER('%s')"
+ #define Query_for_list_of_system_functions \
+ "SELECT pg_catalog.quote_ident(proname) FROM pg_catalog.pg_proc"\
+ " WHERE pg_catalog.pg_function_is_visible(oid)"\
+ " AND pronamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"\
+ " AND substring(pg_catalog.quote_ident(proname),1,%d)='%s'"
+
#define Query_for_list_of_languages \
"SELECT pg_catalog.quote_ident(lanname) "\
" FROM pg_language "\
" WHERE lanname != 'internal' "\
" AND substring(pg_catalog.quote_ident(lanname),1,%d)='%s' "
+ #define Query_for_list_of_largeobjects \
+ "SELECT pg_catalog.obj_description(loid, 'pg_largeobject')"\
+ " FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject)"
+
#define Query_for_list_of_schemas \
"SELECT pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace "\
" WHERE substring(pg_catalog.quote_ident(nspname),1,%d)='%s'"
+ #define Query_for_list_of_tablespaces \
+ "SELECT pg_catalog.quote_ident(spcname) FROM pg_catalog.pg_tablespace "\
+ " WHERE substring(pg_catalog.quote_ident(spcname),1,%d)='%s'"
+
#define Query_for_list_of_set_vars \
"SELECT name FROM "\
" (SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings "\
***************
*** 461,468 ****
--- 593,604 ----
* CREATE CONSTRAINT TRIGGER is not supported here because it is designed
* to be used only by pg_dump.
*/
+ <<<<<<< tab-complete.c
+ {"CONVERSION", NULL, &Query_for_list_of_conversions},
+ =======
{"CONFIGURATION", Query_for_list_of_ts_configurations, NULL, true},
{"CONVERSION", "SELECT pg_catalog.quote_ident(conname) FROM pg_catalog.pg_conversion WHERE substring(pg_catalog.quote_ident(conname),1,%d)='%s'"},
+ >>>>>>> 1.167
{"DATABASE", Query_for_list_of_databases},
{"DICTIONARY", Query_for_list_of_ts_dictionaries, NULL, true},
{"DOMAIN", NULL, &Query_for_list_of_domains},
***************
*** 562,570 ****
--- 698,712 ----
static const char *const backslash_commands[] = {
"\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
+ <<<<<<< tab-complete.c
+ "\\d", "\\da", "\\daS", "\\db", "\\dc", "\\dcS", "\\dC", "\\dd", "\\ddS", "\\dD", "\\dDS",
+ "\\df", "\\dfS", "\\dg", "\\di", "\\diS", "\\dl", "\\dn", "\\do", "\\doS", "\\dp", "\\ds",
+ "\\dt", "\\dtS", "\\dT", "\\dTS", "\\dv", "\\du",
+ =======
"\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl",
"\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du",
+ >>>>>>> 1.167
"\\e", "\\echo", "\\encoding",
"\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
***************
*** 2001,2019 ****
/* Backslash commands */
- /* TODO: \dc \dd \dl */
else if (strcmp(prev_wd, "\\connect") == 0 || strcmp(prev_wd, "\\c") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_databases);
else if (strcmp(prev_wd, "\\d") == 0 || strcmp(prev_wd, "\\d+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisv, NULL);
else if (strcmp(prev_wd, "\\da") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
else if (strcmp(prev_wd, "\\db") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
else if (strcmp(prev_wd, "\\dD") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
else if (strcmp(prev_wd, "\\df") == 0 || strcmp(prev_wd, "\\df+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
else if (strcmp(prev_wd, "\\dF") == 0 || strcmp(prev_wd, "\\dF+") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_ts_configurations);
else if (strcmp(prev_wd, "\\dFd") == 0 || strcmp(prev_wd, "\\dFd+") == 0)
--- 2143,2176 ----
/* Backslash commands */
else if (strcmp(prev_wd, "\\connect") == 0 || strcmp(prev_wd, "\\c") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_databases);
else if (strcmp(prev_wd, "\\d") == 0 || strcmp(prev_wd, "\\d+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisv, NULL);
else if (strcmp(prev_wd, "\\da") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (strcmp(prev_wd, "\\daS") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_aggregates);
else if (strcmp(prev_wd, "\\db") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
+ else if (strcmp(prev_wd, "\\dc") == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_conversions, NULL);
+ else if (strcmp(prev_wd, "\\dcS") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_conversions);
+ else if (strcmp(prev_wd, "\\dd") == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_comments, NULL);
+ else if (strcmp(prev_wd, "\\ddS") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_comments);
else if (strcmp(prev_wd, "\\dD") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
+ else if (strcmp(prev_wd, "\\dDS") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_domains);
else if (strcmp(prev_wd, "\\df") == 0 || strcmp(prev_wd, "\\df+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ <<<<<<< tab-complete.c
+ else if (strcmp(prev_wd, "\\dfS") == 0 || strcmp(prev_wd, "\\dfS+") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_functions);
+ =======
else if (strcmp(prev_wd, "\\dF") == 0 || strcmp(prev_wd, "\\dF+") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_ts_configurations);
else if (strcmp(prev_wd, "\\dFd") == 0 || strcmp(prev_wd, "\\dFd+") == 0)
***************
*** 2022,2031 ****
--- 2179,2193 ----
COMPLETE_WITH_QUERY(Query_for_list_of_ts_parsers);
else if (strcmp(prev_wd, "\\dFt") == 0 || strcmp(prev_wd, "\\dFt+") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_ts_templates);
+ >>>>>>> 1.167
else if (strcmp(prev_wd, "\\di") == 0 || strcmp(prev_wd, "\\di+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
+ else if (strcmp(prev_wd, "\\dl") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_largeobjects);
else if (strcmp(prev_wd, "\\dn") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+ else if (strcmp(prev_wd, "\\do") == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operators, NULL);
else if (strcmp(prev_wd, "\\dp") == 0 || strcmp(prev_wd, "\\z") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL);
else if (strcmp(prev_wd, "\\ds") == 0 || strcmp(prev_wd, "\\ds+") == 0)
***************
*** 2036,2041 ****
--- 2198,2205 ----
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
else if (strcmp(prev_wd, "\\dT") == 0 || strcmp(prev_wd, "\\dT+") == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+ else if (strcmp(prev_wd, "\\dTS") == 0 || strcmp(prev_wd, "\\dTS+") == 0)
+ COMPLETE_WITH_QUERY(Query_for_list_of_system_datatypes);
else if (strcmp(prev_wd, "\\du") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
else if (strcmp(prev_wd, "\\dv") == 0 || strcmp(prev_wd, "\\dv+") == 0)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
