-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Improve psql \df to choose functions by their arguments
== OVERVIEW
Having to scroll through same-named functions with different argument types
when you know exactly which one you want is annoying at best, error causing
at worst. This patch enables a quick narrowing of functions with the
same name but different arguments. For example, to see the full details
of a function names "myfunc" with a TEXT argument, but not showing
the version of "myfunc" with a BIGINT argument, one can now do:
psql=# \df myfunc text
For this, we are fairly liberal in what we accept, and try to be as
intuitive as possible.
Features:
* Type names are case insensitive. Whitespace is optional, but quoting is
respected:
greg=# \df myfunc text "character varying" INTEGER
* Abbreviations of common types is permitted (because who really likes
to type out "character varying"?), so the above could also be written as:
greg=# \df myfunc text varchar int
* The matching is greedy, so you can see everything matching a subset:
greg=# \df myfunc timestamptz
List of functions
Schema | Name | Result data type | Argument data types
| Type
-
--------+--------+------------------+-------------------------------------------+------
public | myfunc | void | timestamp with time zone
| func
public | myfunc | void | timestamp with time zone, bigint
| func
public | myfunc | void | timestamp with time zone, bigint, boolean
| func
public | myfunc | void | timestamp with time zone, integer
| func
public | myfunc | void | timestamp with time zone, text, cidr
| func
(5 rows)
* The appearance of a closing paren indicates we do not want the greediness:
greg=# \df myfunc (timestamptz, bigint)
List of functions
Schema | Name | Result data type | Argument data types | Type
- --------+--------+------------------+----------------------------------+------
public | myfunc | void | timestamp with time zone, bigint | func
(1 row)
== TAB COMPLETION:
I'm not entirely happy with this, but I figure piggybacking
onto COMPLETE_WITH_FUNCTION_ARG is better than nothing at all.
Ideally we'd walk prev*_wd to refine the returned list, but
that's an awful lot of complexity for very little gain, and I think
the current behavior of showing the complete list of args each time
should suffice.
== DOCUMENTATION:
The new feature is briefly mentioned: wordsmithing help in the
sgml section is appreciated. I'm not sure how many of the above features
need to be documented in detail.
Regarding psql/help.c, I don't think this really warrants a change there.
As it is, we've gone through great lengths to keep this overloaded backslash
command left justified with the rest!
== TESTS:
I put this into psql.c, seems the best place. Mostly testing out
basic functionality, quoting, and the various abbreviations. Not much
else to test, near as I can tell, as this is a pure convienence addition
and shouldn't affect anything else. Any extra words after a function name
for \df was previously treated as an error.
== IMPLEMENTATION:
Rather than messing with psqlscanslash, we simply slurp in the entire rest
of the line via psql_scan_slash_option (all of which was previously ignored).
This is passed to describeFunction, which then uses strtokx to break it
into tokens. We look for a match by comparing the current proargtypes entry,
casted to text, against the lowercase version of the token found by strtokx.
Along the way, we convert things like "timestamptz" to the official version
(i.e. "timestamp with time zone"). If any of the tokens start with a closing
paren, we immediately stop parsing and set pronargs to the current number
of valid tokens, thereby forcing a match to one (or zero) functions.
dcd972f6b945070ef4454ea39d25378427a90e89 df.patch
-----BEGIN PGP SIGNATURE-----
iF0EAREDAB0WIQQlKd9quPeUB+lERbS8m5BnFJZKyAUCX4bsgwAKCRC8m5BnFJZK
yGDvAJ9ix8jzwtTwKLDQUgu5yb/iBoC7EQCfQsf8LLZ0RWsiiMposi57u3S94nE=
=rQj2
-----END PGP SIGNATURE-----
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ee3fc09577..c63255cebc 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1548,7 +1548,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> ] [ types ]</literal></term>
<listitem>
<para>
@@ -1561,6 +1561,7 @@ testdb=>
If <replaceable
class="parameter">pattern</replaceable> is specified, only
functions whose names match the pattern are shown.
+ Any additional words are considered type arguments to help narrow the list of returned functions.
By default, only user-created
objects are shown; supply a pattern or the <literal>S</literal>
modifier to include system objects.
@@ -1572,7 +1573,7 @@ testdb=>
<tip>
<para>
- To look up functions taking arguments or returning values of a specific
+ To look up functions returning values of a specific
data type, use your pager's search capability to scroll through the
<literal>\df</literal> output.
</para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index d4aa0976b5..72354a5d37 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -783,6 +783,8 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'f': /* function subsystem */
switch (cmd[2])
{
+ char *funcargs;
+
case '\0':
case '+':
case 'S':
@@ -791,7 +793,9 @@ 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);
+ funcargs = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
+ success = describeFunctions(&cmd[2], pattern, show_verbose, show_system, funcargs);
+ free(funcargs);
break;
default:
status = PSQL_CMD_UNKNOWN;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 58de433fd3..98ce4f8085 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -26,6 +26,7 @@
#include "fe_utils/print.h"
#include "fe_utils/string_utils.h"
#include "settings.h"
+#include "stringutils.h"
#include "variables.h"
static bool describeOneTableDetails(const char *schemaname,
@@ -312,7 +313,7 @@ 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 *pattern, bool verbose, bool showSystem, const char *funcargs)
{
bool showAggregate = strchr(functypes, 'a') != NULL;
bool showNormal = strchr(functypes, 'n') != NULL;
@@ -626,6 +627,49 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
+ /*
+ * Check for any additional arguments to narrow down which functions are
+ * desired
+ */
+ if (funcargs)
+ {
+
+ int x = 0;
+ int argoffset = 0;
+ char *functoken;
+
+ while ((functoken = strtokx(x++ ? NULL : funcargs, " \t\n\r", ".,();", "\"", 0, false, true, pset.encoding)))
+ {
+ if (isalpha(functoken[0]))
+ {
+ appendPQExpBuffer(&buf, " AND p.proargtypes[%d]::regtype::text = LOWER(%s)::text\n",
+ argoffset++,
+
+ /*
+ * This is not a comprehensive list - just a little help
+ */
+ pg_strcasecmp(functoken, "bool") == 0 ? "'boolean'"
+ : pg_strcasecmp(functoken, "char") == 0 ? "'character'"
+ : pg_strcasecmp(functoken, "double") == 0 ? "'double precision'"
+ : pg_strcasecmp(functoken, "float") == 0 ? "'double precision'"
+ : pg_strcasecmp(functoken, "int") == 0 ? "'integer'"
+ : pg_strcasecmp(functoken, "time") == 0 ? "'time without time zone'"
+ : pg_strcasecmp(functoken, "timetz") == 0 ? "'time with time zone'"
+ : pg_strcasecmp(functoken, "timestamp") == 0 ? "'timestamp without time zone'"
+ : pg_strcasecmp(functoken, "timestamptz") == 0 ? "'timestamp with time zone'"
+ : pg_strcasecmp(functoken, "varbit") == 0 ? "'bit varying'"
+ : pg_strcasecmp(functoken, "varchar") == 0 ? "'character varying'"
+ : PQescapeLiteral(pset.db, functoken, strlen(functoken)));
+
+ }
+ else if (functoken[0] == ')' && argoffset)
+ { /* Force limit the number of args */
+ appendPQExpBuffer(&buf, " AND p.pronargs = %d\n", argoffset);
+ break;
+ }
+ }
+ }
+
if (!showSystem && !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 f0e3ec957c..a3400afb8c 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -19,7 +19,7 @@ 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 *pattern, bool verbose, bool showSystem, const char *funcargs);
/* \dT */
extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 24c7b414cf..d135364e2c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3778,6 +3778,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
else if (TailMatchesCS("\\df*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+ else if (HeadMatches("\\df", MatchAny))
+ COMPLETE_WITH_FUNCTION_ARG(previous_words[previous_words_count - 2]);
else if (TailMatchesCS("\\dFd*"))
COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index daac0ff49d..fd69956d07 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5072,3 +5072,110 @@ List of access methods
hash | uuid_ops | uuid | uuid | 2 | uuid_hash_extended
(5 rows)
+-- list specific functions of the same name but different args
+create function mtest(int) returns int as $$ select 1; $$ language sql;
+create function mtest(int,text) returns int as $$ select 1; $$ language sql;
+create function mtest(bool,character(10),varchar(10)) returns int as $$ select 1; $$ language sql;
+create function mtest(float,float,int) returns int as $$ select 1; $$ language sql;
+create function mtest(time,timetz) returns int as $$ select 1; $$ language sql;
+create function mtest(timestamp,timestamptz) returns int as $$ select 1; $$ language sql;
+create function mtest(varbit) returns int as $$ select 1; $$ language sql;
+-- With no arguments, all functions are shown
+\df mtest
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+-------------------------------------------------------+------
+ public | mtest | integer | bit varying | func
+ public | mtest | integer | boolean, character, character varying | func
+ public | mtest | integer | double precision, double precision, integer | func
+ public | mtest | integer | integer | func
+ public | mtest | integer | integer, text | func
+ public | mtest | integer | timestamp without time zone, timestamp with time zone | func
+ public | mtest | integer | time without time zone, time with time zone | func
+(7 rows)
+
+-- An invalid argument type matches nothing
+\df mtest mint
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
+-- A single argument type matches all functions starting with that type
+\df mtest integer
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------+------
+ public | mtest | integer | integer | func
+ public | mtest | integer | integer, text | func
+(2 rows)
+
+-- Two argument types match up
+\df mtest integer text
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------+------
+ public | mtest | integer | integer, text | func
+(1 row)
+
+-- A single argument type only matches a single argument if a closing paren is added
+\df mtest (integer)
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------+------
+ public | mtest | integer | integer | func
+(1 row)
+
+-- Allowed abbreviations: bool->boolean, char -> character, varchar -> character varying
+\df mtest bool,char,varchar
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------------------------+------
+ public | mtest | integer | boolean, character, character varying | func
+(1 row)
+
+-- Allowed abbreviations: double -> double precision, float - double precision, int -> integer
+\df mtest double float int
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------------------------------+------
+ public | mtest | integer | double precision, double precision, integer | func
+(1 row)
+
+-- Allowed abbreviations: time -> time without time zone, timetz -> time with time zone
+\df mtest (time timetz)
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------------------------------+------
+ public | mtest | integer | time without time zone, time with time zone | func
+(1 row)
+
+-- Allowed abbreviations: timestamp -> timestamp without time zone, timestamptz -> timestampt with time zone
+\df mtest timestamp timestamptz
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+-------------------------------------------------------+------
+ public | mtest | integer | timestamp without time zone, timestamp with time zone | func
+(1 row)
+
+-- Allowed abbreviation: varbit -> bit varying
+\df mtest varbit
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+-------+------------------+---------------------+------
+ public | mtest | integer | bit varying | func
+(1 row)
+
+drop function mtest(int);
+drop function mtest(int,text);
+drop function mtest(bool,char,varchar);
+drop function mtest(float,float,int);
+drop function mtest(time,timetz);
+drop function mtest(timestamp,timestamptz);
+drop function mtest(varbit);
+\df mtest
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 47b28d2a07..73f4d7cf90 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1225,3 +1225,53 @@ drop role regress_partitioning_role;
\dAo * pg_catalog.jsonb_path_ops
\dAp+ btree float_ops
\dAp * pg_catalog.uuid_ops
+
+-- list specific functions of the same name but different args
+
+create function mtest(int) returns int as $$ select 1; $$ language sql;
+create function mtest(int,text) returns int as $$ select 1; $$ language sql;
+create function mtest(bool,character(10),varchar(10)) returns int as $$ select 1; $$ language sql;
+create function mtest(float,float,int) returns int as $$ select 1; $$ language sql;
+create function mtest(time,timetz) returns int as $$ select 1; $$ language sql;
+create function mtest(timestamp,timestamptz) returns int as $$ select 1; $$ language sql;
+create function mtest(varbit) returns int as $$ select 1; $$ language sql;
+
+-- With no arguments, all functions are shown
+\df mtest
+
+-- An invalid argument type matches nothing
+\df mtest mint
+
+-- A single argument type matches all functions starting with that type
+\df mtest integer
+
+-- Two argument types match up
+\df mtest integer text
+
+-- A single argument type only matches a single argument if a closing paren is added
+\df mtest (integer)
+
+-- Allowed abbreviations: bool->boolean, char -> character, varchar -> character varying
+\df mtest bool,char,varchar
+
+-- Allowed abbreviations: double -> double precision, float - double precision, int -> integer
+\df mtest double float int
+
+-- Allowed abbreviations: time -> time without time zone, timetz -> time with time zone
+\df mtest (time timetz)
+
+-- Allowed abbreviations: timestamp -> timestamp without time zone, timestamptz -> timestampt with time zone
+\df mtest timestamp timestamptz
+
+-- Allowed abbreviation: varbit -> bit varying
+\df mtest varbit
+
+drop function mtest(int);
+drop function mtest(int,text);
+drop function mtest(bool,char,varchar);
+drop function mtest(float,float,int);
+drop function mtest(time,timetz);
+drop function mtest(timestamp,timestamptz);
+drop function mtest(varbit);
+
+\df mtest
