Hello,
A big part of my job is creating simple select statements with a few joins
in them. Unfortunately, the existing autocomplete functionality of pgadmin
was a bit too limited for my use case. A relatively simple query like:
select * from person p join company c on p.company_id = c.id where p.name =
'John' would be enough to completely throw the autocompletion off.
So I started working on a patch to improve the autocompletion a bit and the
result is attached. It doesn't have all the features I would like it to
have (no support for table aliases, for example) but it is already a big
improvement for my use case.
Please comment if I need to change anything to get this patch into pgadmin.
This patch is against pgadmin3-1.14.0.tar.gz
Regards,
Wander Winkelhorst.
diff -U2 -a -r ./old/pgadmin/utils/tab-complete.inc ./new//pgadmin/utils/tab-complete.inc
--- ./old/pgadmin/utils/tab-complete.inc 2011-06-20 21:13:22.000000000 +0200
+++ ./new//pgadmin/utils/tab-complete.inc 2011-10-31 14:41:51.022693279 +0100
@@ -229,4 +229,14 @@
" AND pg_catalog.pg_table_is_visible(c.oid)"
+#define Query_for_list_of_attributes_list \
+"SELECT pg_catalog.quote_ident(attname) "\
+" FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c "\
+" WHERE c.oid = a.attrelid "\
+" AND a.attnum > 0 "\
+" AND NOT a.attisdropped "\
+" AND substring(pg_catalog.quote_ident(attname),1,%d)='%s' "\
+" AND pg_catalog.pg_table_is_visible(c.oid)"\
+" AND pg_catalog.quote_ident(relname) in ("
+
#define Query_for_list_of_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
@@ -361,4 +371,22 @@
};
+/*
+A function to get all attributes of a list of tables
+Used for autocompletion.
+*/
+static char * complete_with_attr_for_list(char *tablelist, char *text, void *dbptr)
+{
+ char* attrquery;
+ char* result;
+
+ attrquery = malloc(strlen(Query_for_list_of_attributes_list) + strlen(tablelist) + 1);
+ strcpy(attrquery, Query_for_list_of_attributes_list);
+ strcat(attrquery, tablelist);
+ result = complete_from_query(text, attrquery, NULL, dbptr);
+
+ free(attrquery);
+
+ return result;
+}
/* The completion function. Acc. to readline spec this gets passed the text
@@ -401,4 +429,59 @@
(void) end; /* not used */
+ char *buffer;
+ char *tablelist;
+ char *scratch;
+
+ buffer = malloc(strlen(rl_line_buffer));
+ tablelist = malloc(strlen(rl_line_buffer) + 20); //+20 is for extra chars like quotes and comma's
+ strcpy (tablelist,"");
+ strcpy(buffer, rl_line_buffer);
+ #define START 1
+ #define TABLE 2
+ #define TABLEALIAS 3
+ int grammarState = START;
+ char *p = strtok(buffer, " ");
+ while (p) {
+ if (grammarState == TABLE) {
+ //Add tablename to list of tables
+ strcat(tablelist,"'");
+ scratch = malloc(strlen(p) * 2 + 1);
+ PQescapeString(scratch, p, strlen(p));
+ strcat(tablelist, scratch);
+ free(scratch);
+ strcat(tablelist,"', ");
+
+ if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "WHERE") == 0) {
+ grammarState = START;
+ } else {
+ //Must be alias :)
+ grammarState = TABLEALIAS;
+ }
+ } else if (grammarState == TABLEALIAS) {
+ if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "WHERE") == 0) {
+ grammarState = START;
+ } else {
+ //Must be alias :)
+ grammarState = TABLEALIAS;
+ }
+ } else {
+ if (pg_strcasecmp(p, "FROM") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ }
+ }
+
+ p = strtok(NULL, " ");
+ }
+
+ free(buffer);
+
+ strcat(tablelist,"' ')"); //Add dummy to mark end-of-list
+
#ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
rl_completion_append_character = ' ';
@@ -1356,5 +1439,5 @@
pg_strcasecmp(prev2_wd, "ORDER") == 0 &&
pg_strcasecmp(prev_wd, "BY") == 0)
- COMPLETE_WITH_ATTR(prev3_wd);
+ matches = complete_with_attr_for_list(tablelist, text, dbptr);
/* PREPARE xx AS */
@@ -1621,5 +1704,5 @@
/* Simple case of the word before the where being the table name */
else if (pg_strcasecmp(prev_wd, "WHERE") == 0)
- COMPLETE_WITH_ATTR(prev2_wd);
+ matches = complete_with_attr_for_list(tablelist, text, dbptr);
/* ... FROM ... */
@@ -1630,4 +1713,21 @@
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL);
+/* ... JOIN ... */
+ else if (pg_strcasecmp(prev_wd, "JOIN") == 0 &&
+ pg_strcasecmp(prev3_wd, "COPY") != 0 &&
+ pg_strcasecmp(prev3_wd, "\\copy") != 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL);
+
+/* ... ON ... */
+ else if (pg_strcasecmp(prev_wd, "ON") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr);
+
+/* ... = ... */
+ else if (pg_strcasecmp(prev_wd, "=") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr);
+
+/* ... AND ... */
+ else if (pg_strcasecmp(prev_wd, "AND") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr);
/* Backslash commands */
@@ -1731,4 +1831,5 @@
free(prev4_wd);
free(prev5_wd);
+ free(tablelist);
/* Return our Grand List O' Matches */
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
