2 * psql - the PostgreSQL interactive terminal
4 * Copyright (c) 2000-2003, PostgreSQL Global Development Group
6 * $Header: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v 1.86 2003/10/14 22:47:12 tgl Exp $
9 /*----------------------------------------------------------------------
10 * This file implements a somewhat more sophisticated readline "TAB
11 * completion" in psql. It is not intended to be AI, to replace
12 * learning SQL, or to relieve you from thinking about what you're
13 * doing. Also it does not always give you all the syntactically legal
14 * completions, only those that are the most common or the ones that
15 * the programmer felt most like implementing.
17 * CAVEAT: Tab completion causes queries to be sent to the backend.
18 * The number tuples returned gets limited, in most default
19 * installations to 101, but if you still don't like this prospect,
20 * you can turn off tab completion in your ~/.inputrc (or else
21 * ${INPUTRC}) file so:
24 * set disable-completion on
27 * See `man 3 readline' or `info readline' for the full details. Also,
32 * - If you split your queries across lines, this whole things gets
33 * confused. (To fix this, one would have to read psql's query
34 * buffer rather than readline's line buffer, which would require
35 * some major revisions of things.)
37 * - Table or attribute names with spaces in it may confuse it.
39 * - Quotes, parenthesis, and other funny characters are not handled
40 * all that gracefully.
41 *----------------------------------------------------------------------
44 #include "postgres_fe.h"
45 #include "tab-complete.h"
49 /* If we don't have this, we might as well forget about the whole thing: */
53 #ifdef USE_ASSERT_CHECKING
62 #ifdef HAVE_RL_FILENAME_COMPLETION_FUNCTION
63 #define filename_completion_function rl_filename_completion_function
65 /* missing in some header files */
66 extern char *filename_completion_function();
69 #ifdef HAVE_RL_COMPLETION_MATCHES
70 #define completion_matches rl_completion_matches
74 #define ERROR_QUERY_TOO_LONG /* empty */
77 /* Forward declaration of functions */
78 static char **psql_completion(char *text, int start, int end);
79 static char *create_command_generator(const char *text, int state);
80 static char *complete_from_query(const char *text, int state);
81 static char *complete_from_schema_query(const char *text, int state);
82 static char *_complete_from_query(int is_schema_query,
83 const char *text, int state);
84 static char *complete_from_const(const char *text, int state);
85 static char *complete_from_list(const char *text, int state);
87 static PGresult *exec_query(char *query);
89 static char *previous_word(int point, int skip);
92 static char *quote_file_name(char *text, int match_type, char *quote_pointer);
93 static char *dequote_file_name(char *text, char quote_char);
96 /* These variables are used to pass information into the completion functions.
97 Realizing that this is the cardinal sin of programming, I don't see a better
99 static char *completion_charp; /* if you need to pass a string */
100 static char **completion_charpp; /* if you need to pass a list of strings */
101 static char *completion_info_charp; /* if you need to pass another
104 /* Store how many records from a database query we want to return at most
105 (implemented via SELECT ... LIMIT xx). */
106 static int completion_max_records;
109 /* Initialize the readline library for our purposes. */
111 initialize_readline(void)
113 rl_readline_name = pset.progname;
114 rl_attempted_completion_function = (void *) psql_completion;
116 rl_basic_word_break_characters = "\t\n@$><=;|&{( ";
118 completion_max_records = 100;
121 * There is a variable rl_completion_query_items for this but
122 * apparently it's not defined everywhere.
128 * Queries to get lists of names of various kinds of things, possibly
129 * restricted to names matching a partially entered name. In these queries,
130 * %s will be replaced by the text entered so far (suitably escaped to
131 * become a SQL literal string). %d will be replaced by the length of the
132 * string (in unescaped form). Beware that the allowed sequences of %s and
133 * %d are determined by _complete_from_query().
136 #define Query_for_list_of_aggregates \
137 " SELECT DISTINCT proname " \
138 " FROM pg_catalog.pg_proc" \
140 " AND substr(proname,1,%d)='%s'" \
142 " SELECT nspname || '.' AS relname" \
143 " FROM pg_catalog.pg_namespace" \
144 " WHERE substr(nspname,1,%d)='%s'" \
146 " SELECT DISTINCT nspname || '.' || proname AS relname" \
147 " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n" \
149 " AND substr(nspname || '.' || proname,1,%d)='%s'" \
150 " AND pronamespace = n.oid" \
151 " AND ('%s' ~ '^.*\\\\.' "\
153 " FROM pg_catalog.pg_namespace "\
154 " WHERE substr(nspname,1,%d)='%s' "\
155 " HAVING COUNT(nspname)=1))"
157 #define Query_for_list_of_attributes \
158 "SELECT a.attname FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c "\
159 " WHERE c.oid = a.attrelid "\
160 " AND a.attnum > 0 "\
161 " AND NOT a.attisdropped "\
162 " AND substr(a.attname,1,%d)='%s' "\
163 " AND c.relname='%s' "\
164 " AND pg_catalog.pg_table_is_visible(c.oid)"
166 #define Query_for_list_of_databases \
167 "SELECT datname FROM pg_catalog.pg_database "\
168 " WHERE substr(datname,1,%d)='%s'"
170 #define Query_for_list_of_datatypes \
171 " SELECT pg_catalog.format_type(t.oid, NULL) "\
172 " FROM pg_catalog.pg_type t "\
173 " WHERE (t.typrelid = 0 "\
174 " OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "\
175 " AND t.typname !~ '^_' "\
176 " AND substr(pg_catalog.format_type(t.oid, NULL),1,%d)='%s' "\
178 " SELECT nspname || '.' AS relname "\
179 " FROM pg_catalog.pg_namespace "\
180 " WHERE substr(nspname,1,%d)='%s' "\
182 " SELECT nspname || '.' || pg_catalog.format_type(t.oid, NULL) AS relname "\
183 " FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n "\
184 " WHERE(t.typrelid = 0 "\
185 " OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) "\
186 " AND t.typname !~ '^_' "\
187 " AND substr(nspname || '.' || pg_catalog.format_type(t.oid, NULL),1,%d)='%s' "\
188 " AND typnamespace = n.oid "\
189 " AND ('%s' ~ '^.*\\\\.' "\
191 " FROM pg_catalog.pg_namespace "\
192 " WHERE substr(nspname,1,%d)='%s' "\
193 " HAVING COUNT(nspname)=1))"
195 #define Query_for_list_of_domains \
197 " FROM pg_catalog.pg_type t "\
198 " WHERE typtype = 'd' "\
199 " AND substr(typname,1,%d)='%s' "\
201 " SELECT nspname || '.' "\
202 " FROM pg_catalog.pg_namespace "\
203 " WHERE substr(nspname,1,%d)='%s' "\
205 " SELECT nspname || '.' || pg_catalog.format_type(t.oid, NULL) "\
206 " FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n "\
207 " WHERE typtype = 'd' "\
208 " AND substr(nspname || '.' || typname,1,%d)='%s' "\
209 " AND typnamespace = n.oid "\
210 " AND ('%s' ~ '^.*\\\\.' "\
212 " FROM pg_catalog.pg_namespace "\
213 " WHERE substr(nspname,1,%d)='%s' "\
214 " HAVING COUNT(nspname)=1))"
216 #define Query_for_list_of_encodings \
217 " SELECT DISTINCT pg_catalog.pg_encoding_to_char(conforencoding) "\
218 " FROM pg_catalog.pg_conversion "\
219 " WHERE substr(pg_catalog.pg_encoding_to_char(conforencoding),1,%d)=UPPER('%s')"
221 #define Query_for_list_of_functions \
222 " SELECT DISTINCT proname || '()' "\
223 " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n "\
224 " WHERE substr(proname,1,%d)='%s'"\
225 " AND pg_catalog.pg_function_is_visible(p.oid) "\
226 " AND pronamespace = n.oid "\
228 " SELECT nspname || '.' "\
229 " FROM pg_catalog.pg_namespace "\
230 " WHERE substr(nspname,1,%d)='%s' "\
232 " SELECT nspname || '.' || proname "\
233 " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n "\
234 " WHERE substr(nspname || '.' || proname,1,%d)='%s' "\
235 " AND pronamespace = n.oid "\
236 " AND ('%s' ~ '^.*\\\\.' "\
238 " FROM pg_catalog.pg_namespace "\
239 " WHERE substr(nspname,1,%d)='%s' "\
240 " HAVING COUNT(nspname)=1))"
242 #define Query_for_list_of_indexes \
244 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
245 " WHERE relkind='i' "\
246 " AND substr(relname,1,%d)='%s' "\
247 " AND pg_catalog.pg_table_is_visible(c.oid) "\
248 " AND relnamespace = n.oid "\
249 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
251 " SELECT nspname || '.' "\
252 " FROM pg_catalog.pg_namespace "\
253 " WHERE substr(nspname,1,%d)='%s' "\
255 " SELECT nspname || '.' || relname "\
256 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
257 " WHERE relkind='i' "\
258 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
259 " AND relnamespace = n.oid "\
260 " AND ('%s' ~ '^.*\\\\.' "\
262 " FROM pg_catalog.pg_namespace "\
263 " WHERE substr(nspname,1,%d)='%s' "\
264 " HAVING COUNT(nspname)=1))"
267 #define Query_for_list_of_languages \
269 " FROM pg_language "\
270 " WHERE lanname != 'internal' "\
271 " AND substr(lanname,1,%d)='%s' "
273 #define Query_for_list_of_schemas \
274 "SELECT nspname FROM pg_catalog.pg_namespace "\
275 " WHERE substr(nspname,1,%d)='%s'"
277 #define Query_for_list_of_sequences \
279 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
280 " WHERE relkind='S' "\
281 " AND substr(relname,1,%d)='%s' "\
282 " AND pg_catalog.pg_table_is_visible(c.oid) "\
283 " AND relnamespace = n.oid "\
284 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
286 " SELECT nspname || '.' "\
287 " FROM pg_catalog.pg_namespace "\
288 " WHERE substr(nspname,1,%d)='%s' "\
290 " SELECT nspname || '.' || relname "\
291 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
292 " WHERE relkind='S' "\
293 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
294 " AND relnamespace = n.oid "\
295 " AND ('%s' ~ '^.*\\\\.' "\
297 " FROM pg_catalog.pg_namespace "\
298 " WHERE substr(nspname,1,%d)='%s' "\
299 " HAVING COUNT(nspname)=1))"
301 #define Query_for_list_of_system_relations \
302 "SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
303 " WHERE (c.relkind='r' OR c.relkind='v' OR c.relkind='s' OR c.relkind='S') "\
304 " AND substr(c.relname,1,%d)='%s' "\
305 " AND pg_catalog.pg_table_is_visible(c.oid)"\
306 " AND relnamespace = n.oid "\
307 " AND n.nspname = 'pg_catalog'"
309 #define Query_for_list_of_tables \
311 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
312 " WHERE relkind='r' "\
313 " AND substr(relname,1,%d)='%s' "\
314 " AND pg_catalog.pg_table_is_visible(c.oid) "\
315 " AND relnamespace = n.oid "\
316 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
318 " SELECT nspname || '.' "\
319 " FROM pg_catalog.pg_namespace "\
320 " WHERE substr(nspname || '.',1,%d)='%s' "\
322 " SELECT nspname || '.' || relname "\
323 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
324 " WHERE relkind='r' "\
325 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
326 " AND relnamespace = n.oid "\
327 " AND ('%s' ~ '^.*\\\\.' "\
329 " FROM pg_catalog.pg_namespace n1 "\
330 " WHERE substr(nspname ||'.',1,%d)='%s' "\
331 " HAVING COUNT(nspname)=1))"
333 #define Query_for_list_of_tisv \
335 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
336 " WHERE (relkind='r' OR relkind='i' OR relkind='S' OR relkind='v') "\
337 " AND substr(relname,1,%d)='%s' "\
338 " AND pg_catalog.pg_table_is_visible(c.oid) "\
339 " AND relnamespace = n.oid "\
340 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
342 " SELECT nspname || '.' "\
343 " FROM pg_catalog.pg_namespace "\
344 " WHERE substr(nspname,1,%d)='%s' "\
346 " SELECT nspname || '.' || relname "\
347 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
348 " WHERE (relkind='r' OR relkind='i' OR relkind='S' OR relkind='v') "\
349 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
350 " AND relnamespace = n.oid "\
351 " AND ('%s' ~ '^.*\\\\.' "\
353 " FROM pg_catalog.pg_namespace "\
354 " WHERE substr(nspname,1,%d)='%s' "\
355 " HAVING COUNT(nspname)=1))"
357 #define Query_for_list_of_tsv \
359 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
360 " WHERE (relkind='r' OR relkind='S' OR relkind='v') "\
361 " AND substr(relname,1,%d)='%s' "\
362 " AND pg_catalog.pg_table_is_visible(c.oid) "\
363 " AND relnamespace = n.oid "\
364 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
366 " SELECT nspname || '.' "\
367 " FROM pg_catalog.pg_namespace "\
368 " WHERE substr(nspname,1,%d)='%s' "\
370 " SELECT nspname || '.' || relname "\
371 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
372 " WHERE (relkind='r' OR relkind='S' OR relkind='v') "\
373 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
374 " AND relnamespace = n.oid "\
375 " AND ('%s' ~ '^.*\\\\.' "\
377 " FROM pg_catalog.pg_namespace "\
378 " WHERE substr(nspname,1,%d)='%s' "\
379 " HAVING COUNT(nspname)=1))"
381 #define Query_for_list_of_views \
383 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
384 " WHERE relkind='v'"\
385 " AND substr(relname,1,%d)='%s' "\
386 " AND pg_catalog.pg_table_is_visible(c.oid) "\
387 " AND relnamespace = n.oid "\
388 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "\
390 " SELECT nspname || '.' "\
391 " FROM pg_catalog.pg_namespace "\
392 " WHERE substr(nspname,1,%d)='%s' "\
394 " SELECT nspname || '.' || relname "\
395 " FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
396 " WHERE relkind='v' "\
397 " AND substr(nspname || '.' || relname,1,%d)='%s' "\
398 " AND relnamespace = n.oid "\
399 " AND ('%s' ~ '^.*\\\\.' "\
401 " FROM pg_catalog.pg_namespace "\
402 " WHERE substr(nspname,1,%d)='%s' "\
403 " HAVING COUNT(nspname)=1))"
405 #define Query_for_list_of_users \
407 " FROM pg_catalog.pg_user "\
408 " WHERE substr(usename,1,%d)='%s'"
410 /* the silly-looking length condition is just to eat up the current word */
411 #define Query_for_table_owning_index \
412 "SELECT c1.relname "\
413 " FROM pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_index i"\
414 " WHERE c1.oid=i.indrelid and i.indexrelid=c2.oid"\
415 " and (%d = length('%s'))"\
416 " and c2.relname='%s'"\
417 " and pg_catalog.pg_table_is_visible(c2.oid)"
419 /* This is a list of all "things" in Pgsql, which can show up after CREATE or
420 DROP; and there is also a query to get a list of them.
423 #define WITH_SCHEMA 1
433 pgsql_thing_t words_after_create[] = {
434 {"AGGREGATE", WITH_SCHEMA, Query_for_list_of_aggregates},
435 {"CAST", NO_SCHEMA, NULL}, /* Casts have complex structures for
436 * namees, so skip it */
437 {"CONVERSION", NO_SCHEMA, "SELECT conname FROM pg_catalog.pg_conversion WHERE substr(conname,1,%d)='%s'"},
438 {"DATABASE", NO_SCHEMA, Query_for_list_of_databases},
439 {"DOMAIN", WITH_SCHEMA, Query_for_list_of_domains},
440 {"FUNCTION", WITH_SCHEMA, Query_for_list_of_functions},
441 {"GROUP", NO_SCHEMA, "SELECT groname FROM pg_catalog.pg_group WHERE substr(groname,1,%d)='%s'"},
442 {"LANGUAGE", NO_SCHEMA, Query_for_list_of_languages},
443 {"INDEX", WITH_SCHEMA, Query_for_list_of_indexes},
444 {"OPERATOR", NO_SCHEMA, NULL}, /* Querying for this is probably
445 * not such a good idea. */
446 {"RULE", NO_SCHEMA, "SELECT rulename FROM pg_catalog.pg_rules WHERE substr(rulename,1,%d)='%s'"},
447 {"SCHEMA", NO_SCHEMA, Query_for_list_of_schemas},
448 {"SEQUENCE", WITH_SCHEMA, Query_for_list_of_sequences},
449 {"TABLE", WITH_SCHEMA, Query_for_list_of_tables},
450 {"TEMP", NO_SCHEMA, NULL}, /* for CREATE TEMP TABLE ... */
451 {"TRIGGER", NO_SCHEMA, "SELECT tgname FROM pg_catalog.pg_trigger WHERE substr(tgname,1,%d)='%s'"},
452 {"TYPE", WITH_SCHEMA, Query_for_list_of_datatypes},
453 {"UNIQUE", NO_SCHEMA, NULL}, /* for CREATE UNIQUE INDEX ... */
454 {"USER", NO_SCHEMA, Query_for_list_of_users},
455 {"VIEW", WITH_SCHEMA, Query_for_list_of_views},
456 {NULL, NO_SCHEMA, NULL} /* end of list */
460 /* A couple of macros to ease typing. You can use these to complete the given
462 1) The results from a query you pass it. (Perhaps one of those above?)
463 2) The results from a schema query you pass it.
464 3) The items from a null-pointer-terminated list.
466 5) The list of attributes to the given table.
468 #define COMPLETE_WITH_QUERY(query) \
469 do { completion_charp = query; matches = completion_matches(text, complete_from_query); } while(0)
470 #define COMPLETE_WITH_SCHEMA_QUERY(query) \
471 do { completion_charp = query; matches = completion_matches(text, complete_from_schema_query); } while(0)
472 #define COMPLETE_WITH_LIST(list) \
473 do { completion_charpp = list; matches = completion_matches(text, complete_from_list); } while(0)
474 #define COMPLETE_WITH_CONST(string) \
475 do { completion_charp = string; matches = completion_matches(text, complete_from_const); } while(0)
476 #define COMPLETE_WITH_ATTR(table) \
477 do {completion_charp = Query_for_list_of_attributes; completion_info_charp = table; matches = completion_matches(text, complete_from_query); } while(0)
480 /* The completion function. Acc. to readline spec this gets passed the text
481 entered to far and its start and end in the readline buffer. The return value
482 is some partially obscure list format that can be generated by the readline
483 libraries completion_matches() function, so we don't have to worry about it.
486 psql_completion(char *text, int start, int end)
488 /* This is the variable we'll return. */
489 char **matches = NULL;
491 /* These are going to contain some scannage of the input line. */
497 static char *sql_commands[] = {
498 "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT",
499 "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", "EXECUTE",
500 "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
501 "PREPARE", "REINDEX", "RESET", "REVOKE", "ROLLBACK", "SELECT", "SET", "SHOW",
502 "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", NULL
505 static char *pgsql_variables[] = {
506 /* these SET arguments are known in gram.y */
513 * the rest should match USERSET and possibly SUSET entries in
514 * backend/utils/misc/guc.c.
517 "australian_timezones",
519 "client_min_messages",
522 "cpu_index_tuple_cost",
527 "debug_pretty_print",
530 "debug_print_rewritten",
531 "default_statistics_target",
532 "default_transaction_isolation",
533 "default_transaction_read_only",
534 "dynamic_library_path",
535 "effective_cache_size",
544 "explain_pretty_print",
545 "extra_float_digits",
546 "from_collapse_limit",
552 "geqo_selection_bias",
554 "join_collapse_limit",
555 "krb_server_keyfile",
561 "log_error_verbosity",
562 "log_executor_stats",
563 "log_min_duration_statement",
564 "log_min_error_statement",
569 "log_statement_stats",
572 "max_files_per_process",
575 "max_locks_per_transaction",
576 "password_encryption",
589 "stats_command_string",
590 "stats_reset_on_server_start",
592 "stats_start_collector",
593 "superuser_reserved_connections",
600 "transform_null_equals",
601 "unix_socket_directory",
603 "unix_socket_permissions",
611 static char *backslash_commands[] = {
612 "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
613 "\\d", "\\da", "\\dc", "\\dC", "\\dd", "\\dD", "\\df", "\\di",
614 "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT",
616 "\\e", "\\echo", "\\encoding",
617 "\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
618 "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
619 "\\o", "\\p", "\\pset", "\\q", "\\qecho", "\\r", "\\set", "\\t", "\\T",
620 "\\timing", "\\unset", "\\x", "\\w", "\\z", "\\!", NULL
623 (void) end; /* not used */
625 #ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
626 rl_completion_append_character = ' ';
629 /* Clear a few things. */
630 completion_charp = NULL;
631 completion_charpp = NULL;
632 completion_info_charp = NULL;
635 * Scan the input line before our current position for the last four
636 * words. According to those we'll make some smart decisions on what
637 * the user is probably intending to type. TODO: Use strtokx() to do
640 prev_wd = previous_word(start, 0);
641 prev2_wd = previous_word(start, 1);
642 prev3_wd = previous_word(start, 2);
643 prev4_wd = previous_word(start, 3);
645 /* If a backslash command was started, continue */
647 COMPLETE_WITH_LIST(backslash_commands);
649 /* If no previous word, suggest one of the basic sql commands */
651 COMPLETE_WITH_LIST(sql_commands);
653 /* CREATE or DROP but not ALTER TABLE sth DROP */
654 /* complete with something you can create or drop */
655 else if (strcasecmp(prev_wd, "CREATE") == 0 ||
656 (strcasecmp(prev_wd, "DROP") == 0 &&
657 strcasecmp(prev3_wd, "TABLE") != 0))
658 matches = completion_matches(text, create_command_generator);
663 * complete with what you can alter (TABLE, GROUP, USER, ...) unless
664 * we're in ALTER TABLE sth ALTER
666 else if (strcasecmp(prev_wd, "ALTER") == 0 &&
667 strcasecmp(prev3_wd, "TABLE") != 0)
669 char *list_ALTER[] = {"DATABASE", "GROUP", "SCHEMA", "TABLE",
670 "TRIGGER", "USER", NULL};
672 COMPLETE_WITH_LIST(list_ALTER);
675 /* ALTER DATABASE <name> */
676 else if (strcasecmp(prev3_wd, "ALTER") == 0 &&
677 strcasecmp(prev2_wd, "DATABASE") == 0)
679 char *list_ALTERDATABASE[] = {"RESET", "SET", NULL};
681 COMPLETE_WITH_LIST(list_ALTERDATABASE);
683 /* ALTER TRIGGER <name>, add ON */
684 else if (strcasecmp(prev3_wd, "ALTER") == 0 &&
685 strcasecmp(prev2_wd, "TRIGGER") == 0)
686 COMPLETE_WITH_CONST("ON");
689 * If we have ALTER TRIGGER <sth> ON, then add the correct tablename
691 else if (strcasecmp(prev4_wd, "ALTER") == 0 &&
692 strcasecmp(prev3_wd, "TRIGGER") == 0 &&
693 strcasecmp(prev_wd, "ON") == 0)
694 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
697 * If we detect ALTER TABLE <name>, suggest either ADD, DROP, ALTER,
700 else if (strcasecmp(prev3_wd, "ALTER") == 0 &&
701 strcasecmp(prev2_wd, "TABLE") == 0)
703 char *list_ALTER2[] = {"ADD", "ALTER", "DROP", "RENAME",
706 COMPLETE_WITH_LIST(list_ALTER2);
708 /* If we have TABLE <sth> ALTER|RENAME, provide list of columns */
709 else if (strcasecmp(prev3_wd, "TABLE") == 0 &&
710 (strcasecmp(prev_wd, "ALTER") == 0 ||
711 strcasecmp(prev_wd, "RENAME") == 0))
712 COMPLETE_WITH_ATTR(prev2_wd);
714 /* If we have TABLE <sth> DROP, provide COLUMN or CONSTRAINT */
715 else if (strcasecmp(prev3_wd, "TABLE") == 0 &&
716 strcasecmp(prev_wd, "DROP") == 0)
718 char *list_TABLEDROP[] = {"COLUMN", "CONSTRAINT", NULL};
720 COMPLETE_WITH_LIST(list_TABLEDROP);
722 /* If we have TABLE <sth> DROP COLUMN, provide list of columns */
723 else if (strcasecmp(prev4_wd, "TABLE") == 0 &&
724 strcasecmp(prev2_wd, "DROP") == 0 &&
725 strcasecmp(prev_wd, "COLUMN") == 0)
726 COMPLETE_WITH_ATTR(prev3_wd);
728 /* complete ALTER GROUP <foo> with ADD or DROP */
729 else if (strcasecmp(prev3_wd, "ALTER") == 0 &&
730 strcasecmp(prev2_wd, "GROUP") == 0)
732 char *list_ALTERGROUP[] = {"ADD", "DROP", NULL};
734 COMPLETE_WITH_LIST(list_ALTERGROUP);
736 /* complete ALTER GROUP <foo> ADD|DROP with USER */
737 else if (strcasecmp(prev4_wd, "ALTER") == 0 &&
738 strcasecmp(prev3_wd, "GROUP") == 0 &&
739 (strcasecmp(prev_wd, "ADD") == 0 ||
740 strcasecmp(prev_wd, "DROP") == 0))
741 COMPLETE_WITH_CONST("USER");
742 /* complete {ALTER} GROUP <foo> ADD|DROP USER with a user name */
743 else if (strcasecmp(prev4_wd, "GROUP") == 0 &&
744 (strcasecmp(prev2_wd, "ADD") == 0 ||
745 strcasecmp(prev2_wd, "DROP") == 0) &&
746 strcasecmp(prev_wd, "USER") == 0)
747 COMPLETE_WITH_QUERY(Query_for_list_of_users);
750 /* If the previous word is ANALYZE, produce list of tables. */
751 else if (strcasecmp(prev_wd, "ANALYZE") == 0)
752 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
753 /* If we have ANALYZE <table>, complete with semicolon. */
754 else if (strcasecmp(prev2_wd, "ANALYZE") == 0)
755 COMPLETE_WITH_CONST(";");
758 /* If the previous word is CLUSTER, produce list of indexes. */
759 else if (strcasecmp(prev_wd, "CLUSTER") == 0)
760 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
761 /* If we have CLUSTER <sth>, then add "ON" */
762 else if (strcasecmp(prev2_wd, "CLUSTER") == 0)
763 COMPLETE_WITH_CONST("ON");
766 * If we have CLUSTER <sth> ON, then add the correct tablename as
769 else if (strcasecmp(prev3_wd, "CLUSTER") == 0 &&
770 strcasecmp(prev_wd, "ON") == 0)
772 completion_info_charp = prev2_wd;
773 COMPLETE_WITH_QUERY(Query_for_table_owning_index);
777 else if (strcasecmp(prev_wd, "COMMENT") == 0)
778 COMPLETE_WITH_CONST("ON");
779 else if (strcasecmp(prev2_wd, "COMMENT") == 0 &&
780 strcasecmp(prev_wd, "ON") == 0)
782 char *list_COMMENT[] =
783 {"DATABASE", "INDEX", "RULE", "SCHEMA", "SEQUENCE", "TABLE",
784 "TYPE", "VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR",
785 "TRIGGER", "CONSTRAINT", "DOMAIN", NULL};
787 COMPLETE_WITH_LIST(list_COMMENT);
789 else if (strcasecmp(prev4_wd, "COMMENT") == 0 &&
790 strcasecmp(prev3_wd, "ON") == 0)
791 COMPLETE_WITH_CONST("IS");
796 * If we have COPY [BINARY] (which you'd have to type yourself), offer
797 * list of tables (Also cover the analogous backslash command)
799 else if (strcasecmp(prev_wd, "COPY") == 0 ||
800 strcasecmp(prev_wd, "\\copy") == 0 ||
801 (strcasecmp(prev2_wd, "COPY") == 0 &&
802 strcasecmp(prev_wd, "BINARY") == 0))
803 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
804 /* If we have COPY|BINARY <sth>, complete it with "TO" or "FROM" */
805 else if (strcasecmp(prev2_wd, "COPY") == 0 ||
806 strcasecmp(prev2_wd, "\\copy") == 0 ||
807 strcasecmp(prev2_wd, "BINARY") == 0)
809 char *list_FROMTO[] = {"FROM", "TO", NULL};
811 COMPLETE_WITH_LIST(list_FROMTO);
815 /* First off we complete CREATE UNIQUE with "INDEX" */
816 else if (strcasecmp(prev2_wd, "CREATE") == 0 &&
817 strcasecmp(prev_wd, "UNIQUE") == 0)
818 COMPLETE_WITH_CONST("INDEX");
819 /* If we have CREATE|UNIQUE INDEX <sth>, then add "ON" */
820 else if (strcasecmp(prev2_wd, "INDEX") == 0 &&
821 (strcasecmp(prev3_wd, "CREATE") == 0 ||
822 strcasecmp(prev3_wd, "UNIQUE") == 0))
823 COMPLETE_WITH_CONST("ON");
824 /* Complete ... INDEX <name> ON with a list of tables */
825 else if (strcasecmp(prev3_wd, "INDEX") == 0 &&
826 strcasecmp(prev_wd, "ON") == 0)
827 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
830 * Complete INDEX <name> ON <table> with a list of table columns
831 * (which should really be in parens)
833 else if (strcasecmp(prev4_wd, "INDEX") == 0 &&
834 strcasecmp(prev2_wd, "ON") == 0)
835 COMPLETE_WITH_ATTR(prev_wd);
836 /* same if you put in USING */
837 else if (strcasecmp(prev4_wd, "ON") == 0 &&
838 strcasecmp(prev2_wd, "USING") == 0)
839 COMPLETE_WITH_ATTR(prev3_wd);
840 /* Complete USING with an index method */
841 else if (strcasecmp(prev_wd, "USING") == 0)
843 char *index_mth[] = {"BTREE", "RTREE", "HASH", "GIST", NULL};
845 COMPLETE_WITH_LIST(index_mth);
849 /* Complete "CREATE RULE <sth>" with "AS" */
850 else if (strcasecmp(prev3_wd, "CREATE") == 0 &&
851 strcasecmp(prev2_wd, "RULE") == 0)
852 COMPLETE_WITH_CONST("AS");
853 /* Complete "CREATE RULE <sth> AS with "ON" */
854 else if (strcasecmp(prev4_wd, "CREATE") == 0 &&
855 strcasecmp(prev3_wd, "RULE") == 0 &&
856 strcasecmp(prev_wd, "AS") == 0)
857 COMPLETE_WITH_CONST("ON");
858 /* Complete "RULE * AS ON" with SELECT|UPDATE|DELETE|INSERT */
859 else if (strcasecmp(prev4_wd, "RULE") == 0 &&
860 strcasecmp(prev2_wd, "AS") == 0 &&
861 strcasecmp(prev_wd, "ON") == 0)
863 char *rule_events[] = {"SELECT", "UPDATE", "INSERT",
866 COMPLETE_WITH_LIST(rule_events);
868 /* Complete "AS ON <sth with a 'T' :)>" with a "TO" */
869 else if (strcasecmp(prev3_wd, "AS") == 0 &&
870 strcasecmp(prev2_wd, "ON") == 0 &&
871 (toupper((unsigned char) prev_wd[4]) == 'T' ||
872 toupper((unsigned char) prev_wd[5]) == 'T'))
873 COMPLETE_WITH_CONST("TO");
874 /* Complete "AS ON <sth> TO" with a table name */
875 else if (strcasecmp(prev4_wd, "AS") == 0 &&
876 strcasecmp(prev3_wd, "ON") == 0 &&
877 strcasecmp(prev_wd, "TO") == 0)
878 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
881 /* Complete CREATE TEMP with "TABLE" */
882 else if (strcasecmp(prev2_wd, "CREATE") == 0 &&
883 strcasecmp(prev_wd, "TEMP") == 0)
884 COMPLETE_WITH_CONST("TABLE");
887 /* is on the agenda . . . */
890 /* Complete "CREATE VIEW <name>" with "AS" */
891 else if (strcasecmp(prev3_wd, "CREATE") == 0 &&
892 strcasecmp(prev2_wd, "VIEW") == 0)
893 COMPLETE_WITH_CONST("AS");
894 /* Complete "CREATE VIEW <sth> AS with "SELECT" */
895 else if (strcasecmp(prev4_wd, "CREATE") == 0 &&
896 strcasecmp(prev3_wd, "VIEW") == 0 &&
897 strcasecmp(prev_wd, "AS") == 0)
898 COMPLETE_WITH_CONST("SELECT");
903 * Complete DELETE with FROM (only if the word before that is not "ON"
904 * (cf. rules) or "BEFORE" or "AFTER" (cf. triggers) or GRANT)
906 else if (strcasecmp(prev_wd, "DELETE") == 0 &&
907 !(strcasecmp(prev2_wd, "ON") == 0 ||
908 strcasecmp(prev2_wd, "GRANT") == 0 ||
909 strcasecmp(prev2_wd, "BEFORE") == 0 ||
910 strcasecmp(prev2_wd, "AFTER") == 0))
911 COMPLETE_WITH_CONST("FROM");
912 /* Complete DELETE FROM with a list of tables */
913 else if (strcasecmp(prev2_wd, "DELETE") == 0 &&
914 strcasecmp(prev_wd, "FROM") == 0)
915 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
916 /* Complete DELETE FROM <table> with "WHERE" (perhaps a safe idea?) */
917 else if (strcasecmp(prev3_wd, "DELETE") == 0 &&
918 strcasecmp(prev2_wd, "FROM") == 0)
919 COMPLETE_WITH_CONST("WHERE");
924 * Complete EXPLAIN [VERBOSE] (which you'd have to type yourself) with
925 * the list of SQL commands
927 else if (strcasecmp(prev_wd, "EXPLAIN") == 0 ||
928 (strcasecmp(prev2_wd, "EXPLAIN") == 0 &&
929 strcasecmp(prev_wd, "VERBOSE") == 0))
930 COMPLETE_WITH_LIST(sql_commands);
933 /* Complete FETCH with one of FORWARD, BACKWARD, RELATIVE */
934 else if (strcasecmp(prev_wd, "FETCH") == 0 ||
935 strcasecmp(prev_wd, "MOVE") == 0)
937 char *list_FETCH1[] = {"FORWARD", "BACKWARD", "RELATIVE", NULL};
939 COMPLETE_WITH_LIST(list_FETCH1);
941 /* Complete FETCH <sth> with one of ALL, NEXT, PRIOR */
942 else if (strcasecmp(prev2_wd, "FETCH") == 0 ||
943 strcasecmp(prev2_wd, "MOVE") == 0)
945 char *list_FETCH2[] = {"ALL", "NEXT", "PRIOR", NULL};
947 COMPLETE_WITH_LIST(list_FETCH2);
951 * Complete FETCH <sth1> <sth2> with "FROM" or "TO". (Is there a
952 * difference? If not, remove one.)
954 else if (strcasecmp(prev3_wd, "FETCH") == 0 ||
955 strcasecmp(prev3_wd, "MOVE") == 0)
957 char *list_FROMTO[] = {"FROM", "TO", NULL};
959 COMPLETE_WITH_LIST(list_FROMTO);
963 /* Complete GRANT/REVOKE with a list of privileges */
964 else if (strcasecmp(prev_wd, "GRANT") == 0 ||
965 strcasecmp(prev_wd, "REVOKE") == 0)
967 char *list_privileg[] = {"SELECT", "INSERT", "UPDATE", "DELETE", "RULE", "REFERENCES", "TRIGGER", "CREATE", "TEMPORARY", "EXECUTE", "USAGE", "ALL", NULL};
969 COMPLETE_WITH_LIST(list_privileg);
971 /* Complete GRANT/REVOKE <sth> with "ON" */
972 else if (strcasecmp(prev2_wd, "GRANT") == 0 ||
973 strcasecmp(prev2_wd, "REVOKE") == 0)
974 COMPLETE_WITH_CONST("ON");
977 * Complete GRANT/REVOKE <sth> ON with a list of tables, views,
978 * sequences, and indexes
980 * keywords DATABASE, FUNCTION, LANGUAGE, SCHEMA added to query result
981 * via UNION; seems to work intuitively
983 * Note: GRANT/REVOKE can get quite complex; tab-completion as
984 * implemented here will only work if the privilege list contains
985 * exactly one privilege
987 else if ((strcasecmp(prev3_wd, "GRANT") == 0 ||
988 strcasecmp(prev3_wd, "REVOKE") == 0) &&
989 strcasecmp(prev_wd, "ON") == 0)
990 COMPLETE_WITH_QUERY("SELECT relname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "
991 " WHERE relkind in ('r','S','v') "
992 " AND substr(relname,1,%d)='%s' "
993 " AND pg_catalog.pg_table_is_visible(c.oid) "
994 " AND relnamespace = n.oid "
995 " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "
997 "SELECT 'DATABASE' AS relname "
999 "SELECT 'FUNCTION' AS relname "
1001 "SELECT 'LANGUAGE' AS relname "
1003 "SELECT 'SCHEMA' AS relname ");
1005 /* Complete "GRANT/REVOKE * ON * " with "TO" */
1006 else if ((strcasecmp(prev4_wd, "GRANT") == 0 ||
1007 strcasecmp(prev4_wd, "REVOKE") == 0) &&
1008 strcasecmp(prev2_wd, "ON") == 0)
1010 if (strcasecmp(prev_wd, "DATABASE") == 0)
1011 COMPLETE_WITH_QUERY(Query_for_list_of_databases);
1012 else if (strcasecmp(prev_wd, "FUNCTION") == 0)
1013 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions);
1014 else if (strcasecmp(prev_wd, "LANGUAGE") == 0)
1015 COMPLETE_WITH_QUERY(Query_for_list_of_languages);
1016 else if (strcasecmp(prev_wd, "SCHEMA") == 0)
1017 COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
1019 COMPLETE_WITH_CONST("TO");
1023 * TODO: to complete with user name we need prev5_wd -- wait for a
1024 * more general solution there same for GRANT <sth> ON { DATABASE |
1025 * FUNCTION | LANGUAGE | SCHEMA } xxx TO
1029 /* Complete INSERT with "INTO" */
1030 else if (strcasecmp(prev_wd, "INSERT") == 0)
1031 COMPLETE_WITH_CONST("INTO");
1032 /* Complete INSERT INTO with table names */
1033 else if (strcasecmp(prev2_wd, "INSERT") == 0 &&
1034 strcasecmp(prev_wd, "INTO") == 0)
1035 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1036 /* Complete "INSERT INTO <table> (" with attribute names */
1037 else if (rl_line_buffer[start - 1] == '(' &&
1038 strcasecmp(prev3_wd, "INSERT") == 0 &&
1039 strcasecmp(prev2_wd, "INTO") == 0)
1040 COMPLETE_WITH_ATTR(prev_wd);
1043 * Complete INSERT INTO <table> with "VALUES" or "SELECT" or "DEFAULT
1046 else if (strcasecmp(prev3_wd, "INSERT") == 0 &&
1047 strcasecmp(prev2_wd, "INTO") == 0)
1049 char *list_INSERT[] = {"DEFAULT VALUES", "SELECT", "VALUES", NULL};
1051 COMPLETE_WITH_LIST(list_INSERT);
1053 /* Complete INSERT INTO <table> (attribs) with "VALUES" or "SELECT" */
1054 else if (strcasecmp(prev4_wd, "INSERT") == 0 &&
1055 strcasecmp(prev3_wd, "INTO") == 0 &&
1056 prev_wd[strlen(prev_wd) - 1] == ')')
1058 char *list_INSERT[] = {"SELECT", "VALUES", NULL};
1060 COMPLETE_WITH_LIST(list_INSERT);
1063 /* Insert an open parenthesis after "VALUES" */
1064 else if (strcasecmp(prev_wd, "VALUES") == 0 &&
1065 strcasecmp(prev2_wd, "DEFAULT") != 0)
1066 COMPLETE_WITH_CONST("(");
1069 /* Complete LOCK [TABLE] with a list of tables */
1070 else if (strcasecmp(prev_wd, "LOCK") == 0 ||
1071 (strcasecmp(prev_wd, "TABLE") == 0 &&
1072 strcasecmp(prev2_wd, "LOCK") == 0))
1073 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1075 /* For the following, handle the case of a single table only for now */
1077 /* Complete LOCK [TABLE] <table> with "IN" */
1078 else if ((strcasecmp(prev2_wd, "LOCK") == 0 &&
1079 strcasecmp(prev_wd, "TABLE")) ||
1080 (strcasecmp(prev2_wd, "TABLE") == 0 &&
1081 strcasecmp(prev3_wd, "LOCK") == 0))
1082 COMPLETE_WITH_CONST("IN");
1084 /* Complete LOCK [TABLE] <table> IN with a lock mode */
1085 else if (strcasecmp(prev_wd, "IN") == 0 &&
1086 (strcasecmp(prev3_wd, "LOCK") == 0 ||
1087 (strcasecmp(prev3_wd, "TABLE") == 0 &&
1088 strcasecmp(prev4_wd, "LOCK") == 0)))
1090 char *lock_modes[] = {"ACCESS SHARE MODE",
1091 "ROW SHARE MODE", "ROW EXCLUSIVE MODE",
1092 "SHARE UPDATE EXCLUSIVE MODE", "SHARE MODE",
1093 "SHARE ROW EXCLUSIVE MODE",
1094 "EXCLUSIVE MODE", "ACCESS EXCLUSIVE MODE", NULL};
1096 COMPLETE_WITH_LIST(lock_modes);
1100 else if (strcasecmp(prev_wd, "NOTIFY") == 0)
1101 COMPLETE_WITH_QUERY("SELECT relname FROM pg_catalog.pg_listener WHERE substr(relname,1,%d)='%s'");
1104 else if (strcasecmp(prev_wd, "REINDEX") == 0)
1106 char *list_REINDEX[] = {"TABLE", "DATABASE", "INDEX", NULL};
1108 COMPLETE_WITH_LIST(list_REINDEX);
1110 else if (strcasecmp(prev2_wd, "REINDEX") == 0)
1112 if (strcasecmp(prev_wd, "TABLE") == 0)
1113 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1114 else if (strcasecmp(prev_wd, "DATABASE") == 0)
1115 COMPLETE_WITH_QUERY(Query_for_list_of_databases);
1116 else if (strcasecmp(prev_wd, "INDEX") == 0)
1117 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
1123 /* SET, RESET, SHOW */
1124 /* Complete with a variable name */
1125 else if ((strcasecmp(prev_wd, "SET") == 0 &&
1126 strcasecmp(prev3_wd, "UPDATE") != 0) ||
1127 strcasecmp(prev_wd, "RESET") == 0 ||
1128 strcasecmp(prev_wd, "SHOW") == 0)
1129 COMPLETE_WITH_LIST(pgsql_variables);
1130 /* Complete "SET TRANSACTION" */
1131 else if ((strcasecmp(prev2_wd, "SET") == 0 &&
1132 strcasecmp(prev_wd, "TRANSACTION") == 0) ||
1133 (strcasecmp(prev4_wd, "SESSION") == 0 &&
1134 strcasecmp(prev3_wd, "CHARACTERISTICS") == 0 &&
1135 strcasecmp(prev2_wd, "AS") == 0 &&
1136 strcasecmp(prev_wd, "TRANSACTION") == 0))
1138 char *my_list[] = {"ISOLATION", "READ", NULL};
1140 COMPLETE_WITH_LIST(my_list);
1142 else if (strcasecmp(prev3_wd, "SET") == 0 &&
1143 strcasecmp(prev2_wd, "TRANSACTION") == 0 &&
1144 strcasecmp(prev_wd, "ISOLATION") == 0)
1145 COMPLETE_WITH_CONST("LEVEL");
1146 else if ((strcasecmp(prev4_wd, "SET") == 0 ||
1147 strcasecmp(prev4_wd, "AS") == 0) &&
1148 strcasecmp(prev3_wd, "TRANSACTION") == 0 &&
1149 strcasecmp(prev2_wd, "ISOLATION") == 0 &&
1150 strcasecmp(prev_wd, "LEVEL") == 0)
1152 char *my_list[] = {"READ", "SERIALIZABLE", NULL};
1154 COMPLETE_WITH_LIST(my_list);
1156 else if (strcasecmp(prev4_wd, "TRANSACTION") == 0 &&
1157 strcasecmp(prev3_wd, "ISOLATION") == 0 &&
1158 strcasecmp(prev2_wd, "LEVEL") == 0 &&
1159 strcasecmp(prev_wd, "READ") == 0)
1160 COMPLETE_WITH_CONST("COMMITTED");
1161 else if ((strcasecmp(prev3_wd, "SET") == 0 ||
1162 strcasecmp(prev3_wd, "AS") == 0) &&
1163 strcasecmp(prev2_wd, "TRANSACTION") == 0 &&
1164 strcasecmp(prev_wd, "READ") == 0)
1166 char *my_list[] = {"ONLY", "WRITE", NULL};
1168 COMPLETE_WITH_LIST(my_list);
1170 /* Complete SET CONSTRAINTS <foo> with DEFERRED|IMMEDIATE */
1171 else if (strcasecmp(prev3_wd, "SET") == 0 &&
1172 strcasecmp(prev2_wd, "CONSTRAINTS") == 0)
1174 char *constraint_list[] = {"DEFERRED", "IMMEDIATE", NULL};
1176 COMPLETE_WITH_LIST(constraint_list);
1178 /* Complete SET SESSION with AUTHORIZATION or CHARACTERISTICS... */
1179 else if (strcasecmp(prev2_wd, "SET") == 0 &&
1180 strcasecmp(prev_wd, "SESSION") == 0)
1182 char *my_list[] = {"AUTHORIZATION",
1183 "CHARACTERISTICS AS TRANSACTION",
1186 COMPLETE_WITH_LIST(my_list);
1188 /* Complete SET SESSION AUTHORIZATION with username */
1189 else if (strcasecmp(prev3_wd, "SET") == 0
1190 && strcasecmp(prev2_wd, "SESSION") == 0
1191 && strcasecmp(prev_wd, "AUTHORIZATION") == 0)
1192 COMPLETE_WITH_QUERY(Query_for_list_of_users);
1193 /* Complete SET <var> with "TO" */
1194 else if (strcasecmp(prev2_wd, "SET") == 0 &&
1195 strcasecmp(prev4_wd, "UPDATE") != 0)
1196 COMPLETE_WITH_CONST("TO");
1197 /* Suggest possible variable values */
1198 else if (strcasecmp(prev3_wd, "SET") == 0 &&
1199 (strcasecmp(prev_wd, "TO") == 0 || strcmp(prev_wd, "=") == 0))
1201 if (strcasecmp(prev2_wd, "DateStyle") == 0)
1203 char *my_list[] = {"ISO", "SQL", "Postgres", "German",
1204 "YMD", "DMY", "MDY",
1205 "US", "European", "NonEuropean",
1208 COMPLETE_WITH_LIST(my_list);
1210 else if (strcasecmp(prev2_wd, "GEQO") == 0)
1212 char *my_list[] = {"ON", "OFF", "DEFAULT", NULL};
1214 COMPLETE_WITH_LIST(my_list);
1218 char *my_list[] = {"DEFAULT", NULL};
1220 COMPLETE_WITH_LIST(my_list);
1225 else if (strcasecmp(prev_wd, "TRUNCATE") == 0)
1226 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1229 else if (strcasecmp(prev_wd, "UNLISTEN") == 0)
1230 COMPLETE_WITH_QUERY("SELECT relname FROM pg_catalog.pg_listener WHERE substr(relname,1,%d)='%s' UNION SELECT '*'::name");
1233 /* If prev. word is UPDATE suggest a list of tables */
1234 else if (strcasecmp(prev_wd, "UPDATE") == 0)
1235 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1236 /* Complete UPDATE <table> with "SET" */
1237 else if (strcasecmp(prev2_wd, "UPDATE") == 0)
1238 COMPLETE_WITH_CONST("SET");
1241 * If the previous word is SET (and it wasn't caught above as the
1242 * _first_ word) the word before it was (hopefully) a table name and
1243 * we'll now make a list of attributes.
1245 else if (strcasecmp(prev_wd, "SET") == 0)
1246 COMPLETE_WITH_ATTR(prev2_wd);
1249 else if (strcasecmp(prev_wd, "VACUUM") == 0)
1250 COMPLETE_WITH_QUERY("SELECT relname FROM pg_catalog.pg_class WHERE relkind='r' and substr(relname,1,%d)='%s' and pg_catalog.pg_table_is_visible(oid) UNION SELECT 'FULL'::name UNION SELECT 'ANALYZE'::name");
1251 else if (strcasecmp(prev2_wd, "VACUUM") == 0 &&
1252 (strcasecmp(prev_wd, "FULL") == 0 ||
1253 strcasecmp(prev_wd, "ANALYZE") == 0))
1254 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1257 /* Simple case of the word before the where being the table name */
1258 else if (strcasecmp(prev_wd, "WHERE") == 0)
1259 COMPLETE_WITH_ATTR(prev2_wd);
1262 /* TODO: also include SRF ? */
1263 else if (strcasecmp(prev_wd, "FROM") == 0)
1264 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv);
1267 /* Backslash commands */
1268 /* TODO: \dc \dd \dl */
1269 else if (strcmp(prev_wd, "\\connect") == 0 || strcmp(prev_wd, "\\c") == 0)
1270 COMPLETE_WITH_QUERY(Query_for_list_of_databases);
1271 else if (strcmp(prev_wd, "\\d") == 0 || strcmp(prev_wd, "\\d+") == 0)
1272 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisv);
1273 else if (strcmp(prev_wd, "\\da") == 0)
1274 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates);
1275 else if (strcmp(prev_wd, "\\dD") == 0)
1276 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains);
1277 else if (strcmp(prev_wd, "\\df") == 0 || strcmp(prev_wd, "\\df+") == 0)
1278 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions);
1279 else if (strcmp(prev_wd, "\\di") == 0 || strcmp(prev_wd, "\\di+") == 0)
1280 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
1281 else if (strcmp(prev_wd, "\\dn") == 0)
1282 COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
1283 else if (strcmp(prev_wd, "\\dp") == 0 || strcmp(prev_wd, "\\z") == 0)
1284 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv);
1285 else if (strcmp(prev_wd, "\\ds") == 0 || strcmp(prev_wd, "\\ds+") == 0)
1286 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
1287 else if (strcmp(prev_wd, "\\dS") == 0 || strcmp(prev_wd, "\\dS+") == 0)
1288 COMPLETE_WITH_QUERY(Query_for_list_of_system_relations);
1289 else if (strcmp(prev_wd, "\\dt") == 0 || strcmp(prev_wd, "\\dt+") == 0)
1290 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
1291 else if (strcmp(prev_wd, "\\dT") == 0 || strcmp(prev_wd, "\\dT+") == 0)
1292 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
1293 else if (strcmp(prev_wd, "\\du") == 0)
1294 COMPLETE_WITH_QUERY(Query_for_list_of_users);
1295 else if (strcmp(prev_wd, "\\dv") == 0 || strcmp(prev_wd, "\\dv+") == 0)
1296 COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views);
1297 else if (strcmp(prev_wd, "\\encoding") == 0)
1298 COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
1299 else if (strcmp(prev_wd, "\\h") == 0 || strcmp(prev_wd, "\\help") == 0)
1300 COMPLETE_WITH_LIST(sql_commands);
1301 else if (strcmp(prev_wd, "\\pset") == 0)
1303 char *my_list[] = {"format", "border", "expanded",
1304 "null", "fieldsep", "tuples_only", "title", "tableattr", "pager",
1307 COMPLETE_WITH_LIST(my_list);
1309 else if (strcmp(prev_wd, "\\cd") == 0 ||
1310 strcmp(prev_wd, "\\e") == 0 || strcmp(prev_wd, "\\edit") == 0 ||
1311 strcmp(prev_wd, "\\g") == 0 ||
1312 strcmp(prev_wd, "\\i") == 0 || strcmp(prev_wd, "\\include") == 0 ||
1313 strcmp(prev_wd, "\\o") == 0 || strcmp(prev_wd, "\\out") == 0 ||
1314 strcmp(prev_wd, "\\s") == 0 ||
1315 strcmp(prev_wd, "\\w") == 0 || strcmp(prev_wd, "\\write") == 0
1317 matches = completion_matches(text, filename_completion_function);
1321 * Finally, we look through the list of "things", such as TABLE, INDEX
1322 * and check if that was the previous word. If so, execute the query
1323 * to get a list of them.
1329 for (i = 0; words_after_create[i].name; i++)
1330 if (strcasecmp(prev_wd, words_after_create[i].name) == 0)
1332 if (words_after_create[i].with_schema == WITH_SCHEMA)
1333 COMPLETE_WITH_SCHEMA_QUERY(words_after_create[i].query);
1335 COMPLETE_WITH_QUERY(words_after_create[i].query);
1342 * If we still don't have anything to match we have to fabricate some
1343 * sort of default list. If we were to just return NULL, readline
1344 * automatically attempts filename completion, and that's usually no
1347 if (matches == NULL)
1349 COMPLETE_WITH_CONST("");
1350 #ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
1351 rl_completion_append_character = '\0';
1362 /* Return our Grand List O' Matches */
1368 /* GENERATOR FUNCTIONS
1370 These functions do all the actual work of completing the input. They get
1371 passed the text so far and the count how many times they have been called so
1372 far with the same text.
1373 If you read the above carefully, you'll see that these don't get called
1374 directly but through the readline interface.
1375 The return value is expected to be the full completion of the text, going
1376 through a list each time, or NULL if there are no more matches. The string
1377 will be free()'d be readline, so you must run it through strdup() or
1378 something of that sort.
1381 /* This one gives you one from a list of things you can put after CREATE or DROP
1385 create_command_generator(const char *text, int state)
1387 static int list_index,
1391 /* If this is the first time for this completion, init some values */
1395 string_length = strlen(text);
1398 /* find something that matches */
1399 while ((name = words_after_create[list_index++].name))
1400 if (strncasecmp(name, text, string_length) == 0)
1401 return xstrdup(name);
1403 /* if nothing matches, return NULL */
1408 /* The following two functions are wrappers for _complete_from_query */
1411 complete_from_query(const char *text, int state)
1413 return _complete_from_query(0, text, state);
1417 complete_from_schema_query(const char *text, int state)
1419 return _complete_from_query(1, text, state);
1423 /* This creates a list of matching things, according to a query pointed to
1424 by completion_charp.
1425 The query can be one of two kinds:
1426 - A simple query which must contain a %d and a %s, which will be replaced
1427 by the string length of the text and the text itself. The query may also
1428 have another %s in it, which will be replaced by the value of
1429 completion_info_charp.
1431 - A schema query used for completion of both schema and relation names;
1432 these are more complex and must contain in the following order:
1433 %d %s %d %s %d %s %s %d %s
1434 where %d is the string length of the text and %s the text itself.
1436 It is assumed that strings should be escaped to become SQL literals
1437 (that is, what is in the query is actually ... '%s' ...)
1439 See top of file for examples of both kinds of query.
1443 _complete_from_query(int is_schema_query, const char *text, int state)
1445 static int list_index,
1447 static PGresult *result = NULL;
1450 * If this is the first time for this completion, we fetch a list of
1451 * our "things" from the backend.
1455 char query_buffer[BUF_SIZE];
1460 string_length = strlen(text);
1462 /* Free any prior result */
1466 /* Need to have a query */
1467 if (completion_charp == NULL)
1470 /* Set up suitably-escaped copies of textual inputs */
1473 e_text = (char *) malloc(strlen(text) * 2 + 1);
1476 PQescapeString(e_text, text, strlen(text));
1481 if (completion_info_charp)
1483 e_info_charp = (char *)
1484 malloc(strlen(completion_info_charp) * 2 + 1);
1491 PQescapeString(e_info_charp, completion_info_charp,
1492 strlen(completion_info_charp));
1495 e_info_charp = NULL;
1497 if (is_schema_query)
1499 if (snprintf(query_buffer, BUF_SIZE, completion_charp,
1500 string_length, e_text,
1501 string_length, e_text,
1502 string_length, e_text,
1504 string_length, e_text,
1505 string_length, e_text) == -1)
1506 ERROR_QUERY_TOO_LONG;
1508 result = exec_query(query_buffer);
1512 if (snprintf(query_buffer, BUF_SIZE, completion_charp,
1513 string_length, e_text, e_info_charp) == -1)
1514 ERROR_QUERY_TOO_LONG;
1516 result = exec_query(query_buffer);
1525 /* Find something that matches */
1526 if (result && PQresultStatus(result) == PGRES_TUPLES_OK)
1530 while (list_index < PQntuples(result) &&
1531 (item = PQgetvalue(result, list_index++, 0)))
1532 if (strncasecmp(text, item, string_length) == 0)
1533 return xstrdup(item);
1536 /* If nothing matches, free the db structure and return null */
1543 /* This function returns in order one of a fixed, NULL pointer terminated list
1544 of strings (if matching). This can be used if there are only a fixed number
1545 SQL words that can appear at certain spot.
1548 complete_from_list(const char *text, int state)
1550 static int string_length,
1553 static bool casesensitive;
1556 /* need to have a list */
1557 #ifdef USE_ASSERT_CHECKING
1558 assert(completion_charpp);
1561 /* Initialization */
1565 string_length = strlen(text);
1566 casesensitive = true;
1570 while ((item = completion_charpp[list_index++]))
1572 /* First pass is case sensitive */
1573 if (casesensitive && strncmp(text, item, string_length) == 0)
1576 return xstrdup(item);
1579 /* Second pass is case insensitive, don't bother counting matches */
1580 if (!casesensitive && strncasecmp(text, item, string_length) == 0)
1581 return xstrdup(item);
1585 * No matches found. If we're not case insensitive already, lets
1586 * switch to being case insensitive and try again
1588 if (casesensitive && matches == 0)
1590 casesensitive = false;
1593 return (complete_from_list(text, state));
1596 /* If no more matches, return null. */
1601 /* This function returns one fixed string the first time even if it doesn't
1602 match what's there, and nothing the second time. This should be used if there
1603 is only one possibility that can appear at a certain spot, so misspellings
1604 will be overwritten.
1605 The string to be passed must be in completion_charp.
1608 complete_from_const(const char *text, int state)
1610 (void) text; /* We don't care about what was entered
1613 #ifdef USE_ASSERT_CHECKING
1614 assert(completion_charp);
1617 return xstrdup(completion_charp);
1624 /* HELPER FUNCTIONS */
1627 /* Execute a query and report any errors. This should be the preferred way of
1628 talking to the database in this file.
1629 Note that the query passed in here must not have a semicolon at the end
1630 because we need to append LIMIT xxx.
1633 exec_query(char *query)
1636 char query_buffer[BUF_SIZE];
1638 if (query == NULL || !pset.db || PQstatus(pset.db) != CONNECTION_OK)
1640 #ifdef USE_ASSERT_CHECKING
1641 assert(query[strlen(query) - 1] != ';');
1644 if (snprintf(query_buffer, BUF_SIZE, "%s LIMIT %d;",
1645 query, completion_max_records) == -1)
1647 ERROR_QUERY_TOO_LONG;
1651 result = PQexec(pset.db, query);
1653 if (result != NULL && PQresultStatus(result) != PGRES_TUPLES_OK)
1656 psql_error("tab completion: %s failed - %s\n",
1657 query, PQresStatus(PQresultStatus(result)));
1668 /* Return the word (space delimited) before point. Set skip > 0 to skip that
1669 many words; e.g. skip=1 finds the word before the previous one.
1672 previous_word(int point, int skip)
1682 /* first we look for a space before the current word */
1683 for (i = point; i >= 0; i--)
1684 if (rl_line_buffer[i] == ' ')
1687 /* now find the first non-space which then constitutes the end */
1689 if (rl_line_buffer[i] != ' ')
1696 * If no end found we return null, because there is no word before
1703 * Otherwise we now look for the start. The start is either the
1704 * last character before any space going backwards from the end,
1705 * or it's simply character 0
1707 for (start = end; start > 0; start--)
1709 if (rl_line_buffer[start] == '"')
1710 inquotes = !inquotes;
1711 if ((rl_line_buffer[start - 1] == ' ') && inquotes == 0)
1719 s = (char *) malloc(end - start + 2);
1722 psql_error("out of memory\n");
1723 if (!pset.cur_cmd_interactive)
1729 strncpy(s, &rl_line_buffer[start], end - start + 1);
1730 s[end - start + 1] = '\0';
1740 * Surround a string with single quotes. This works for both SQL and
1741 * psql internal. Currently disable because it is reported not to
1742 * cooperate with certain versions of readline.
1745 quote_file_name(char *text, int match_type, char *quote_pointer)
1750 (void) quote_pointer; /* not used */
1752 length = strlen(text) +(match_type == SINGLE_MATCH ? 3 : 2);
1755 strcpy(s + 1, text);
1756 if (match_type == SINGLE_MATCH)
1757 s[length - 2] = '\'';
1758 s[length - 1] = '\0';
1765 dequote_file_name(char *text, char quote_char)
1771 return xstrdup(text);
1773 length = strlen(text);
1774 s = malloc(length - 2 + 1);
1775 strncpy(s, text +1, length - 2);
1782 #endif /* USE_READLINE */