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 | 1727740.1617832704@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:
> Greg Sabino Mullane <htamfids@gmail.com> writes:
>> * SQL error on \df foo a..b as well as one on \df foo (bigint bigint)
> The first one seems to be a bug, will look.
Argh, silly typo (and I'd failed to test the schema-qualified-name case).
While I was thinking about use-cases for this, I realized that at least
for me, being able to restrict \do operator searches by input type would
be even more useful than is true for \df. Operator names tend to be
overloaded even more heavily than functions. So here's a v8 that
also fixes \do in the same spirit.
(With respect to the other point: for \do it does seem to make sense
to constrain the match to operators with exactly as many arguments
as specified. I still say that's a bad idea for functions, though.)
regards, tom lane
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c1451c1672..d54e8b2457 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,9 @@ 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. (Not all the function's arguments need be mentioned.)
By default, only user-created
objects are shown; supply a pattern or the <literal>S</literal>
modifier to include system objects.
@@ -1589,14 +1592,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 +1716,18 @@ 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 infix operators whose argument type names match
+ those patterns are listed.
By default, only user-created objects are shown; supply a
pattern or the <literal>S</literal> modifier to include system
objects.
@@ -4986,6 +4987,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..06ef4b26ea 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,32 @@ 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++)
+ {
+ /*
+ * 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);
+ }
+
+ if (!showSystem && !func_pattern)
appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
@@ -787,7 +818,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 +869,55 @@ 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++)
+ {
+ /*
+ * 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);
+ }
+
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..c63be9e927 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5078,6 +5078,92 @@ 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)
+
+\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 - 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..9e897e18d8 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1229,6 +1229,17 @@ 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[]
+\dfa bit* small*
+\do - int4
+\do && anyarray *
+
--
-- combined queries
--
pgsql-hackers by date: