Обсуждение: psql auto-completion for multiple where clauses

Someone highlighed on IRC that after the first WHERE clause,
autocomplete no longer works.

An example:

CREATE TABLE tab_completion (
id serial,
stuff text,
meow boolean
);

SELECT * FROM tab_completion WHERE id = 2 AND s<tab><tab>

This would output a blank line.

Is there any chance of improving this so it would work for more than 1
WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
all, but seems to be fine for ORDER BY.

While we're at it, how about a smarter auto-complete for
quote-required column names (or whichever term I was supposed to use
just then):

CREATE TABLE tab_completion (
id serial,
stuff text,
"bark bark" boolean
);

SELECT * FROM tab_completion WHERE id = 2 AND b<tab>

Since there is no column beginning with "b", it might be an idea to
get it to match "bark bark" instead.  It might help alleviate what may
be a gotcha for some.

Thanks

Thom

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


> Someone highlighed on IRC that after the first WHERE clause,
> autocomplete no longer works.
...
> SELECT * FROM tab_completion WHERE id = 2 AND s<tab><tab>
...
> Is there any chance of improving this so it would work for more than 1
> WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
> all, but seems to be fine for ORDER BY.

No: there is only a small number of words that we go back through, 
so the above will not work as we cannot get back to the name of the table 
from the right side of the AND. The way to fix that is to redesign our 
tab-completion system such that it knows about a greater number of words, 
perhaps even the complete statement.

> SELECT * FROM tab_completion WHERE id = 2 AND b<tab>
>
> Since there is no column beginning with "b", it might be an idea to
> get it to match "bark bark" instead.  It might help alleviate what may
> be a gotcha for some.

This one is more doable, assuming we are really talking about:

SELECT * FROM tab_completion WHERE b<tab>

Keep in mind it will show up in a list if you do the following:

SELECT * FROM tab_completion WHERE <tab>


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

iEYEAREDAAYFAkxAdOkACgkQvJuQZxSWSsiMpACgvheNYe35eXugYQrR3fZ7AYl2
ZWoAnAwzDPREKuxrJzZK45TpInUCh03w
=E6eG
-----END PGP SIGNATURE-----

On 16 July 2010 16:04, Greg Sabino Mullane <greg@turnstep.com> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> Someone highlighed on IRC that after the first WHERE clause,
>> autocomplete no longer works.
> ...
>> SELECT * FROM tab_completion WHERE id = 2 AND s<tab><tab>
> ...
>> Is there any chance of improving this so it would work for more than 1
>> WHERE clause?  I notice it also doesn't work for GROUP BY or HAVING at
>> all, but seems to be fine for ORDER BY.
>
> No: there is only a small number of words that we go back through,
> so the above will not work as we cannot get back to the name of the table
> from the right side of the AND. The way to fix that is to redesign our
> tab-completion system such that it knows about a greater number of words,
> perhaps even the complete statement.
>

Yay for complete overhauls!  Okay, fair enough.  Bit unintuitive
behaviour though.

>> SELECT * FROM tab_completion WHERE id = 2 AND b<tab>
>>
>> Since there is no column beginning with "b", it might be an idea to
>> get it to match "bark bark" instead.  It might help alleviate what may
>> be a gotcha for some.
>
> This one is more doable, assuming we are really talking about:
>
> SELECT * FROM tab_completion WHERE b<tab>
>
> Keep in mind it will show up in a list if you do the following:
>
> SELECT * FROM tab_completion WHERE <tab>
>
>

Doesn't it do that already?

Thom

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> No: there is only a small number of words that we go back through, 
> so the above will not work as we cannot get back to the name of the table 
> from the right side of the AND. The way to fix that is to redesign our 
> tab-completion system such that it knows about a greater number of words, 
> perhaps even the complete statement.

Yeah, tab_complete.c is really a pretty awful kluge.  One thing that
might be interesting is to make it use psql's lexer, which I think
wasn't there at all when the completion code was started.  But that just
takes care of word-recognition issues, it won't do anything at the
semantic level.  I hesitate to think of trying to incorporate the
backend grammar too :-(
        regards, tom lane