psql tab completion for SELECT

In his blog entry http://www.depesz.com/2011/07/08/wish-list-for-psql/
depesz described a simple way to do tab completion for SELECT in psql:

"""
Make tab-completion complete also function names – like: SELECT
pg_get<tab><tab> to see all functions that start with pg_get.

Make tab-completion work for columns in SELECT. I know that when writing
SELECT clause, psql doesn’t know which table it will deal with, but it
could search through all the columns in database.
"""

That seems pretty useful, and it's more or less a one-line change, as in
the attached patch.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Make tab-completion complete also function names – like: SELECT
> pg_get<tab><tab> to see all functions that start with pg_get.
>
> Make tab-completion work for columns in SELECT. I know that when writing
> SELECT clause, psql doesn’t know which table it will deal with, but it
> could search through all the columns in database.
>
> That seems pretty useful, and it's more or less a one-line change, as in
> the attached patch.

Does that includes support for completing SRF functions in the FROM clause?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> That seems pretty useful, and it's more or less a one-line change, as in
> the attached patch.

That seems pretty nearly entirely bogus. What is the argument for
supposing that the word right after SELECT is a function name? I would
think it would be a column name (from who-knows-what table) much more
often. Also, if it is useful, people will expect it to work in more
places than just the first word after SELECT --- for instance, somebody
who didn't realize what a kluge it was would expect it to also work
right after a top-level comma after SELECT. Or probably after a left
parenthesis in the SELECT list. Etc.

regards, tom lane

On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> That seems pretty useful, and it's more or less a one-line change, as in
>> the attached patch.
>
> That seems pretty nearly entirely bogus.  What is the argument for
> supposing that the word right after SELECT is a function name?  I would
> think it would be a column name (from who-knows-what table) much more
> often.

It isn't necessarily, but it might be. It'd certainly be nice to type:

SELECT pg_si<TAB>

and get:

SELECT pg_size_pretty(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/2/10 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> That seems pretty useful, and it's more or less a one-line change, as in
>>> the attached patch.
>>
>> That seems pretty nearly entirely bogus.  What is the argument for
>> supposing that the word right after SELECT is a function name?  I would
>> think it would be a column name (from who-knows-what table) much more
>> often.
>
> It isn't necessarily, but it might be.  It'd certainly be nice to type:
>
> SELECT pg_si<TAB>
>
> and get:
>
> SELECT pg_size_pretty(

+1

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On 10/02/12 08:50, Robert Haas wrote:
> On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> >> That seems pretty useful, and it's more or less a one-line change, as in
> >> the attached patch.
> >
> > That seems pretty nearly entirely bogus.  What is the argument for
> > supposing that the word right after SELECT is a function name?  I would
> > think it would be a column name (from who-knows-what table) much more
> > often.
>
> It isn't necessarily, but it might be. It'd certainly be nice to type:
>
> SELECT pg_si<TAB>
>
> and get:
>
> SELECT pg_size_pretty(
>

Well the important problem in completion is how the dictionary of
possible terms is determined and how much context info is used to
reduce / enhance that dictionary.

case 1:

select x<TAB>

case 2:

select x<TAB> from bar

Possible dictionaries in case 1:

1.1 complete nothing
1.2 complete assuming the union of all columns from all tables
in the search_path as the dictionary.
1.3 complete assuming the union of all function names in the
search_path as the dictionary
1.4 complete assuming 1.2 and 1.3

Possible dictionaries in case 2:

2.1 treat it like case 1
2.2 complete assuming the union of all columns from bar
in the search_path as the dictionary
2.3 2.2 + 1.3

Now these are heuristics and the decision becomes a question
of usefulness vs amount of time it costs to implement and
possibly how expensive computing the dictionary is.

I personally would like 1.3 in case 1 and 2.3 in case 2,
because I expect 1.2 to be to show too many possible
completions to be useful. But even 1.3 in both cases wouldn't
be that bad.

Cheers,

Bene

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> That seems pretty nearly entirely bogus. What is the argument for
>> supposing that the word right after SELECT is a function name?

> It isn't necessarily, but it might be. It'd certainly be nice to type:
> SELECT pg_si<TAB>
> and get:
> SELECT pg_size_pretty(

Yeah, and then you'll type

SELECT pg_size_pretty(pg_dat<TAB>

and get nothing, and curse the authors of such a misbegotten incomplete
concept that leads your fingers to rely on something that doesn't work
where it should.

I'm not against tab-completing functions, if people think that's
useful. I am against tab-completing them in 1% of use-cases, which is
what this patch accomplishes. The fact that it's short doesn't make it
good.

regards, tom lane

On Fri, Feb 10, 2012 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Feb 10, 2012 at 1:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> That seems pretty nearly entirely bogus.  What is the argument for
>>> supposing that the word right after SELECT is a function name?
>
>> It isn't necessarily, but it might be.  It'd certainly be nice to type:
>> SELECT pg_si<TAB>
>> and get:
>> SELECT pg_size_pretty(
>
> Yeah, and then you'll type
>
>        SELECT pg_size_pretty(pg_dat<TAB>
>
> and get nothing, and curse the authors of such a misbegotten incomplete
> concept that leads your fingers to rely on something that doesn't work
> where it should.
>
> I'm not against tab-completing functions, if people think that's
> useful.  I am against tab-completing them in 1% of use-cases, which is
> what this patch accomplishes.  The fact that it's short doesn't make it
> good.

Our tab completion is in general very incomplete; we have made a
practice of cherry-picking the most commonly encountered cases and
handling only those. Whether or not that is a good policy is a
philosophical question, but there is no reason to hold this particular
patch to a higher standard than the quality of our tab completion code
in general.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 10, 2012 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm not against tab-completing functions, if people think that's
>> useful. I am against tab-completing them in 1% of use-cases, which is
>> what this patch accomplishes. The fact that it's short doesn't make it
>> good.

> Our tab completion is in general very incomplete; we have made a
> practice of cherry-picking the most commonly encountered cases and
> handling only those. Whether or not that is a good policy is a
> philosophical question, but there is no reason to hold this particular
> patch to a higher standard than the quality of our tab completion code
> in general.

Well, if you want a patch with low standards, what about tab-completing
function names anywhere that we do not see context suggesting something
else? I really think that doing it only immediately after SELECT is
going to prove far more of an annoyance than a help, because once you
get used to relying on it you are going to wish it worked elsewhere.

regards, tom lane

On Fri, Feb 10, 2012 at 11:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Feb 10, 2012 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I'm not against tab-completing functions, if people think that's
>>> useful.  I am against tab-completing them in 1% of use-cases, which is
>>> what this patch accomplishes.  The fact that it's short doesn't make it
>>> good.
>
>> Our tab completion is in general very incomplete; we have made a
>> practice of cherry-picking the most commonly encountered cases and
>> handling only those.  Whether or not that is a good policy is a
>> philosophical question, but there is no reason to hold this particular
>> patch to a higher standard than the quality of our tab completion code
>> in general.
>
> Well, if you want a patch with low standards, what about tab-completing
> function names anywhere that we do not see context suggesting something
> else?  I really think that doing it only immediately after SELECT is
> going to prove far more of an annoyance than a help, because once you
> get used to relying on it you are going to wish it worked elsewhere.

I think that without a bit more contextual information that's likely
to lead to some odd results. Unimplemented completions will lead to
bizarre things happening.

One thing that's been bugging me for a while is that the tab
completion code all works by looking backward up to n words. What we
really want to know is what kind of statement we're in and where we
are in it. Absent other information, if we're in the target list of a
SELECT statement (nested arbitrarily) that behavior is reasonable. If
we're someplace in a GRANT statement, or someplace in a CREATE
STATEMENT where, say, a column name is expected, it's really not.

Unfortunately, making the tab completion something other than
incredibly stupid is likely to be an insane amount of work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 10, 2012 at 11:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, if you want a patch with low standards, what about tab-completing
>> function names anywhere that we do not see context suggesting something
>> else?

> I think that without a bit more contextual information that's likely
> to lead to some odd results. Unimplemented completions will lead to
> bizarre things happening.

True. I was first thinking of doing this only if we know we're in
a DML query, ie *first* word on the line is
WITH/SELECT/INSERT/UPDATE/DELETE. However, in the current
implementation that is not terribly workable because we are only looking
at the current line of text, not the whole input buffer; so making such
a restriction would disable completion after the first line of a multi-
line command.

> One thing that's been bugging me for a while is that the tab
> completion code all works by looking backward up to n words.

Yup. At the very least it would be good if it had access to the entire
current command, so that we could sanity-check on the basis of the first
word.

regards, tom lane

On Fri, Feb 10, 2012 at 11:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Feb 10, 2012 at 11:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Well, if you want a patch with low standards, what about tab-completing
>>> function names anywhere that we do not see context suggesting something
>>> else?
>
>> I think that without a bit more contextual information that's likely
>> to lead to some odd results.  Unimplemented completions will lead to
>> bizarre things happening.
>
> True.  I was first thinking of doing this only if we know we're in
> a DML query, ie *first* word on the line is
> WITH/SELECT/INSERT/UPDATE/DELETE.  However, in the current
> implementation that is not terribly workable because we are only looking
> at the current line of text, not the whole input buffer; so making such
> a restriction would disable completion after the first line of a multi-
> line command.
>
>> One thing that's been bugging me for a while is that the tab
>> completion code all works by looking backward up to n words.
>
> Yup.  At the very least it would be good if it had access to the entire
> current command, so that we could sanity-check on the basis of the first
> word.

Agreed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Robert Haas wrote:

> One thing that's been bugging me for a while is that the tab
> completion code all works by looking backward up to n words. What we
> really want to know is what kind of statement we're in and where we
> are in it. Absent other information, if we're in the target list of a
> SELECT statement (nested arbitrarily) that behavior is reasonable. If
> we're someplace in a GRANT statement, or someplace in a CREATE
> STATEMENT where, say, a column name is expected, it's really not.

I played with this years ago, but readline does not really offer a
good way to easily get what we want (the whole statement, chunked into
nice bits to analyze). Of course at this point we should think about
making things more generic so we can drop in whatever readline-alternative
comes along in the future.

> Unfortunately, making the tab completion something other than
> incredibly stupid is likely to be an insane amount of work.

Insane amount of work? Check. Inredibly stupid? No, I think we've done
pretty good given the limitations we have. You want to see incredibly
stupid, see some of the *other* CLIs out there (hi, mysql! :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201202101157
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk81TI4ACgkQvJuQZxSWSsivRQCfcze1WMq81rE+mtrOReHBQ6eV
SzEAn2JySDAoCokFkY/gtz//GqolVVm5
=d2LG
-----END PGP SIGNATURE-----

On tor, 2012-02-09 at 23:02 +0100, Dimitri Fontaine wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Make tab-completion complete also function names – like: SELECT
> > pg_get<tab><tab> to see all functions that start with pg_get.
> >
> > Make tab-completion work for columns in SELECT. I know that when writing
> > SELECT clause, psql doesn’t know which table it will deal with, but it
> > could search through all the columns in database.
> >
> > That seems pretty useful, and it's more or less a one-line change, as in
> > the attached patch.
>
> Does that includes support for completing SRF functions in the FROM clause?

No, that's an entirely different issue.

On fre, 2012-02-10 at 01:24 -0500, Tom Lane wrote:
> That seems pretty nearly entirely bogus. What is the argument for
> supposing that the word right after SELECT is a function name? I would
> think it would be a column name (from who-knows-what table) much more
> often.

That's what the patch does. It looks like you misread what I wrote.

> Also, if it is useful, people will expect it to work in more
> places than just the first word after SELECT --- for instance, somebody
> who didn't realize what a kluge it was would expect it to also work
> right after a top-level comma after SELECT. Or probably after a left
> parenthesis in the SELECT list. Etc.

All of psql tab completion works like that. We only complete the first
table after FROM, the first column after ORDER BY, the first privilege
to grant, the first role to grant to, the first table to drop, the first
reloption, etc.