Hello,I think it would be nice to have tab completion for user functions after `SELECT`, and also for `pg_` in some cases. There was already a discussion around this here https://www.postgresql.org/message-id/CACLU5mQBYaMgGzJ-ZoG4MrBuXLsREcQbiY4pqsnYjMaMQP6hQg%40mail.gmail.com , however as that discussion is ~ 1 year old I thought it's better to start a new one.
Attaching a patch, keeping in mind some doubts that were expressed in the old discussion.
Cheers, Florin Irion https://www.enterprisedb.com
From eee63a47fee041f3eb1be1944eed2499539e9cd6 Mon Sep 17 00:00:00 2001 From: Florin Irion <[email protected]> Date: Thu, 12 Feb 2026 12:27:29 +0400 Subject: [PATCH v1] Add function name tab completion after SELECT in psql Teach psql's tab completion to sugest function names after SELECT, teh keywords ALL, DISTINCT, and * are also included since they are the most common non-function tokens that follow SELECT. To keep the completion list manageable, pg_catalog functions are not checked unless the text typed so far begins with "pg_". Also add DISTINCT to the function query results to avoid duplicate entries from overloaded functions. Update the documentation to describe the new completion behavior, and add TAP tests covering both user-defined function completion and pg_catalog prefix filtering. --- doc/src/sgml/ref/psql-ref.sgml | 7 +++++++ src/bin/psql/t/010_tab_completion.pl | 19 ++++++++++++++++++- src/bin/psql/tab-complete.in.c | 24 ++++++++++++++++++++++++ 3 files changed, 49 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8b1d948ba05..603702dba8f 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -5227,6 +5227,13 @@ testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# ' will fill in the unfinished name, or offer a menu of possible completions when there's more than one. (Depending on the library in use, you may need to press <literal>TAB</literal> more than once to get a menu.) + Tab completition is also aware of SQL context: for instance, after + <command>SELECT</command> it will suggest function names (as well as + keywords such as <literal>ALL</literal> and <literal>DISTINCT</literal>), + while after <command>FROM</command> it will suggest table names. + To avoid overwhelming the list with built-in functions, names from + <literal>pg_catalog</literal> are not checked unless the text typed so + far begins with <literal>pg_</literal>. </para> <para> diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl index 7104aba2394..fcdc72b4720 100644 --- a/src/bin/psql/t/010_tab_completion.pl +++ b/src/bin/psql/t/010_tab_completion.pl @@ -44,7 +44,8 @@ $node->safe_psql('postgres', . "CREATE TABLE mytab246 (f1 int, f2 text);\n" . "CREATE TABLE \"mixedName\" (f1 int, f2 text);\n" . "CREATE TYPE enum1 AS ENUM ('foo', 'bar', 'baz', 'BLACK');\n" - . "CREATE PUBLICATION some_publication;\n"); + . "CREATE PUBLICATION some_publication;\n" + . "CREATE FUNCTION tcfunc_test(int) RETURNS int LANGUAGE sql AS 'SELECT 1';\n"); # In a VPATH build, we'll be started in the source directory, but we want # to run in the build directory so that we can use relative paths to @@ -423,6 +424,22 @@ check_completion( clear_line(); +# check completion of function names after SELECT +check_completion( + "SELECT tcfunc_te\t", + qr/tcfunc_test\b/, + "complete function name after SELECT"); + +clear_query(); + +# check that pg_ prefix enables system function completion +check_completion( + "SELECT pg_cancel_b\t", + qr/pg_cancel_backend\b/, + "complete pg_catalog function with pg_ prefix in SELECT"); + +clear_query(); + # send psql an explicit \q to shut it down, else pty won't close properly $h->quit or die "psql returned $?"; diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 8b91bc00062..afeb050f3bb 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -646,12 +646,14 @@ static const SchemaQuery Query_for_list_of_functions[] = { .viscondition = "pg_catalog.pg_function_is_visible(p.oid)", .namespace = "p.pronamespace", .result = "p.proname", + .use_distinct = true, }, { .catname = "pg_catalog.pg_proc p", .viscondition = "pg_catalog.pg_function_is_visible(p.oid)", .namespace = "p.pronamespace", .result = "p.proname", + .use_distinct = true, } }; @@ -663,6 +665,7 @@ static const SchemaQuery Query_for_list_of_procedures[] = { .viscondition = "pg_catalog.pg_function_is_visible(p.oid)", .namespace = "p.pronamespace", .result = "p.proname", + .use_distinct = true, }, { /* not supported in older versions */ @@ -675,6 +678,7 @@ static const SchemaQuery Query_for_list_of_routines = { .viscondition = "pg_catalog.pg_function_is_visible(p.oid)", .namespace = "p.pronamespace", .result = "p.proname", + .use_distinct = true, }; static const SchemaQuery Query_for_list_of_sequences = { @@ -5401,6 +5405,12 @@ match_previous_words(int pattern_id, COMPLETE_WITH("'standby_replay'", "'standby_write'", "'standby_flush'", "'primary_flush'"); } +/* SELECT -- offer functions and SELECT-list keywords */ + else if (TailMatches("SELECT") && + !HeadMatches("GRANT") && !HeadMatches("REVOKE")) + COMPLETE_WITH_VERSIONED_SCHEMA_QUERY_PLUS(Query_for_list_of_functions, + "ALL", "DISTINCT", "*"); + /* WITH [RECURSIVE] */ /* @@ -5977,6 +5987,20 @@ _complete_from_query(const char *simple_query, " pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"); } + /* + * Similarly, when fetching function names, don't check + * pg_catalog entries unles the input-so-far begins with + * "pg_". + */ + if (strcmp(schema_query->catname, + "pg_catalog.pg_proc p") == 0 && + strncmp(objectname, "pg_", 3) != 0) + { + appendPQExpBufferStr(&query_buffer, + " AND p.pronamespace <> (SELECT oid FROM" + " pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')"); + } + /* * If the target object type can be schema-qualified, add in * schema names matching the input-so-far. -- 2.45.1
