Re: psql \df choose functions by their arguments - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: psql \df choose functions by their arguments |
| Date | |
| Msg-id | 1767656.1617847208@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: psql \df choose functions by their arguments (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
I wrote:
> I had an idea about that. I've not tested this, but I think it would be
> a trivial matter of adding a coalesce() call to make the query act like
> the type name for a not-present argument is an empty string, rather than
> NULL which is what it gets right now. Then you could do what I think
> you're asking for with
> \df foo integer ""
Actually, what would make more sense is to treat "-" as specifying
a non-existent argument. There are precedents for that in, eg, \c,
and a dash is a little more robust than an empty-string argument.
So that leads me to 0001 attached.
> As for the point about "int" versus "integer" and so on, I wouldn't
> be averse to installing a mapping layer for that, so long as we
> did it to \dT as well.
And for that, I suggest 0002. (We only need mappings for cases that
don't work out-of-the-box, so your list seemed a bit redundant.)
regards, tom lane
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c1451c1672..ddb7043362 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1567,7 +1567,7 @@ testdb=>
<varlistentry>
- <term><literal>\df[anptwS+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>]</literal></term>
+ <term><literal>\df[anptwS+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>[ <replaceable class="parameter">arg_pattern</replaceable> ... ]
]</literal></term>
<listitem>
<para>
@@ -1580,6 +1580,11 @@ testdb=>
If <replaceable
class="parameter">pattern</replaceable> is specified, only
functions whose names match the pattern are shown.
+ Any additional arguments are type-name patterns, which are matched
+ to the type names of the first, second, and so on arguments of the
+ function. (Matching functions can have more arguments than what
+ you specify. To prevent that, write a dash <literal>-</literal> as
+ the last <replaceable class="parameter">arg_pattern</replaceable>.)
By default, only user-created
objects are shown; supply a pattern or the <literal>S</literal>
modifier to include system objects.
@@ -1589,14 +1594,6 @@ testdb=>
language, source code and description.
</para>
- <tip>
- <para>
- To look up functions taking arguments or returning values of a specific
- data type, use your pager's search capability to scroll through the
- <literal>\df</literal> output.
- </para>
- </tip>
-
</listitem>
</varlistentry>
@@ -1721,12 +1718,19 @@ testdb=>
<varlistentry>
- <term><literal>\do[S+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>]</literal></term>
+ <term><literal>\do[S+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>[ <replaceable class="parameter">arg_pattern</replaceable> [ <replaceable
class="parameter">arg_pattern</replaceable>] ] ]</literal></term>
<listitem>
<para>
Lists operators with their operand and result types.
If <replaceable class="parameter">pattern</replaceable> is
specified, only operators whose names match the pattern are listed.
+ If one <replaceable class="parameter">arg_pattern</replaceable> is
+ specified, only prefix operators whose right argument's type name
+ matches that pattern are listed.
+ If two <replaceable class="parameter">arg_pattern</replaceable>s
+ are specified, only binary operators whose argument type names match
+ those patterns are listed. (Alternatively, write <literal>-</literal>
+ for the unused argument of a unary operator.)
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
@@ -4986,6 +4990,22 @@ second | four
</programlisting>
</para>
+ <para>
+ Here is an example of using the <command>\df</command> command to
+ find only functions with names matching <literal>int*pl</literal>
+ and whose second argument is of type <type>bigint</type>:
+<programlisting>
+testdb=> <userinput>\df int*pl * bigint</userinput>
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+---------+------------------+---------------------+------
+ pg_catalog | int28pl | bigint | smallint, bigint | func
+ pg_catalog | int48pl | bigint | integer, bigint | func
+ pg_catalog | int8pl | bigint | bigint, bigint | func
+(3 rows)
+</programlisting>
+ </para>
+
<para>
When suitable, query results can be shown in a crosstab representation
with the <command>\crosstabview</command> command:
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e04ccc5b62..543401c6d6 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -72,6 +72,9 @@ static backslashResult exec_command_copyright(PsqlScanState scan_state, bool act
static backslashResult exec_command_crosstabview(PsqlScanState scan_state, bool active_branch);
static backslashResult exec_command_d(PsqlScanState scan_state, bool active_branch,
const char *cmd);
+static bool exec_command_dfo(PsqlScanState scan_state, const char *cmd,
+ const char *pattern,
+ bool show_verbose, bool show_system);
static backslashResult exec_command_edit(PsqlScanState scan_state, bool active_branch,
PQExpBuffer query_buf, PQExpBuffer previous_buf);
static backslashResult exec_command_ef_ev(PsqlScanState scan_state, bool active_branch,
@@ -790,7 +793,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'p':
case 't':
case 'w':
- success = describeFunctions(&cmd[2], pattern, show_verbose, show_system);
+ success = exec_command_dfo(scan_state, cmd, pattern,
+ show_verbose, show_system);
break;
default:
status = PSQL_CMD_UNKNOWN;
@@ -811,7 +815,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listSchemas(pattern, show_verbose, show_system);
break;
case 'o':
- success = describeOperators(pattern, show_verbose, show_system);
+ success = exec_command_dfo(scan_state, cmd, pattern,
+ show_verbose, show_system);
break;
case 'O':
success = listCollations(pattern, show_verbose, show_system);
@@ -951,6 +956,45 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
return status;
}
+/* \df and \do; messy enough to split out of exec_command_d */
+static bool
+exec_command_dfo(PsqlScanState scan_state, const char *cmd,
+ const char *pattern,
+ bool show_verbose, bool show_system)
+{
+ bool success;
+ char *arg_patterns[FUNC_MAX_ARGS];
+ int num_arg_patterns = 0;
+
+ /* Collect argument-type patterns too */
+ if (pattern) /* otherwise it was just \df or \do */
+ {
+ char *ap;
+
+ while ((ap = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true)) != NULL)
+ {
+ arg_patterns[num_arg_patterns++] = ap;
+ if (num_arg_patterns >= FUNC_MAX_ARGS)
+ break; /* protect limited-size array */
+ }
+ }
+
+ if (cmd[1] == 'f')
+ success = describeFunctions(&cmd[2], pattern,
+ arg_patterns, num_arg_patterns,
+ show_verbose, show_system);
+ else
+ success = describeOperators(pattern,
+ arg_patterns, num_arg_patterns,
+ show_verbose, show_system);
+
+ while (--num_arg_patterns >= 0)
+ free(arg_patterns[num_arg_patterns]);
+
+ return success;
+}
+
/*
* \e or \edit -- edit the current query buffer, or edit a file and
* make it the query buffer
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 52f7b2ce78..cd829e0759 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -312,7 +312,9 @@ describeTablespaces(const char *pattern, bool verbose)
* and you can mix and match these in any order.
*/
bool
-describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
+describeFunctions(const char *functypes, const char *func_pattern,
+ char **arg_patterns, int num_arg_patterns,
+ bool verbose, bool showSystem)
{
bool showAggregate = strchr(functypes, 'a') != NULL;
bool showNormal = strchr(functypes, 'n') != NULL;
@@ -524,6 +526,14 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
"\nFROM pg_catalog.pg_proc p"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
+ for (int i = 0; i < num_arg_patterns; i++)
+ {
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_type t%d ON t%d.oid = p.proargtypes[%d]\n"
+ " LEFT JOIN pg_catalog.pg_namespace nt%d ON nt%d.oid = t%d.typnamespace\n",
+ i, i, i, i, i, i);
+ }
+
if (verbose)
appendPQExpBufferStr(&buf,
" LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
@@ -629,11 +639,42 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
appendPQExpBufferStr(&buf, " )\n");
}
- processSQLNamePattern(pset.db, &buf, pattern, have_where, false,
+ processSQLNamePattern(pset.db, &buf, func_pattern, have_where, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
- if (!showSystem && !pattern)
+ for (int i = 0; i < num_arg_patterns; i++)
+ {
+ if (strcmp(arg_patterns[i], "-") != 0)
+ {
+ /*
+ * Match type-name patterns against either internal or external
+ * name, like \dT. Unlike \dT, there seems no reason to
+ * discriminate against arrays or composite types.
+ */
+ char nspname[64];
+ char typname[64];
+ char ft[64];
+ char tiv[64];
+
+ snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
+ snprintf(typname, sizeof(typname), "t%d.typname", i);
+ snprintf(ft, sizeof(ft),
+ "pg_catalog.format_type(t%d.oid, NULL)", i);
+ snprintf(tiv, sizeof(tiv),
+ "pg_catalog.pg_type_is_visible(t%d.oid)", i);
+ processSQLNamePattern(pset.db, &buf, arg_patterns[i],
+ true, false,
+ nspname, typname, ft, tiv);
+ }
+ else
+ {
+ /* "-" pattern specifies no such parameter */
+ appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
+ }
+ }
+
+ if (!showSystem && !func_pattern)
appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
@@ -787,7 +828,9 @@ describeTypes(const char *pattern, bool verbose, bool showSystem)
* Describe operators
*/
bool
-describeOperators(const char *pattern, bool verbose, bool showSystem)
+describeOperators(const char *oper_pattern,
+ char **arg_patterns, int num_arg_patterns,
+ bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
@@ -836,14 +879,65 @@ describeOperators(const char *pattern, bool verbose, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
gettext_noop("Description"));
- if (!showSystem && !pattern)
+ if (num_arg_patterns >= 2)
+ {
+ num_arg_patterns = 2; /* ignore any additional arguments */
+ appendPQExpBufferStr(&buf,
+ " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft\n"
+ " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n"
+ " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright\n"
+ " LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace\n");
+ }
+ else if (num_arg_patterns == 1)
+ {
+ appendPQExpBufferStr(&buf,
+ " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprright\n"
+ " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n");
+ }
+
+ if (!showSystem && !oper_pattern)
appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
- processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
+ processSQLNamePattern(pset.db, &buf, oper_pattern,
+ !showSystem && !oper_pattern, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
+ if (num_arg_patterns == 1)
+ appendPQExpBufferStr(&buf, " AND o.oprleft = 0\n");
+
+ for (int i = 0; i < num_arg_patterns; i++)
+ {
+ if (strcmp(arg_patterns[i], "-") != 0)
+ {
+ /*
+ * Match type-name patterns against either internal or external
+ * name, like \dT. Unlike \dT, there seems no reason to
+ * discriminate against arrays or composite types.
+ */
+ char nspname[64];
+ char typname[64];
+ char ft[64];
+ char tiv[64];
+
+ snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
+ snprintf(typname, sizeof(typname), "t%d.typname", i);
+ snprintf(ft, sizeof(ft),
+ "pg_catalog.format_type(t%d.oid, NULL)", i);
+ snprintf(tiv, sizeof(tiv),
+ "pg_catalog.pg_type_is_visible(t%d.oid)", i);
+ processSQLNamePattern(pset.db, &buf, arg_patterns[i],
+ true, false,
+ nspname, typname, ft, tiv);
+ }
+ else
+ {
+ /* "-" pattern specifies no such parameter */
+ appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
+ }
+ }
+
appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
res = PSQLexec(buf.data);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 39856a0c7e..71b320f1fc 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -19,13 +19,17 @@ extern bool describeAccessMethods(const char *pattern, bool verbose);
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df, \dfa, \dfn, \dft, \dfw, etc. */
-extern bool describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem);
+extern bool describeFunctions(const char *functypes, const char *func_pattern,
+ char **arg_patterns, int num_arg_patterns,
+ bool verbose, bool showSystem);
/* \dT */
extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
/* \do */
-extern bool describeOperators(const char *pattern, bool verbose, bool showSystem);
+extern bool describeOperators(const char *oper_pattern,
+ char **arg_patterns, int num_arg_patterns,
+ bool verbose, bool showSystem);
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac9a89a889..36501d5e2b 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -166,7 +166,7 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(133, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(135, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -240,7 +240,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data wrappers\n"));
- fprintf(output, _(" \\df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions\n"));
+ fprintf(output, _(" \\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]\n"));
+ fprintf(output, _(" list [only agg/normal/procedure/trigger/window] functions\n"));
fprintf(output, _(" \\dF[+] [PATTERN] list text search configurations\n"));
fprintf(output, _(" \\dFd[+] [PATTERN] list text search dictionaries\n"));
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
@@ -251,7 +252,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
fprintf(output, _(" \\dn[S+] [PATTERN] list schemas\n"));
- fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
+ fprintf(output, _(" \\do[S] [OPPTRN [TYPEPTRN [TYPEPTRN]]] list operators\n"));
fprintf(output, _(" \\dO[S+] [PATTERN] list collations\n"));
fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
fprintf(output, _(" \\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]\n"));
diff --git a/src/fe_utils/string_utils.c b/src/fe_utils/string_utils.c
index 9a1ea9ab98..5b206c7481 100644
--- a/src/fe_utils/string_utils.c
+++ b/src/fe_utils/string_utils.c
@@ -1062,10 +1062,16 @@ patternToSQLRegex(int encoding, PQExpBuffer dbnamebuf, PQExpBuffer schemabuf,
* regexp errors. Outside quotes, however, let them pass through
* as-is; this lets knowledgeable users build regexp expressions
* that are more powerful than shell-style patterns.
+ *
+ * As an exception to that, though, always quote "[]", as that's
+ * much more likely to be an attempt to write an array type name
+ * than it is to be the start of a regexp bracket expression.
*/
if ((inquotes || force_escape) &&
strchr("|*+?()[]{}.^$\\", ch))
appendPQExpBufferChar(curbuf, '\\');
+ else if (ch == '[' && cp[1] == ']')
+ appendPQExpBufferChar(curbuf, '\\');
i = PQmblen(cp, encoding);
while (i-- && *cp)
{
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 9a51940530..672937b2f8 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5078,6 +5078,107 @@ List of access methods
hash | uuid_ops | uuid | uuid | 2 | uuid_hash_extended
(5 rows)
+-- check \df, \do with argument specifications
+\df *sqrt
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+--------------+------------------+---------------------+------
+ pg_catalog | dsqrt | double precision | double precision | func
+ pg_catalog | numeric_sqrt | numeric | numeric | func
+ pg_catalog | sqrt | double precision | double precision | func
+ pg_catalog | sqrt | numeric | numeric | func
+(4 rows)
+
+\df *sqrt num*
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+--------------+------------------+---------------------+------
+ pg_catalog | numeric_sqrt | numeric | numeric | func
+ pg_catalog | sqrt | numeric | numeric | func
+(2 rows)
+
+\df int*pl
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+-------------+------------------+---------------------+------
+ pg_catalog | int24pl | integer | smallint, integer | func
+ pg_catalog | int28pl | bigint | smallint, bigint | func
+ pg_catalog | int2pl | smallint | smallint, smallint | func
+ pg_catalog | int42pl | integer | integer, smallint | func
+ pg_catalog | int48pl | bigint | integer, bigint | func
+ pg_catalog | int4pl | integer | integer, integer | func
+ pg_catalog | int82pl | bigint | bigint, smallint | func
+ pg_catalog | int84pl | bigint | bigint, integer | func
+ pg_catalog | int8pl | bigint | bigint, bigint | func
+ pg_catalog | interval_pl | interval | interval, interval | func
+(10 rows)
+
+\df int*pl int4
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+---------+------------------+---------------------+------
+ pg_catalog | int42pl | integer | integer, smallint | func
+ pg_catalog | int48pl | bigint | integer, bigint | func
+ pg_catalog | int4pl | integer | integer, integer | func
+(3 rows)
+
+\df int*pl * pg_catalog.int8
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+---------+------------------+---------------------+------
+ pg_catalog | int28pl | bigint | smallint, bigint | func
+ pg_catalog | int48pl | bigint | integer, bigint | func
+ pg_catalog | int8pl | bigint | bigint, bigint | func
+(3 rows)
+
+\df acl* aclitem[]
+ List of functions
+ Schema | Name | Result data type | Argument data types
| Type
+------------+-------------+------------------+----------------------------------------------------------------------------------------------------+------
+ pg_catalog | aclcontains | boolean | aclitem[], aclitem
| func
+ pg_catalog | aclexplode | SETOF record | acl aclitem[], OUT grantor oid, OUT grantee oid, OUT privilege_type
text,OUT is_grantable boolean | func
+ pg_catalog | aclinsert | aclitem[] | aclitem[], aclitem
| func
+ pg_catalog | aclremove | aclitem[] | aclitem[], aclitem
| func
+(4 rows)
+
+\df has_database_privilege oid text
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+------------------------+------------------+---------------------+------
+ pg_catalog | has_database_privilege | boolean | oid, text | func
+ pg_catalog | has_database_privilege | boolean | oid, text, text | func
+(2 rows)
+
+\df has_database_privilege oid text -
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+------------------------+------------------+---------------------+------
+ pg_catalog | has_database_privilege | boolean | oid, text | func
+(1 row)
+
+\dfa bit* small*
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+------------+---------+------------------+---------------------+------
+ pg_catalog | bit_and | smallint | smallint | agg
+ pg_catalog | bit_or | smallint | smallint | agg
+ pg_catalog | bit_xor | smallint | smallint | agg
+(3 rows)
+
+\do - pg_catalog.int4
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+------------+------+---------------+----------------+-------------+-------------
+ pg_catalog | - | | integer | integer | negate
+(1 row)
+
+\do && anyarray *
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+------------+------+---------------+----------------+-------------+-------------
+ pg_catalog | && | anyarray | anyarray | boolean | overlaps
+(1 row)
+
--
-- combined queries
--
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index bf06bb87b5..f90a0270fc 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1229,6 +1229,19 @@ drop role regress_partitioning_role;
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
+-- check \df, \do with argument specifications
+\df *sqrt
+\df *sqrt num*
+\df int*pl
+\df int*pl int4
+\df int*pl * pg_catalog.int8
+\df acl* aclitem[]
+\df has_database_privilege oid text
+\df has_database_privilege oid text -
+\dfa bit* small*
+\do - pg_catalog.int4
+\do && anyarray *
+
--
-- combined queries
--
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index cd829e0759..e65fa07049 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -28,6 +28,7 @@
#include "settings.h"
#include "variables.h"
+static const char *map_typename_pattern(const char *pattern);
static bool describeOneTableDetails(const char *schemaname,
const char *relationname,
const char *oid,
@@ -663,7 +664,8 @@ describeFunctions(const char *functypes, const char *func_pattern,
"pg_catalog.format_type(t%d.oid, NULL)", i);
snprintf(tiv, sizeof(tiv),
"pg_catalog.pg_type_is_visible(t%d.oid)", i);
- processSQLNamePattern(pset.db, &buf, arg_patterns[i],
+ processSQLNamePattern(pset.db, &buf,
+ map_typename_pattern(arg_patterns[i]),
true, false,
nspname, typname, ft, tiv);
}
@@ -787,20 +789,24 @@ describeTypes(const char *pattern, bool verbose, bool showSystem)
"WHERE c.oid = t.typrelid))\n");
/*
- * do not include array types (before 8.3 we have to use the assumption
- * that their names start with underscore)
+ * do not include array types unless the pattern contains [] (before 8.3
+ * we have to use the assumption that their names start with underscore)
*/
- if (pset.sversion >= 80300)
- appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND
el.typarray= t.oid)\n");
- else
- appendPQExpBufferStr(&buf, " AND t.typname !~ '^_'\n");
+ if (strstr(pattern, "[]") == NULL)
+ {
+ if (pset.sversion >= 80300)
+ appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem
ANDel.typarray = t.oid)\n");
+ else
+ appendPQExpBufferStr(&buf, " AND t.typname !~ '^_'\n");
+ }
if (!showSystem && !pattern)
appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
/* Match name pattern against either internal or external name */
- processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ processSQLNamePattern(pset.db, &buf, map_typename_pattern(pattern),
+ true, false,
"n.nspname", "t.typname",
"pg_catalog.format_type(t.oid, NULL)",
"pg_catalog.pg_type_is_visible(t.oid)");
@@ -822,6 +828,60 @@ describeTypes(const char *pattern, bool verbose, bool showSystem)
return true;
}
+/*
+ * Map some variant type names accepted by the backend grammar into
+ * canonical type names.
+ *
+ * Helper for \dT and other functions that take typename patterns.
+ * This doesn't completely mask the fact that these names are special;
+ * for example, a pattern of "dec*" won't magically match "numeric".
+ * But it goes a long way to reduce the surprise factor.
+ */
+static const char *
+map_typename_pattern(const char *pattern)
+{
+ static const char *const typename_map[] = {
+ /*
+ * These names are accepted by gram.y, although they are neither the
+ * "real" name seen in pg_type nor the canonical name printed by
+ * format_type().
+ */
+ "decimal", "numeric",
+ "float", "double precision",
+ "int", "integer",
+
+ /*
+ * We also have to map the array names for cases where the canonical
+ * name is different from what pg_type says.
+ */
+ "bool[]", "boolean[]",
+ "decimal[]", "numeric[]",
+ "float[]", "double precision[]",
+ "float4[]", "real[]",
+ "float8[]", "double precision[]",
+ "int[]", "integer[]",
+ "int2[]", "smallint[]",
+ "int4[]", "integer[]",
+ "int8[]", "bigint[]",
+ "time[]", "time without time zone[]",
+ "timetz[]", "time with time zone[]",
+ "timestamp[]", "timestamp without time zone[]",
+ "timestamptz[]", "timestamp with time zone[]",
+ "varbit[]", "bit varying[]",
+ "varchar[]", "character varying[]",
+ NULL
+ };
+
+ if (pattern == NULL)
+ return NULL;
+ for (int i = 0; typename_map[i] != NULL; i += 2)
+ {
+ if (strcmp(pattern, typename_map[i]) == 0)
+ return typename_map[i + 1];
+ }
+ return pattern;
+}
+
/*
* \do
@@ -927,7 +987,8 @@ describeOperators(const char *oper_pattern,
"pg_catalog.format_type(t%d.oid, NULL)", i);
snprintf(tiv, sizeof(tiv),
"pg_catalog.pg_type_is_visible(t%d.oid)", i);
- processSQLNamePattern(pset.db, &buf, arg_patterns[i],
+ processSQLNamePattern(pset.db, &buf,
+ map_typename_pattern(arg_patterns[i]),
true, false,
nspname, typname, ft, tiv);
}
pgsql-hackers by date: