Re: psql \df choose functions by their arguments - Mailing list pgsql-hackers

Greg Sabino Mullane <htamfids@gmail.com> writes:
>  [ v6-psql-df-pick-function-by-type.patch ]

I looked this over.  I like the idea a lot, but not much of anything
about the implementation.  I think the additional arguments should be
matched to the function types using the same rules as for \dT.  That
allows patterns for the argument type names, which is particularly
useful if you want to do something like
    \df foo * integer
to find functions whose second argument is integer, without restricting
the first argument.

As a lesser quibble, splitting the arguments with strtokx is a hack;
we should let the normal psql scanner collect the arguments.

So that leads me to the attached, which I think is committable.  Since
we're down to the last day of the CF, I'm going to push this shortly if
there aren't squawks soon.

            regards, tom lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index c1451c1672..db987c14bb 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.
         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>

@@ -4986,6 +4981,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..8d2c8e85df 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_df(PsqlScanState scan_state, const char *cmd,
+                            const char *func_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_df(scan_state, cmd, pattern,
+                                                  show_verbose, show_system);
                         break;
                     default:
                         status = PSQL_CMD_UNKNOWN;
@@ -951,6 +955,40 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
     return status;
 }

+/* \df and related commands; messy enough to split out of exec_command_d */
+static bool
+exec_command_df(PsqlScanState scan_state, const char *cmd,
+                const char *func_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 (func_pattern)            /* otherwise it was just \df */
+    {
+        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 */
+        }
+    }
+
+    success = describeFunctions(&cmd[2], func_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 440249ff69..d31cdac3a7 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;
@@ -517,6 +519,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");
@@ -622,11 +632,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), "tn%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");

diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 39856a0c7e..01deed6799 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -19,7 +19,9 @@ 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);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ac9a89a889..46e068cd7c 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"));
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..3135b2ab4b 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5078,6 +5078,78 @@ List of access methods
  hash  | uuid_ops        | uuid                 | uuid                  |      2 | uuid_hash_extended
 (5 rows)

+-- check \df 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 * 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)
+
 --
 -- combined queries
 --
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index bf06bb87b5..06b470aa8d 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1229,6 +1229,15 @@ drop role regress_partitioning_role;
 \dAp+ btree float_ops
 \dAp * pg_catalog.uuid_ops

+-- check \df with argument specifications
+\df *sqrt
+\df *sqrt num*
+\df int*pl
+\df int*pl int4
+\df int*pl * int8
+\df acl* aclitem[]
+\dfa bit* small*
+
 --
 -- combined queries
 --