Re: psql \df choose functions by their arguments
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= -\df[anptwS+] [ pattern ] +\df[anptwS+] [ pattern [ arg_pattern ... ] ] @@ -1580,6 +1580,11 @@ testdb= If pattern 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 - as +the last arg_pattern.) By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. @@ -1589,14 +1594,6 @@ testdb= language, source code and description. - - -To look up functions taking arguments or returning values of a specific -data type, use your pager's search capability to scroll through the -\df output. - - - @@ -1721,12 +1718,19 @@ testdb= -\do[S+] [ pattern ] +\do[S+] [ pattern [ arg_pattern [ arg_pattern ] ] ] Lists operators with their operand and result types. If pattern is specified, only operators whose names match the pattern are listed. +If one arg_pattern is +specified, only prefix operators whose right argument's type name +matches that pattern are listed. +If two arg_patterns +are specified, only binary operators whose argument type names match +those patterns are listed. (Alternatively, write - +for the unused argument of a unary operator.) By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. @@ -4986,6 +4990,22 @@ second | four + + Here is an example of using the \df command to + find only functions with names matching int*pl + and whose second argument is of type bigint: + +testdb= \df int*pl * bigint + 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) + + + When suitable, query results can be shown in a crosstab representation with the \crosstabview 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([2], pattern, show_verbose, show_system); + success = exec_command_dfo(scan_state, cmd, pattern, + show_verbose, show_system); break; default: status
Re: psql \df choose functions by their arguments
I wrote: > Greg Sabino Mullane writes: >> * There seems to be no way (?) to limit the functions returned if they >> share a common root. The previous incantation allowed you to pull out >> foo(int) from foo(int, bigint). This was a big motivation for writing this >> patch. > Hmm, are you trying to say that a invocation with N arg patterns should > match only functions with exactly N arguments? We could do that, but > I'm not convinced it's an improvement over what I did here. Default > arguments are a counterexample. 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 "" Admittedly this is a bit of a hack, but to me this seems like a minority use-case, so maybe that's good enough. 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. regards, tom lane
Re: psql \df choose functions by their arguments
I wrote: > Greg Sabino Mullane 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= -\df[anptwS+] [ pattern ] +\df[anptwS+] [ pattern [ arg_pattern ... ] ] @@ -1580,6 +1580,9 @@ testdb= If pattern 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 S modifier to include system objects. @@ -1589,14 +1592,6 @@ testdb= language, source code and description. - - -To look up functions taking arguments or returning values of a specific -data type, use your pager's search capability to scroll through the -\df output. - - - @@ -1721,12 +1716,18 @@ testdb= -\do[S+] [ pattern ] +\do[S+] [ pattern [ arg_pattern [ arg_pattern ] ] ] Lists operators with their operand and result types. If pattern is specified, only operators whose names match the pattern are listed. +If one arg_pattern is +specified, only prefix operators whose right argument's type name +matches that pattern are listed. +If two arg_patterns +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 S modifier to include system objects. @@ -4986,6 +4987,22 @@ second | four + + Here is an example of using the \df command to + find only functions with names matching int*pl + and whose second argument is of type bigint: + +testdb= \df int*pl * bigint + 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) + + + When suitable, query results can be shown in a crosstab representation with the \crosstabview 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([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 =
Re: psql \df choose functions by their arguments
Greg Sabino Mullane writes: > I like the wildcard aspect, but I have a few issues with the patch: > * It doesn't respect some common abbreviations that work elsewhere (e.g. > CREATE FUNCTION). So while "int4" works, "int" does not. Nor does "float", > which thus requires the mandatory-double-quoted "double precision" "\dT int" doesn't match anything either. Maybe there's room to improve on that, but I don't think this patch should deviate from what \dT does. > * Adding commas to the args, as returned by psql itself via \df, provides > no matches. The docs are fairly clear that the args are to be space-separated, not comma-separated. This fits with psql's general treatment of backslash arguments, and I think trying to "improve" on it will just end badly. > * There seems to be no way (?) to limit the functions returned if they > share a common root. The previous incantation allowed you to pull out > foo(int) from foo(int, bigint). This was a big motivation for writing this > patch. Hmm, are you trying to say that a invocation with N arg patterns should match only functions with exactly N arguments? We could do that, but I'm not convinced it's an improvement over what I did here. Default arguments are a counterexample. > * 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. As for the second, I still don't agree that that should be within the mandated syntax. regards, tom lane
Re: psql \df choose functions by their arguments
I like the wildcard aspect, but I have a few issues with the patch: * It doesn't respect some common abbreviations that work elsewhere (e.g. CREATE FUNCTION). So while "int4" works, "int" does not. Nor does "float", which thus requires the mandatory-double-quoted "double precision" * Adding commas to the args, as returned by psql itself via \df, provides no matches. * There seems to be no way (?) to limit the functions returned if they share a common root. The previous incantation allowed you to pull out foo(int) from foo(int, bigint). This was a big motivation for writing this patch. * SQL error on \df foo a..b as well as one on \df foo (bigint bigint) Cheers, Greg
Re: psql \df choose functions by their arguments
Greg Sabino Mullane 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=
-\df[anptwS+] [ pattern ]
+\df[anptwS+] [ pattern [ arg_pattern ... ] ]
@@ -1580,6 +1580,9 @@ testdb=
If pattern 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 S
modifier to include system objects.
@@ -1589,14 +1592,6 @@ testdb=
language, source code and description.
-
-
-To look up functions taking arguments or returning values of a specific
-data type, use your pager's search capability to scroll through the
-\df output.
-
-
-
@@ -4986,6 +4981,22 @@ second | four
+
+ Here is an example of using the \df command to
+ find only functions with names matching int*pl
+ and whose second argument is of type bigint:
+
+testdb= \df int*pl * bigint
+ 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)
+
+
+
When suitable, query results can be shown in a crosstab representation
with the \crosstabview 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([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([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;
+}
Re: psql \df choose functions by their arguments
On 1/19/21 11:58 AM, Greg Sabino Mullane wrote: Ha ha ha, my bad, I am not sure why I left those out. Here is a new patch with int2, int4, and int8. Thanks for the email. Ian, does the new patch look good to you? Also, not sure why the target version for this patch is stable so I have updated it to PG14. Regards, -- -David da...@pgmasters.net
Re: psql \df choose functions by their arguments
Ha ha ha, my bad, I am not sure why I left those out. Here is a new patch with int2, int4, and int8. Thanks for the email. Cheers, Greg v6-psql-df-pick-function-by-type.patch Description: Binary data
Re: psql \df choose functions by their arguments
2021年1月15日(金) 1:46 Greg Sabino Mullane : > Thanks for the feedback: new version v5 (attached) has int8, plus the > suggested code formatting. > > Cheers, > Greg > Thanks for the update. In my preceding mail I meant we should add int2, int4 and int8 for completeness (apologies, I was a bit unclear there), as AFAICS that covers all aliases, even if these three are less widely used. FWIW one place where these do get used in substantial numbers is in the regression tests themselves: $ for L in 2 4 8; do git grep int$L src/test/regress/ | wc -l; done 544 2332 1353 Regards Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Re: psql \df choose functions by their arguments
Thanks for the feedback: new version v5 (attached) has int8, plus the suggested code formatting. Cheers, Greg v5-psql-df-pick-function-by-type.patch Description: Binary data
Re: psql \df choose functions by their arguments
Hi
I tried this patch out last year but was overrolled by Other Stuff before I got
around to providing any feedback, and was reminded of it just now when I was
trying to execute "\df somefunction text int" or similar, which had me
confused until I remembered it's not a feature yet, so it would
certainly be very
welcome to have this.
2020年11月3日(火) 23:27 Greg Sabino Mullane :
>
> Thanks for looking this over!
>
>>
>> some Abbreviations of common types are not added to the type_abbreviations[]
>> Such as:
>>
>> Int8=> bigint
>
>
> I wasn't aiming to provide a canonical list, as I personally have never seen
> anyone use int8 instead of bigint when (for example) creating a function, but
> I'm not strongly opposed to expanding the list.
I have vague memories of working with "int8" a bit (possibly related to an
Informix migration), anyway it seems easy enough to add them for completeness
as someone (possibly migrating from another database) might wonder why
it's not working.
Just a small code readability suggestion - in exec_command_d(), it seems
neater to put the funcargs declaration in a block together with the
code with which uses it (see attached diff).
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index d449cea66c..e8c1fd64bc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -781,8 +781,6 @@ 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,10 +789,14 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'p':
case 't':
case 'w':
+ {
+ char *funcargs;
+
funcargs = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
success = describeFunctions([2], pattern, show_verbose, show_system, funcargs);
free(funcargs);
break;
+ }
default:
status = PSQL_CMD_UNKNOWN;
break;
Re: psql \df choose functions by their arguments
On Sat, Jan 2, 2021 at 1:56 AM Thomas Munro wrote: > ... > It looks like there is a collation dependency here that causes the > test to fail on some systems: > Thanks for pointing that out. I tweaked the function definitions to hopefully sidestep the ordering issue - attached is v4. Cheers, Greg v4-psql-df-pick-function-by-type.patch Description: Binary data
Re: psql \df choose functions by their arguments
On Thu, Dec 31, 2020 at 7:01 AM Greg Sabino Mullane wrote: > Attached is the latest patch against HEAD - basically fixes a few typos. Hi Greg, It looks like there is a collation dependency here that causes the test to fail on some systems: === ./src/test/regress/regression.diffs === diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/psql.out /tmp/cirrus-ci-build/src/test/regress/results/psql.out --- /tmp/cirrus-ci-build/src/test/regress/expected/psql.out 2021-01-01 16:05:25.749692000 + +++ /tmp/cirrus-ci-build/src/test/regress/results/psql.out 2021-01-01 16:11:28.525632000 + @@ -5094,8 +5094,8 @@ 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 + public | mtest | integer | timestamp without time zone, timestamp with time zone | func
Re: psql \df choose functions by their arguments
Attached is the latest patch against HEAD - basically fixes a few typos. Cheers, Greg v3-psql-df-pick-function-by-type.patch Description: Binary data
Re: psql \df choose functions by their arguments
On Sun, Nov 1, 2020 at 12:05 PM David Christensen wrote: > > I can’t speak for the specific patch, but tab completion of proc args for > \df, \ef and friends has long been a desired feature of mine, particularly > when you are dealing with functions with huge numbers of arguments and the > same name which I have (sadly) come across many times in the wild. > If someone can get this working against this current patch, that would be great, but I suspect it will require some macro-jiggering in tab-complete.c and possibly more, so yeah, could be something to add to the todo list. Cheers, Greg
Re: psql \df choose functions by their arguments
Thanks for looking this over!
> some Abbreviations of common types are not added to the
> type_abbreviations[] Such as:
>
> Int8=> bigint
>
I wasn't aiming to provide a canonical list, as I personally have never
seen anyone use int8 instead of bigint when (for example) creating a
function, but I'm not strongly opposed to expanding the list.
Single array seems difficult to handle it, may be we can use double array
> or use a struct.
>
I think the single works out okay, as this is a simple write-once variable
that is not likely to get updated often.
> And I think It's better to update '/?' info about '\df[+]' in function
> slashUsage(unsigned short int pager).
>
Suggestions welcome, but it's already pretty tight in there, so I couldn't
think of anything:
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[+] [PATTERN] list text search
configurations\n"));
The \df option is already our longest one, even with the silly attempt to
shorten PATTERN :)
Cheers,
Greg
RE: psql \df choose functions by their arguments
Hi (sorry forget to cc the hacklist) > Improve psql \df to choose functions by their arguments I think this is useful. I found some comments in the patch. 1. > * Abbreviations of common types is permitted (because who really likes > to type out "character varying"?), so the above could also be written as: some Abbreviations of common types are not added to the type_abbreviations[] Such as: Int8=> bigint Int2=> smallint Int4 ,int => integer Float4 => real Float8,float,double => double precision (as same as array type) Single array seems difficult to handle it, may be we can use double array or use a struct. 2. And I think It's better to update '/?' info about '\df[+]' in function slashUsage(unsigned short int pager). Best regards, houzj
Re: psql \df choose functions by their arguments
> * Removed the tab-complete bit, it was too fragile and unhelpful I can’t speak for the specific patch, but tab completion of proc args for \df, \ef and friends has long been a desired feature of mine, particularly when you are dealing with functions with huge numbers of arguments and the same name which I have (sadly) come across many times in the wild. Removing this because it was brittle is fine, but would be good to see if we could figure out a way to have this kind of feature in psql IMHO. Best, David
Re: psql \df choose functions by their arguments
Thanks for the feedback, attached is version two of the patch. Major
changes:
* Use booleans not generic "int x"
* Build a quick list of abbreviations at the top of the function
* Add array mapping for all types
* Removed the tab-complete bit, it was too fragile and unhelpful
Cheers,
Greg
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 221a967bfe..cf3e8c7134 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1574,7 +1574,7 @@ testdb=
-\df[anptwS+] [ pattern ]
+\df[anptwS+] [ pattern ] [ types ]
@@ -1587,6 +1587,7 @@ testdb=
If pattern 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 S
modifier to include system objects.
@@ -1598,7 +1599,7 @@ testdb=
-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
\df output.
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index c7a83d5dfc..426603b0cb 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([2], pattern, show_verbose, show_system);
+ funcargs = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
+ success = describeFunctions([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 07d640021c..a8d3f3ba53 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,67 @@ 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)
+ {
+
+ bool is_initial_run = true;
+ bool found_abbreviation;
+ int argoffset = 0;
+ char *functoken;
+
+ static const char *type_abbreviations[] = {
+ "bool", "boolean", "bool[]", "boolean[]",
+ "char", "character", "char[]", "character[]",
+ "double", "double precision", "double[]", "double precision[]",
+ "float", "double precision", "float[]", "double precision[]",
+ "int", "integer", "int[]", "integer[]",
+ "time", "time without time zone", "time[]", "time without time zone[]",
+ "timetz", "time with time zone", "timetz[]", "time with time zone[]",
+ "timestamp", "timestamp without timestamp zone", "timestamp[]", "timestamp without timestamp zone[]",
+ "timestamptz", "timestamp with timestamp zone", "timestamptz[]", "timestamp with timestamp zone[]",
+ "varbit", "bit varying", "varbit[]", "bit varying[]",
+ "varchar", "character varying", "varchar[]", "character varying[]",
+ NULL
+ };
+
+ while ((functoken = strtokx(is_initial_run ? funcargs : NULL, " \t\n\r", ".,();", "\"", 0, false, true, pset.encoding)))
+ {
+ is_initial_run = false;
+ found_abbreviation = false;
+
+ if (isalpha(functoken[0]))
+ {
+appendPQExpBuffer(, " AND p.proargtypes[%d]::regtype::text = ", argoffset++);
+for (int i = 0; NULL != *(type_abbreviations + i); i += 2)
+{
+ const char *shortname = *(type_abbreviations + i);
+ const char *longname = *(type_abbreviations + i + 1);
+
+ if (pg_strcasecmp(functoken, shortname) == 0)
+ {
+ appendPQExpBuffer(, "LOWER('%s')::text\n", longname);
+ found_abbreviation = true;
+ break;
+ }
+}
+if (!found_abbreviation)
+{
+
Re: psql \df choose functions by their arguments
Thank you for looking this over. > This isn't working for arrays: > ... > postgres=# \df aa aa int[] > Arrays should work as expected, I think you have one too many "aa" in there? > I think it should use the same syntax as \sf and \ef, which require > parenthesis > and commas, not spaces. > Hmm, that will not allow partial matches if we require a closing parens. Right now both commas and parens are accepted, but optional. > I think x is just used as "initial", so I think you should make it boolean > and > then set is_initial = false, or similar. > Good suggestion, it is done. > + > pg_strcasecmp(functoken, "bool") == 0 ? "'boolean'" > > I think writing this all within a call to appendPQExpBuffer() is excessive. > You can make an array or structure to search through and then append the > result > to the buffer. > Hmm, like a custom struct we loop through? I will look into implementing that and submit a new patch. Cheers, Greg
Re: psql \df choose functions by their arguments
On Thu, Oct 15, 2020 at 01:21:06PM -0400, Greg Sabino Mullane wrote: > Improve psql \df to choose functions by their arguments I think this is a good idea. This isn't working for arrays: postgres=# \df aa public | aa | integer | integer, integer| func public | aa | integer | integer, integer, integer | func public | aa | integer | integer[], integer, integer | func postgres=# \df aa aa int[] I think it should use the same syntax as \sf and \ef, which require parenthesis and commas, not spaces. int x = 0 while ((functoken = strtokx(x++ ? NULL : funcargs, " \t\n\r", ".,();", "\"", 0, false, true, pset.encoding))) I think x is just used as "initial", so I think you should make it boolean and then set is_initial = false, or similar. + pg_strcasecmp(functoken, "bool") == 0 ? "'boolean'" I think writing this all within a call to appendPQExpBuffer() is excessive. You can make an array or structure to search through and then append the result to the buffer. -- Justin
psql \df choose functions by their arguments
-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. 6ab7a45d541f2c31c5631b811f14081bf7b22271 v1-psql-df-pick-function-by-type.patch - -- Greg Sabino Mullane PGP Key: 0x14964AC8 202010151316 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iF0EAREDAB0WIQQlKd9quPeUB+lERbS8m5BnFJZKyAUCX4iENQAKCRC8m5BnFJZK yIUKAKDiv1E9KgXuSO7lE9p+ttFdk02O2ACg44lu9VdKt3IggIrPiXBPKR8C85M= =QPSd -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= -\df[anptwS+] [ pattern ] +\df[anptwS+] [ pattern ] [ types ] @@ -1561,6 +1561,7 @@ testdb= If pattern 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-c
WIP psql \df choose functions by their arguments
-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= -\df[anptwS+] [ pattern ] +\df[anptwS+] [ pattern ] [ types ] @@ -1561,6 +1561,7 @@ testdb= If pattern 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 S modi
