Thread: PSQL/pgAdmin - Column Completion

I have been having issues with autocomplete in pgAdmin. After some searching,
I found it was my mistake and that pgAdmin doesn't actually support column
autocompletion in select statements. I found that pgAdmin uses the
autocomplete code directly from psql's tab-complete.c, which contains these
comments in the current version starting on line 3387:

/* SELECT */
   /* naah . . . */

Is there any talk of this functionality being added in the future? I find it
a bit disappointing that other applications such as HeidiSQL can
autocomplete column names from tables/aliases/etc, but pgAdmin cannot. I
would definitely rather use pgAdmin for query building, but I never realized
how much I use column completion until I switched to Postgres for a new
project.



--
View this message in context: http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

dvlsg <dave@clubspeed.com> writes:
> I have been having issues with autocomplete in pgAdmin. After some searching,
> I found it was my mistake and that pgAdmin doesn't actually support column
> autocompletion in select statements. I found that pgAdmin uses the
> autocomplete code directly from psql's tab-complete.c, which contains these
> comments in the current version starting on line 3387:

> /* SELECT */
>    /* naah . . . */

> Is there any talk of this functionality being added in the future?

No.  What would the autocompletion be based on?  You don't know any
referenced table names at that point, for instance.

> I find it
> a bit disappointing that other applications such as HeidiSQL can
> autocomplete column names from tables/aliases/etc, but pgAdmin cannot.

The only algorithm I can imagine using would be to autocomplete every
column and function name existing in the database.  While this might be
helpful in toy databases, it's hard to imagine it being very convenient,
or performant, in real-world situations.

            regards, tom lane

dvlsg <dave@clubspeed.com> writes:

I have been having issues with autocomplete in pgAdmin. After some searching,
I found it was my mistake and that pgAdmin doesn't actually support column
autocompletion in select statements. I found that pgAdmin uses the
autocomplete code directly from psql's tab-complete.c, which contains these
comments in the current version starting on line 3387:

/* SELECT */  /* naah . . . */

Is there any talk of this functionality being added in the future?

No.  What would the autocompletion be based on?  You don't know any
referenced table names at that point, for instance.

I find it
a bit disappointing that other applications such as HeidiSQL can
autocomplete column names from tables/aliases/etc, but pgAdmin cannot.

The only algorithm I can imagine using would be to autocomplete every
column and function name existing in the database.  While this might be
helpful in toy databases, it's hard to imagine it being very convenient,
or performant, in real-world situations.
		regards, tom lane

I think I see the autocompleters lining up now:
    just "my" schemas

    I'll type the from clause first

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 12/31/2014 11:56 AM, Rob Sargent wrote:

I think I see the autocompleters lining up now:
    just "my" schemas

we have single schemas with 100s of tables.

    I'll type the from clause first

but, the sql command is SELECT field,field FROM tables ...     so how do you type FROM before you type the fields ?   and what about joins, is it supposed to parse and decode join table aliases, too?

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

The majority of my query writing is done in pgAdmin, not psql. I do tend to
type out the from/where/join/whatever portion of the statement before
finishing the select portion of the statement (starting with a SELECT * and
replacing it once the rest of the query is in place). Fair enough, though --
I am probably in the minority there.

However, what about a statement like this?

SELECT *
FROM public.TableA a
WHERE a.Column1 > 50;

Simplified again, of course, but the autocomplete of a column would come
after a table/alias declaration. As to the joins, other applications I have
used are capable of autocompleting the columns in the ON portion in the
following query.

SELECT *
FROM public.table_a a
INNER JOIN public.table_b b
  ON a.table_a_id = b.table_a_id

I'm not saying it's a deal breaker, or the end of the world, or anything
like that. It seems like that sort of query parsing is considerably outside
the functionality of the existing autocomplete code, and may not be worth
the extra work / overhaul. I will concede that it's a minor inconvenience
for only a portion of the userbase at worst. Just voicing a curiosity.



--
View this message in context: http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5832597.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

dvlsg <dave@clubspeed.com> writes:
> The majority of my query writing is done in pgAdmin, not psql. I do tend to
> type out the from/where/join/whatever portion of the statement before
> finishing the select portion of the statement (starting with a SELECT * and
> replacing it once the rest of the query is in place). Fair enough, though --
> I am probably in the minority there.

Hm.  You're right that pgAdmin could be smarter than plain psql, since
it allows nonsequential typing of a command.  Whether this would be worth
pgAdmin having its own implementation of tab completion is something you'd
have to take up on the pgAdmin lists.

> However, what about a statement like this?

> SELECT *
> FROM public.TableA a
> WHERE a.Column1 > 50;

The big picture there is that psql's tab completion doesn't contain a full
blown parser but just looks at a couple of words of preceding context.
To be able to do anything with a nontrivial FROM clause would seem to
require adding a whole heck of a lot of machinery.  If you're sufficiently
excited about it to work on the project, feel free.  I've thought for some
time that tab-complete.c is overdue for being thrown away and rewritten
from scratch; but it would be a *lot* of work to make it significantly
better, and I doubt anybody is thinking about doing that.

            regards, tom lane

True - I placed this message in general, because all the history I could find
with the pgAdmin mailing lists suggests that they /really/ don't want to
break away from the psql tab completion code and run their own. Those
messages are a few years old, however - maybe things have changed.

I agree about the additional machinery - my first glance at it looked like a
ton of additional pieces to get that sort of parsing functionality. I won't
have any time in the near future to take a stab at it, unfortunately. I'm
also not sure how much benefit a psql command line user would get out of a
full query parser for autocompletion. Probably not much, if anything. Feels
more like GUI functionality to me.

Thanks for taking the time to respond, though. I appreciate it.



--
View this message in context: http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5832599.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 12/31/2014 03:12 PM, dvlsg wrote:
> True - I placed this message in general, because all the history I could find
> with the pgAdmin mailing lists suggests that they /really/ don't want to
> break away from the psql tab completion code and run their own. Those
> messages are a few years old, however - maybe things have changed.

Looks like pgAdmin is in the process of a complete rewrite:

http://pgsnake.blogspot.com/

Might be a good time to get your request in.


--
Adrian Klaver
adrian.klaver@aklaver.com

Oh perfect. Thanks for the heads up! I'll be sure to voice my request one way
or another during their design phase.



--
View this message in context: http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5832603.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Yeah, I think that would be problematic. All other sql query applications I've used in the past with autocomplete seem to parse the entire query and find table references for autocompletion based on the whole query, whereas psql seems to typically base autocomplete off of the current cursor location and strings behind it without any lookaheads (as far as I can tell -- I only took a brief look, and it's been a long time since I've had to write C code).

 

I suppose what I would really be looking for is something like the vastly simplified query below, where pressing Ctrl+Space after each "a." would provide a list of columns only for the table alias.

 

SELECT

  a.Column1,

  a.Column2,

  a.Column3

FROM

  public.a_table_for_querying a

 

It looks like that would take a fairly significant bit of work/overhaul to make the autocomplete parser look at the entire query to find a list of used tables, and then find the list of available columns based on that, if I'm reading the code correctly.

 

I agree with that last sentiment, though -- I don't think a list of every column from every table would be very helpful. 

 

 

 

Dave Lesage

Club Speed, Inc.

Software Engineer

4740 Green River Road, Suite #308

Corona, CA 92880

 

Email: [hidden email]

24 Hour Support: (877) 751-3200

International Support: (951) 817-7073

This e-mail is covered by the Electronic Communications Privacy Act, 18 USC §§ 2510-2521 and is legally privileged. This communication and any accompanying document(s) are confidential and privileged. They are intended for the sole use of the addressee. If you receive this transmission in error, you are advised that any disclosure, copying, distribution, or the taking of any action in reliance upon the communication is strictly prohibited. If you have received this communication in error, please contact Club Speed, Inc. at (877) 751-3200.

 

 

---

View this message in context: Re: PSQL/pgAdmin - Column Completion

Sent from the PostgreSQL - general mailing list archive at Nabble.com.

That looks perfect. I'll look into it, and pass the link along to the pgAdmin
developers. I did bring up the suggestion to Dave Page who was kind enough
to take the auto complete request under consideration for the future when
they get to later stages of the product.



--
View this message in context: http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5833448.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.