PSQL/pgAdmin - Column Completion

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/31/2014 12:51 PM, Tom Lane wrote:

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
etc

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: dave@clubspeed.com
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.

On Wed, Dec 31, 2014 at 11:51 AM, Tom Lane-2 [via PostgreSQL] <
ml-node+s1045698n5832576h4@n5.nabble.com> wrote:

dvlsg <[hidden email]
<http:///user/SendEmail.jtp?type=node&amp;node=5832576&amp;i=0&gt;&gt; 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

--
Sent via pgsql-general mailing list ([hidden email]
<http:///user/SendEmail.jtp?type=node&amp;node=5832576&amp;i=1&gt;)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

------------------------------
If you reply to this email, your message will be added to the discussion
below:

http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5832576.html
To unsubscribe from PSQL/pgAdmin - Column Completion, click here
<http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&amp;node=5832573&amp;code=ZGF2ZUBjbHVic3BlZWQuY29tfDU4MzI1NzN8LTE2MzE3NzAwNjg=&gt;
.
NAML
<http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&amp;id=instant_html%21nabble%3Aemail.naml&amp;base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&amp;breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml&gt;

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

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

On 12/31/2014 01:55 PM, John R Pierce wrote:

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

Sorry john, I couldn't find the facetious smiley. (i.e. I'm not an
autocompleter)

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Dec 31, 2014, at 12:01 PM, dvlsg wrote:

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.

Just ran across pgcli which does you what you want from the
command line:

https://github.com/amjith/pgcli

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[1]

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

Links:

1. http://postgresql.nabble.com/PSQL-pgAdmin-Column-Completion-tp5832573p5832578.html
2. http://postgresql.nabble.com/PostgreSQL-general-f1843780.html

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general