On Tue, 30 Jun 2026 at 14:22, Florin Irion <[email protected]> wrote: > > 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. >
It needed a rebase. Also, the patch didn't apply directly, I fixed the offset and there was a corruption in the file I couldn't fix. $ git am ~/Downloads/v1-0001-Add-function-name-tab-completion-after-SELECT-in-.patch Applying: Add function name tab completion after SELECT in psql error: corrupt patch at line 30 Patch failed at line 30. In the end, after I fixed the offset, I applied the commit by hand, committed the change and squashed it into a single commit to produce the patch again. Let me know please if you find any errors. I'll check into the code now, this was my first attempt to only rebase into latest changes and make cfboot hopefully happy. > > Cheers, > > Florin Irion > https://www.enterprisedb.com -- Mario Gonzalez EDB: https://www.enterprisedb.com
From 412e542f793396d3523723d0b65681af254e1adb Mon Sep 17 00:00:00 2001 From: Florin Irion <[email protected]> Date: Thu, 12 Feb 2026 12:27:29 +0400 Subject: [PATCH v2] 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 7c05afd4719..0c520f03e0c 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -5231,6 +5231,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 64e27ef87a3..ea36deb7a7a 100644 --- a/src/bin/psql/t/010_tab_completion.pl +++ b/src/bin/psql/t/010_tab_completion.pl @@ -45,7 +45,8 @@ $node->safe_psql('postgres', . "CREATE TABLE \"mixedName\" (f1 int, f2 text);\n" . "CREATE TYPE enum1 AS ENUM ('foo', 'bar', 'baz', 'BLACK');\n" . "CREATE PUBLICATION some_publication;\n" - . "CREATE TABLE fpo_test (id int4range, valid_at daterange, name text);\n" + . "CREATE TABLE fpo_test (id int4range, valid_at daterange, name text;\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 @@ -460,6 +461,22 @@ check_completion("FR\t", qr/FROM /, clear_query(); +# 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 289dd12636d..4c36e76e085 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -647,12 +647,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, } }; @@ -664,6 +666,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 */ @@ -676,6 +679,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 = { @@ -5587,6 +5591,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] */ /* @@ -6165,6 +6175,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.47.3
