1 /*-------------------------------------------------------------------------
4 * an interactive front-end to postgreSQL
6 * Copyright (c) 1996, Regents of the University of California
10 * $Header: /cvsroot/pgsql/src/bin/psql/Attic/psql.c,v 1.55 1997/01/26 17:27:32 scrappy Exp $
12 *-------------------------------------------------------------------------
18 #include <sys/types.h>
19 #include <sys/param.h> /* for MAXPATHLEN */
27 #include "stringutils.h"
33 #ifdef HAVE_LIBREADLINE
34 # ifdef HAVE_READLINE_H
35 # include <readline.h>
36 # if defined(HAVE_HISTORY) || defined(HAVE_LIBHISTORY)
40 # include <readline/readline.h>
41 # if defined(HAVE_HISTORY) || defined(HAVE_LIBHISTORY)
42 # include <readline/history.h>
49 #define MAX_QUERY_BUFFER 20000
51 #define COPYBUFSIZ 8192
53 #define DEFAULT_FIELD_SEP "|"
54 #define DEFAULT_EDITOR "vi"
55 #define DEFAULT_SHELL "/bin/sh"
57 typedef struct _psqlSettings {
58 PGconn *db; /* connection to backend */
59 FILE *queryFout; /* where to send the query results */
60 PQprintOpt opt; /* options to be passed to PQprint */
61 char *prompt; /* prompt to display */
62 char *gfname; /* one-shot file output argument for \g */
63 bool notty; /* input or output is not a tty */
64 bool pipe; /* queryFout is from a popen() */
65 bool echoQuery; /* echo the query before sending it */
66 bool quiet; /* run quietly, no messages, no promt */
67 bool singleStep; /* prompt before for each query */
68 bool singleLineMode; /* query terminated by newline */
69 bool useReadline;/* use libreadline routines */
72 /* declarations for functions in this file */
73 static void usage(char *progname);
74 static void slashUsage();
75 static void handleCopyOut(PGresult * res, bool quiet, FILE * copystream);
77 handleCopyIn(PGresult * res, const bool mustprompt,
79 static int tableList(PsqlSettings * ps, bool deep_tablelist);
80 static int tableDesc(PsqlSettings * ps, char *table);
82 char *gets_noreadline(char *prompt, FILE * source);
83 char *gets_readline(char *prompt, FILE * source);
84 char *gets_fromFile(char *prompt, FILE * source);
85 int listAllDbs(PsqlSettings * settings);
87 SendQuery(bool * success_p, PsqlSettings * settings, const char *query,
88 const bool copy_in, const bool copy_out, FILE * copystream);
90 HandleSlashCmds(PsqlSettings * settings,
93 int MainLoop(PsqlSettings * settings, FILE * source);
94 /* probably should move this into libpq */
101 FILE *setFout(PsqlSettings * ps, char *fname);
104 * usage print out usage for command line arguments
108 usage(char *progname)
110 fprintf(stderr, "Usage: %s [options] [dbname]\n", progname);
111 fprintf(stderr, "\t -a authsvc set authentication service\n");
112 fprintf(stderr, "\t -A turn off alignment when printing out attributes\n");
113 fprintf(stderr, "\t -c query run single query (slash commands too)\n");
114 fprintf(stderr, "\t -d dbName specify database name\n");
115 fprintf(stderr, "\t -e echo the query sent to the backend\n");
116 fprintf(stderr, "\t -f filename use file as a source of queries\n");
117 fprintf(stderr, "\t -F sep set the field separator (default is " ")\n");
118 fprintf(stderr, "\t -h host set database server host\n");
119 fprintf(stderr, "\t -H turn on html3.0 table output\n");
120 fprintf(stderr, "\t -l list available databases\n");
121 fprintf(stderr, "\t -n don't use readline library\n");
122 fprintf(stderr, "\t -o filename send output to filename or (|pipe)\n");
123 fprintf(stderr, "\t -p port set port number\n");
124 fprintf(stderr, "\t -q run quietly (no messages, no prompts)\n");
125 fprintf(stderr, "\t -s single step mode (prompts for each query)\n");
126 fprintf(stderr, "\t -S single line mode (i.e. query terminated by newline)\n");
127 fprintf(stderr, "\t -t turn off printing of headings and row count\n");
128 fprintf(stderr, "\t -T html set html3.0 table command options (cf. -H)\n");
129 fprintf(stderr, "\t -x turn on expanded output (field names on left)\n");
134 * slashUsage print out usage for the backslash commands
140 return f ? "on" : "off";
144 slashUsage(PsqlSettings * ps)
146 fprintf(stderr, " \\? -- help\n");
147 fprintf(stderr, " \\a -- toggle field-alignment (currenty %s)\n", on(ps->opt.align));
148 fprintf(stderr, " \\C [<captn>] -- set html3 caption (currently '%s')\n", ps->opt.caption ? ps->opt.caption : "");
149 fprintf(stderr, " \\connect <dbname> -- connect to new database (currently '%s')\n", PQdb(ps->db));
150 fprintf(stderr, " \\copy {<table> to <file> | <file> from <table>}\n");
151 fprintf(stderr, " \\d [<table>] -- list tables in database or columns in <table>, * for all\n");
152 fprintf(stderr, " \\e [<fname>] -- edit the current query buffer or <fname>, \\E execute too\n");
153 fprintf(stderr, " \\f [<sep>] -- change field separater (currently '%s')\n", ps->opt.fieldSep);
154 fprintf(stderr, " \\g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]\n");
155 fprintf(stderr, " \\h [<cmd>] -- help on syntax of sql commands, * for all commands\n");
156 fprintf(stderr, " \\H -- toggle html3 output (currently %s)\n", on(ps->opt.html3));
157 fprintf(stderr, " \\i <fname> -- read and execute queries from filename\n");
158 fprintf(stderr, " \\l -- list all databases\n");
159 fprintf(stderr, " \\m -- toggle monitor-like table display (currently %s)\n", on(ps->opt.standard));
160 fprintf(stderr, " \\o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe\n");
161 fprintf(stderr, " \\p -- print the current query buffer\n");
162 fprintf(stderr, " \\q -- quit\n");
163 fprintf(stderr, " \\r -- reset(clear) the query buffer\n");
164 fprintf(stderr, " \\s [<fname>] -- print history or save it in <fname>\n");
165 fprintf(stderr, " \\t -- toggle table headings and row count (currently %s)\n", on(ps->opt.header));
166 fprintf(stderr, " \\T [<html>] -- set html3.0 <table ...> options (currently '%s')\n", ps->opt.tableOpt ? ps->opt.tableOpt : "");
167 fprintf(stderr, " \\x -- toggle expanded output (currently %s)\n", on(ps->opt.expanded));
168 fprintf(stderr, " \\! [<cmd>] -- shell escape or command\n");
172 PSQLexec(PsqlSettings * ps, char *query)
174 PGresult *res = PQexec(ps->db, query);
176 fputs(PQerrorMessage(ps->db), stderr);
178 if (PQresultStatus(res) == PGRES_COMMAND_OK ||
179 PQresultStatus(res) == PGRES_TUPLES_OK)
182 fputs(PQerrorMessage(ps->db), stderr);
190 * list all the databases in the system returns 0 if all went well
196 listAllDbs(PsqlSettings * ps)
199 char *query = "select * from pg_database;";
201 if (!(results = PSQLexec(ps, query)))
204 PQprint(ps->queryFout,
213 * List The Database Tables returns 0 if all went well
217 tableList(PsqlSettings * ps, bool deep_tablelist)
228 strcat(listbuf, "SELECT usename, relname, relkind, relhasrules");
229 strcat(listbuf, " FROM pg_class, pg_user ");
230 strcat(listbuf, "WHERE ( relkind = 'r' OR relkind = 'i') ");
231 strcat(listbuf, " and relname !~ '^pg_'");
232 strcat(listbuf, " and relname !~ '^Inv[0-9]+'");
234 * the usesysid = relowner won't work on stock 1.0 dbs, need to add in
235 * the int4oideq function
237 strcat(listbuf, " and usesysid = relowner");
238 strcat(listbuf, " ORDER BY relname ");
239 if (!(res = PSQLexec(ps, listbuf)))
242 /* first, print out the attribute names */
243 nColumns = PQntuples(res);
245 if (deep_tablelist) {
246 /* describe everything here */
248 table = (char **) malloc(nColumns * sizeof(char *));
252 /* load table table */
253 for (i = 0; i < nColumns; i++) {
254 table[i] = (char *) malloc(PQgetlength(res, i, 1) * sizeof(char) + 1);
255 if (table[i] == NULL)
257 strcpy(table[i], PQgetvalue(res, i, 1));
261 for (i = 0; i < nColumns; i++) {
262 tableDesc(ps, table[i]);
266 /* Display the information */
268 printf("\nDatabase = %s\n", PQdb(ps->db));
269 printf(" +------------------+----------------------------------+----------+\n");
270 printf(" | Owner | Relation | Type |\n");
271 printf(" +------------------+----------------------------------+----------+\n");
273 /* next, print out the instances */
274 for (i = 0; i < PQntuples(res); i++) {
275 printf(" | %-16.16s", PQgetvalue(res, i, 0));
276 printf(" | %-32.32s | ", PQgetvalue(res, i, 1));
277 rk = PQgetvalue(res, i, 2);
278 rr = PQgetvalue(res, i, 3);
279 if (strcmp(rk, "r") == 0)
280 printf("%-8.8s |", (rr[0] == 't') ? "view?" : "table");
282 printf("%-8.8s |", "index");
285 printf(" +------------------+----------------------------------+----------+\n");
291 fprintf(stderr, "Couldn't find any tables!\n");
299 * Describe the columns in a database table. returns 0 if all went well
304 tableDesc(PsqlSettings * ps, char *table)
314 /* Build the query */
317 strcat(descbuf, "SELECT a.attnum, a.attname, t.typname, a.attlen");
318 strcat(descbuf, " FROM pg_class c, pg_attribute a, pg_type t ");
319 strcat(descbuf, " WHERE c.relname = '");
320 strcat(descbuf, table);
321 strcat(descbuf, "'");
322 strcat(descbuf, " and a.attnum > 0 ");
323 strcat(descbuf, " and a.attrelid = c.oid ");
324 strcat(descbuf, " and a.atttypid = t.oid ");
325 strcat(descbuf, " ORDER BY attnum ");
326 if (!(res = PSQLexec(ps, descbuf)))
328 /* first, print out the attribute names */
329 nColumns = PQntuples(res);
332 * * Display the information
335 printf("\nTable = %s\n", table);
336 printf("+----------------------------------+----------------------------------+-------+\n");
337 printf("| Field | Type | Length|\n");
338 printf("+----------------------------------+----------------------------------+-------+\n");
340 /* next, print out the instances */
341 for (i = 0; i < PQntuples(res); i++) {
342 printf("| %-32.32s | ", PQgetvalue(res, i, 1));
343 rtype = PQgetvalue(res, i, 2);
344 rsize = atoi(PQgetvalue(res, i, 3));
345 if (strcmp(rtype, "text") == 0) {
346 printf("%-32.32s |", rtype);
347 printf("%6s |", "var");
348 } else if (strcmp(rtype, "bpchar") == 0) {
349 printf("%-32.32s |", "(bp)char");
350 printf("%6i |", rsize > 0 ? rsize - 4 : 0);
351 } else if (strcmp(rtype, "varchar") == 0) {
352 printf("%-32.32s |", rtype);
353 printf("%6i |", rsize > 0 ? rsize - 4 : 0);
355 /* array types start with an underscore */
357 printf("%-32.32s |", rtype);
360 newname = malloc(strlen(rtype) + 2);
361 strcpy(newname, rtype + 1);
362 strcat(newname, "[]");
363 printf("%-32.32s |", newname);
367 printf("%6i |", rsize);
369 printf("%6s |", "var");
373 printf("+----------------------------------+----------------------------------+-------+\n");
379 fprintf(stderr, "Couldn't find table %s!\n", table);
384 typedef char *(*READ_ROUTINE) (char *prompt, FILE * source);
387 * gets_noreadline prompt source gets a line of input without calling
388 * readline, the source is ignored
391 gets_noreadline(char *prompt, FILE * source)
393 fputs(prompt, stdout);
395 return (gets_fromFile(prompt, stdin));
399 * gets_readline prompt source the routine to get input from GNU readline(),
400 * the source is ignored the prompt argument is used as the prompting string
403 gets_readline(char *prompt, FILE * source)
406 #ifdef HAVE_LIBREADLINE
407 s = readline(prompt);
410 printf("%s", prompt);
411 s = fgets(buf, 500, stdin);
419 * gets_fromFile prompt source
421 * the routine to read from a file, the prompt argument is ignored the source
422 * argument is a FILE *
425 gets_fromFile(char *prompt, FILE * source)
430 line = malloc(MAX_QUERY_BUFFER + 1);
432 /* read up to MAX_QUERY_BUFFER characters */
433 if (fgets(line, MAX_QUERY_BUFFER, source) == NULL)
436 line[MAX_QUERY_BUFFER - 1] = '\0';
438 if (len == MAX_QUERY_BUFFER) {
439 fprintf(stderr, "line read exceeds maximum length. Truncating at %d\n",
446 * SendQuery: send the query string to the backend return *success_p = 1 if
447 * the query executed successfully returns *success_p = 0 otherwise
450 SendQuery(bool * success_p, PsqlSettings * settings, const char *query,
451 const bool copy_in, const bool copy_out, FILE * copystream)
457 if (settings->singleStep)
458 fprintf(stdout, "\n**************************************"
459 "*****************************************\n");
461 if (settings->echoQuery || settings->singleStep) {
462 fprintf(stderr, "QUERY: %s\n", query);
465 if (settings->singleStep) {
466 fprintf(stdout, "\n**************************************"
467 "*****************************************\n");
469 printf("\npress return to continue ..\n");
470 gets_fromFile("", stdin);
472 results = PQexec(settings->db, query);
473 if (results == NULL) {
474 fprintf(stderr, "%s", PQerrorMessage(settings->db));
477 switch (PQresultStatus(results)) {
478 case PGRES_TUPLES_OK:
479 if (settings->gfname) {
480 PsqlSettings ps = *settings;
482 ps.queryFout = stdout;
483 fp = setFout(&ps, settings->gfname);
484 if (!fp || fp == stdout) {
496 free(settings->gfname);
497 settings->gfname = NULL;
501 PQprint(settings->queryFout,
504 fflush(settings->queryFout);
508 case PGRES_EMPTY_QUERY:
511 case PGRES_COMMAND_OK:
513 if (!settings->quiet)
514 fprintf(stdout, "%s\n", PQcmdStatus(results));
519 handleCopyOut(results, settings->quiet, copystream);
521 if (!settings->quiet)
522 fprintf(stdout, "Copy command returns...\n");
524 handleCopyOut(results, settings->quiet, stdout);
530 handleCopyIn(results, false, copystream);
532 handleCopyIn(results, !settings->quiet, stdin);
534 case PGRES_NONFATAL_ERROR:
535 case PGRES_FATAL_ERROR:
536 case PGRES_BAD_RESPONSE:
538 fprintf(stderr, "%s", PQerrorMessage(settings->db));
542 if (PQstatus(settings->db) == CONNECTION_BAD) {
544 "We have lost the connection to the backend, so "
545 "further processing is impossible. "
547 exit(2); /* we are out'ta here */
549 /* check for asynchronous returns */
550 notify = PQnotifies(settings->db);
553 "ASYNC NOTIFY of '%s' from backend pid '%d' received\n",
554 notify->relname, notify->be_pid);
563 editFile(char *fname)
567 editorName = getenv("EDITOR");
569 editorName = DEFAULT_EDITOR;
570 sys = malloc(strlen(editorName) + strlen(fname) + 32 + 1);
575 sprintf(sys, "exec '%s' '%s'", editorName, fname);
581 toggle(PsqlSettings * settings, bool * sw, char *msg)
584 if (!settings->quiet)
585 fprintf(stderr, "turned %s %s\n", on(*sw), msg);
592 unescape(char *dest, const char *source)
594 /*-----------------------------------------------------------------------------
595 Return as the string <dest> the value of string <source> with escape
596 sequences turned into the bytes they represent.
597 -----------------------------------------------------------------------------*/
599 bool esc; /* Last character we saw was the escape
602 esc = false; /* Haven't seen escape character yet */
603 for (p = (char *) source; *p; p++) {
604 char c; /* Our output character */
627 } else if (*p == '\\') {
629 c = ' '; /* meaningless, but compiler doesn't know
638 *dest = '\0'; /* Terminating null character */
644 parse_slash_copy(const char *args, char *table, const int table_len,
645 char *file, const int file_len,
646 bool * from_p, bool * error_p)
651 * A copy of the \copy command arguments, except that we modify it as we
652 * parse to suit our parsing needs.
654 char *table_tok, *fromto_tok;
656 strncpy(work_args, args, sizeof(work_args));
657 work_args[sizeof(work_args) - 1] = '\0';
659 *error_p = false; /* initial assumption */
661 table_tok = strtok(work_args, " ");
662 if (table_tok == NULL) {
663 fprintf(stderr, "\\copy needs arguments.\n");
666 strncpy(table, table_tok, table_len);
667 file[table_len - 1] = '\0';
669 fromto_tok = strtok(NULL, " ");
670 if (fromto_tok == NULL) {
671 fprintf(stderr, "'FROM' or 'TO' must follow table name.\n");
674 if (strcasecmp(fromto_tok, "from") == 0)
676 else if (strcasecmp(fromto_tok, "to") == 0)
680 "Unrecognized token found where "
681 "'FROM' or 'TO' expected: '%s'.\n",
688 file_tok = strtok(NULL, " ");
689 if (file_tok == NULL) {
690 fprintf(stderr, "A file pathname must follow '%s'.\n",
694 strncpy(file, file_tok, file_len);
695 file[file_len - 1] = '\0';
696 if (strtok(NULL, " ") != NULL) {
698 "You have extra tokens after the filename.\n");
710 do_copy(const char *args, PsqlSettings * settings)
712 /*---------------------------------------------------------------------------
713 Execute a \copy command (frontend copy). We have to open a file, then
714 submit a COPY query to the backend and either feed it data from the
715 file or route its response into the file.
717 We do a text copy with default (tab) column delimiters. Some day, we
718 should do all the things a backend copy can do.
720 ----------------------------------------------------------------------------*/
722 /* The COPY command we send to the back end */
724 /* The direction of the copy is from a file to a table. */
725 char file[MAXPATHLEN + 1];
726 /* The pathname of the file from/to which we copy */
727 char table[NAMEDATALEN + 1];
728 /* The name of the table from/to which we copy */
730 /* The \c command has invalid syntax */
733 parse_slash_copy(args, table, sizeof(table), file, sizeof(file),
734 &from, &syntax_error);
737 strcpy(query, "COPY ");
738 strcat(query, table);
741 strcat(query, " FROM stdin");
743 strcat(query, " TO stdout");
746 copystream = fopen(file, "r");
748 copystream = fopen(file, "w");
750 if (copystream == NULL)
752 "Unable to open file %s which to copy, errno = %s (%d).",
753 from ? "from" : "to", strerror(errno), errno);
755 bool success; /* The query succeeded at the backend */
757 SendQuery(&success, settings, query, from, !from, copystream);
759 if (!settings->quiet) {
761 fprintf(stdout, "Successfully copied.\n");
763 fprintf(stdout, "Copy failed.\n");
771 do_connect(const char *new_dbname, PsqlSettings * settings)
774 char *dbname = PQdb(settings->db);
776 fprintf(stderr, "\\connect must be followed by a database name\n");
778 PGconn *olddb = settings->db;
780 printf("closing connection to database: %s\n", dbname);
781 settings->db = PQsetdb(PQhost(olddb), PQport(olddb),
782 NULL, NULL, new_dbname);
783 printf("connecting to new database: %s\n", new_dbname);
784 if (PQstatus(settings->db) == CONNECTION_BAD) {
785 fprintf(stderr, "%s\n", PQerrorMessage(settings->db));
786 printf("reconnecting to %s\n", dbname);
787 settings->db = PQsetdb(PQhost(olddb), PQport(olddb),
789 if (PQstatus(settings->db) == CONNECTION_BAD) {
791 "could not reconnect to %s. exiting\n", dbname);
796 free(settings->prompt);
797 settings->prompt = malloc(strlen(PQdb(settings->db)) + 10);
798 sprintf(settings->prompt, "%s%s", PQdb(settings->db), PROMPT);
805 do_edit(const char *filename_arg, char *query, int *status_p)
812 const int ql = strlen(query);
816 fname = (char *) filename_arg;
819 sprintf(tmp, "/tmp/psql.%ld.%ld", (long) geteuid(), (long) getpid());
823 if ((fd = open(tmp, O_EXCL | O_CREAT | O_WRONLY, 0600)) == -1) {
827 if (query[ql - 1] != '\n')
829 if (write(fd, query, ql) != ql) {
846 if ((fd = open(fname, O_RDONLY)) == -1) {
852 if ((cc = read(fd, query, MAX_QUERY_BUFFER)) == -1) {
873 do_help(const char *topic)
877 char left_center_right; /* Which column we're
879 int i; /* Index into QL_HELP[] */
881 printf("type \\h <cmd> where <cmd> is one of the following:\n");
883 left_center_right = 'L';/* Start with left column */
885 while (QL_HELP[i].cmd != NULL) {
886 switch (left_center_right) {
888 printf(" %-25s", QL_HELP[i].cmd);
889 left_center_right = 'C';
892 printf("%-25s", QL_HELP[i].cmd);
893 left_center_right = 'R';
896 printf("%-25s\n", QL_HELP[i].cmd);
897 left_center_right = 'L';
902 if (left_center_right != 'L')
904 printf("type \\h * for a complete description of all commands\n");
906 int i; /* Index into QL_HELP[] */
907 bool help_found; /* We found the help he asked for */
909 help_found = false; /* Haven't found it yet */
910 for (i = 0; QL_HELP[i].cmd; i++) {
911 if (strcmp(QL_HELP[i].cmd, topic) == 0 ||
912 strcmp(topic, "*") == 0) {
914 printf("Command: %s\n", QL_HELP[i].cmd);
915 printf("Description: %s\n", QL_HELP[i].help);
917 printf("%s\n", QL_HELP[i].syntax);
922 printf("command not found, "
923 "try \\h with no arguments to see available help\n");
930 do_shell(const char *command)
937 shellName = getenv("SHELL");
938 if (shellName == NULL)
939 shellName = DEFAULT_SHELL;
940 sys = malloc(strlen(shellName) + 16);
945 sprintf(sys, "exec %s", shellName);
957 * Handles all the different commands that start with \ db_ptr is a pointer to
958 * the TgDb* structure line is the current input line prompt_ptr is a pointer
959 * to the prompt string, a pointer is used because the prompt can be used
960 * with a connection to a new database returns a status: 0 - send currently
961 * constructed query to backend (i.e. we got a \g) 1 - skip processing of
962 * this line, continue building up query 2 - terminate processing of this
963 * query entirely, 3 - new query supplied by edit
966 HandleSlashCmds(PsqlSettings * settings,
973 * Pointer inside the <cmd> string to the argument of the slash command,
974 * assuming it is a one-character slash command. If it's not a
975 * one-character command, this is meaningless.
979 * Pointer inside the <cmd> string to the argument of the slash command
980 * assuming it's not a one-character command. If it's a one-character
981 * command, this is meaningless.
985 * String: value of the slash command, less the slash and with escape
989 /* Offset within <cmd> of first blank */
991 cmd = malloc(strlen(line)); /* unescaping better not make string grow. */
993 unescape(cmd, line + 1); /* sets cmd string */
996 * Originally, there were just single character commands. Now, we define
997 * some longer, friendly commands, but we have to keep the old single
998 * character commands too. \c used to be what \connect is now.
999 * Complicating matters is the fact that with the single-character
1000 * commands, you can start the argument right after the single character,
1001 * so "\copy" would mean "connect to database named 'opy'".
1004 if (strlen(cmd) > 1)
1005 optarg = cmd + 1 + strspn(cmd + 1, " \t");
1009 blank_loc = strcspn(cmd, " \t");
1013 optarg2 = cmd + blank_loc + strspn(cmd + blank_loc, " \t");
1017 case 'a': /* toggles to align fields on output */
1018 toggle(settings, &settings->opt.align, "field alignment");
1020 case 'C': /* define new caption */
1021 if (settings->opt.caption)
1022 free(settings->opt.caption);
1025 if (settings->opt.caption)
1026 free(settings->opt.caption);
1027 settings->opt.caption = NULL;
1029 else if (!(settings->opt.caption = strdup(optarg))) {
1035 if (strncmp(cmd, "copy ", strlen("copy ")) == 0)
1036 do_copy(optarg2, settings);
1037 else if (strncmp(cmd, "connect ", strlen("connect ")) == 0)
1038 do_connect(optarg2, settings);
1040 do_connect(optarg, settings);
1043 case 'd': /* \d describe tables or columns in a table */
1045 tableList(settings, 0);
1048 if (strcmp(optarg, "*") == 0) {
1049 tableList(settings, 0);
1050 tableList(settings, 1);
1052 tableDesc(settings, optarg);
1055 case 'e': /* edit */
1057 do_edit(optarg, query, &status);
1063 static char *lastfile;
1064 struct stat st, st2;
1068 lastfile = malloc(strlen(optarg + 1));
1073 strcpy(lastfile, optarg);
1074 } else if (!lastfile) {
1075 fprintf(stderr, "\\r must be followed by a file name initially\n");
1078 stat(lastfile, &st);
1080 if ((stat(lastfile, &st2) == -1) || ((fd = fopen(lastfile, "r")) == NULL)) {
1084 if (st2.st_mtime == st.st_mtime) {
1085 if (!settings->quiet)
1086 fprintf(stderr, "warning: %s not modified. query not executed\n", lastfile);
1090 MainLoop(settings, fd);
1096 char *fs = DEFAULT_FIELD_SEP;
1099 if (settings->opt.fieldSep)
1100 free(settings->opt.fieldSep);
1101 if (!(settings->opt.fieldSep = strdup(fs))) {
1105 if (!settings->quiet)
1106 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1109 case 'g': /* \g means send query */
1111 settings->gfname = NULL;
1112 else if (!(settings->gfname = strdup(optarg))) {
1118 case 'h': /* help */
1123 case 'i': /* \i is include file */
1128 fprintf(stderr, "\\i must be followed by a file name\n");
1131 if ((fd = fopen(optarg, "r")) == NULL) {
1132 fprintf(stderr, "file named %s could not be opened\n", optarg);
1135 MainLoop(settings, fd);
1139 case 'l': /* \l is list database */
1140 listAllDbs(settings);
1143 if (toggle(settings, &settings->opt.html3, "HTML3.0 tabular output"))
1144 settings->opt.standard = 0;
1147 setFout(settings, optarg);
1151 fputs(query, stdout);
1152 fputc('\n', stdout);
1155 case 'q': /* \q is quit */
1158 case 'r': /* reset(clear) the buffer */
1160 if (!settings->quiet)
1161 fprintf(stderr, "buffer reset(cleared)\n");
1163 case 's': /* \s is save history to a file */
1165 optarg = "/dev/tty";
1167 if (write_history(optarg) != 0)
1168 fprintf(stderr, "cannot write history to %s\n", optarg);
1171 case 'm': /* monitor like type-setting */
1172 if (toggle(settings, &settings->opt.standard, "standard SQL separaters and padding")) {
1173 settings->opt.html3 = settings->opt.expanded = 0;
1174 settings->opt.align = settings->opt.header = 1;
1175 if (settings->opt.fieldSep)
1176 free(settings->opt.fieldSep);
1177 settings->opt.fieldSep = strdup("|");
1178 if (!settings->quiet)
1179 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1181 if (settings->opt.fieldSep)
1182 free(settings->opt.fieldSep);
1183 settings->opt.fieldSep = strdup(DEFAULT_FIELD_SEP);
1184 if (!settings->quiet)
1185 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1188 case 't': /* toggle headers */
1189 toggle(settings, &settings->opt.header, "output headings and row count");
1191 case 'T': /* define html <table ...> option */
1192 if (settings->opt.tableOpt)
1193 free(settings->opt.tableOpt);
1195 settings->opt.tableOpt = NULL;
1196 else if (!(settings->opt.tableOpt = strdup(optarg))) {
1202 toggle(settings, &settings->opt.expanded, "expanded table representation");
1208 case '?': /* \? is help */
1209 slashUsage(settings);
1217 * MainLoop: main processing loop for reading lines of input and sending them
1220 * this loop is re-entrant. May be called by \i command which reads input from
1223 * db_ptr must be initialized and set
1227 MainLoop(PsqlSettings * settings, FILE * source)
1229 char *line; /* line of input */
1230 int len; /* length of the line */
1231 char query[MAX_QUERY_BUFFER]; /* multi-line query storage */
1232 int successResult = 1;
1233 int slashCmdStatus = 0;
1235 * slashCmdStatus can be: 0 - send currently constructed query to backend
1236 * (i.e. we got a \g) 1 - skip processing of this line, continue building
1237 * up query 2 - terminate processing of this query entirely 3 - new query
1241 bool querySent = false;
1243 READ_ROUTINE GetNextLine;
1245 /* We've reached the end of our command input. */
1248 bool was_bslash; /* backslash */
1253 interactive = ((source == stdin) && !settings->notty);
1255 if (settings->prompt)
1256 free(settings->prompt);
1258 malloc(strlen(PQdb(settings->db)) + strlen(PROMPT) + 1);
1259 if (settings->quiet)
1260 settings->prompt[0] = '\0';
1262 sprintf(settings->prompt, "%s%s", PQdb(settings->db), PROMPT);
1263 if (settings->useReadline) {
1267 GetNextLine = gets_readline;
1269 GetNextLine = gets_noreadline;
1271 GetNextLine = gets_fromFile;
1276 slashCmdStatus = -1; /* set default */
1278 /* main loop for getting queries and executing them */
1280 if (slashCmdStatus == 3) {
1282 line = strdup(query);
1285 if (interactive && !settings->quiet) {
1287 settings->prompt[strlen(settings->prompt)-3] = '\'';
1288 else if (query[0] != '\0' && !querySent)
1289 settings->prompt[strlen(settings->prompt)-3] = '-';
1291 settings->prompt[strlen(settings->prompt)-3] = '=';
1293 line = GetNextLine(settings->prompt, source);
1295 if (interactive && settings->useReadline && line != NULL)
1296 add_history(line); /* save non-empty lines in history */
1302 if (line == NULL) { /* No more input. Time to quit */
1303 if (!settings->quiet)
1304 printf("EOF\n"); /* Goes on prompt line */
1307 /* remove whitespaces on the right, incl. \n's */
1308 line = rightTrim(line);
1310 if (!interactive && !settings->singleStep && !settings->quiet)
1311 fprintf(stderr, "%s\n", line);
1313 if (line[0] == '\0') {
1320 if (settings->singleLineMode) {
1321 SendQuery(&success, settings, line, false, false, 0);
1322 successResult &= success;
1329 for (i = 0; i < len; i++) {
1330 if (!in_quote && line[i] == '\\') {
1331 char hold_char = line[i];
1334 if (query_start[0] != '\0') {
1335 if (query[0] != '\0') {
1336 strcat(query, "\n");
1337 strcat(query, query_start);
1339 strcpy(query, query_start);
1341 line[i] = hold_char;
1342 query_start = line + i;
1343 break; /* handle command */
1345 if (querySent && !isspace(line[i])) {
1349 if (!in_quote && was_dash && line[i] == '-') {
1350 /* print comment at top of query */
1351 if (settings->singleStep)
1352 fprintf(stdout, "%s\n", line + i - 1);
1353 line[i - 1] = '\0'; /* remove comment */
1358 if (!in_quote && !paren_level &&
1360 char hold_char = line[i + 1];
1363 if (query_start[0] != '\0') {
1364 if (query[0] != '\0') {
1365 strcat(query, "\n");
1366 strcat(query, query_start);
1368 strcpy(query, query_start);
1370 SendQuery(&success, settings, query, false, false, 0);
1371 successResult &= success;
1372 line[i + 1] = hold_char;
1373 query_start = line + i + 1;
1378 else if (line[i] == '\\')
1380 else if (line[i] == '\'')
1382 else if (!in_quote && line[i] == '(')
1384 else if (!in_quote && paren_level && line[i] == ')')
1386 else if (!in_quote && line[i] == '-')
1391 slashCmdStatus = -1;
1392 /* slash commands have to be on their own line */
1393 if (!in_quote && query_start[0] == '\\') {
1394 slashCmdStatus = HandleSlashCmds(settings,
1397 if (slashCmdStatus == 1) {
1398 if (query[0] == '\0')
1403 if (slashCmdStatus == 2) {
1407 } else if (strlen(query) + strlen(query_start) > MAX_QUERY_BUFFER) {
1408 fprintf(stderr, "query buffer max length of %d exceeded\n",
1410 fprintf(stderr, "query line ignored\n");
1412 if (query_start[0] != '\0') {
1414 if (query[0] != '\0') {
1415 strcat(query, "\n");
1416 strcat(query, query_start);
1418 strcpy(query, query_start);
1422 if (slashCmdStatus == 0) {
1423 SendQuery(&success, settings, query, false, false, 0);
1424 successResult &= success;
1427 free(line); /* free storage malloc'd by GetNextLine */
1430 return successResult;
1434 main(int argc, char **argv)
1436 extern char *optarg;
1439 char *dbname = NULL;
1442 char *qfilename = NULL;
1443 char errbuf[ERROR_MSG_LENGTH];
1445 PsqlSettings settings;
1447 char *singleQuery = NULL;
1449 bool listDatabases = 0;
1450 int successResult = 1;
1451 bool singleSlashCmd = 0;
1454 memset(&settings, 0, sizeof settings);
1455 settings.opt.align = 1;
1456 settings.opt.header = 1;
1457 settings.queryFout = stdout;
1458 settings.opt.fieldSep = strdup(DEFAULT_FIELD_SEP);
1459 settings.opt.pager = 1;
1460 if (!isatty(0) || !isatty(1))
1461 settings.quiet = settings.notty = 1;
1462 #ifdef HAVE_LIBREADLINE
1464 settings.useReadline = 1;
1467 while ((c = getopt(argc, argv, "Aa:c:d:ef:F:lh:Hnso:p:qStT:x")) != EOF) {
1470 settings.opt.align = 0;
1473 fe_setauthsvc(optarg, errbuf);
1476 singleQuery = optarg;
1477 if (singleQuery[0] == '\\') {
1485 settings.echoQuery = 1;
1491 settings.opt.fieldSep = optarg;
1500 settings.opt.html3 = 1;
1503 settings.useReadline = 0;
1506 setFout(&settings, optarg);
1515 settings.singleStep = 1;
1518 settings.singleLineMode = 1;
1521 settings.opt.header = 0;
1524 settings.opt.tableOpt = optarg;
1527 settings.opt.expanded = 1;
1534 /* if we still have an argument, use it as the database name */
1535 if (argc - optind == 1)
1536 dbname = argv[optind];
1539 dbname = "template1";
1541 settings.db = PQsetdb(host, port, NULL, NULL, dbname);
1542 dbname = PQdb(settings.db);
1544 if (PQstatus(settings.db) == CONNECTION_BAD) {
1545 fprintf(stderr, "Connection to database '%s' failed.\n", dbname);
1546 fprintf(stderr, "%s", PQerrorMessage(settings.db));
1549 if (listDatabases) {
1550 exit(listAllDbs(&settings));
1552 if (!settings.quiet && !singleQuery && !qfilename) {
1553 printf("Welcome to the POSTGRESQL interactive sql monitor:\n");
1554 printf(" Please read the file COPYRIGHT for copyright terms "
1555 "of POSTGRESQL\n\n");
1556 printf(" type \\? for help on slash commands\n");
1557 printf(" type \\q to quit\n");
1558 printf(" type \\g or terminate with semicolon to execute query\n");
1559 printf(" You are currently connected to the database: %s\n\n", dbname);
1561 if (qfilename || singleSlashCmd) {
1563 * read in a file full of queries instead of reading in queries
1568 if (singleSlashCmd) {
1569 /* Not really a query, but "Do what I mean, not what I say." */
1572 line = malloc(strlen(qfilename) + 5);
1573 sprintf(line, "\\i %s", qfilename);
1575 HandleSlashCmds(&settings, line, "");
1578 bool success; /* The query succeeded at the backend */
1579 SendQuery(&success, &settings, singleQuery, false, false, 0);
1580 successResult = success;
1582 successResult = MainLoop(&settings, stdin);
1585 PQfinish(settings.db);
1587 return !successResult;
1590 #define COPYBUFSIZ 8192
1593 handleCopyOut(PGresult * res, bool quiet, FILE * copystream)
1596 char copybuf[COPYBUFSIZ];
1599 copydone = false; /* Can't be done; haven't started. */
1602 ret = PQgetline(res->conn, copybuf, COPYBUFSIZ);
1604 if (copybuf[0] == '\\' &&
1605 copybuf[1] == '.' &&
1606 copybuf[2] == '\0') {
1607 copydone = true; /* don't print this... */
1609 fputs(copybuf, copystream);
1615 fputc('\n', copystream);
1623 PQendcopy(res->conn);
1629 handleCopyIn(PGresult * res, const bool mustprompt, FILE * copystream)
1631 bool copydone = false;
1634 char copybuf[COPYBUFSIZ];
1640 fputs("Enter info followed by a newline\n", stdout);
1641 fputs("End with a backslash and a "
1642 "period on a line by itself.\n", stdout);
1644 while (!copydone) { /* for each input line ... */
1646 fputs(">> ", stdout);
1651 while (!linedone) { /* for each buffer ... */
1653 buflen = COPYBUFSIZ;
1654 for (; buflen > 1 &&
1655 !(linedone = (c = getc(copystream)) == '\n' || c == EOF);
1660 PQputline(res->conn, "\\.");
1665 PQputline(res->conn, copybuf);
1667 if (!strcmp(copybuf, "\\.")) {
1673 PQputline(res->conn, "\n");
1675 PQendcopy(res->conn);
1681 * try to open fname and return a FILE *, if it fails, use stdout, instead
1685 setFout(PsqlSettings * ps, char *fname)
1687 if (ps->queryFout && ps->queryFout != stdout) {
1689 pclose(ps->queryFout);
1691 fclose(ps->queryFout);
1694 ps->queryFout = stdout;
1695 pqsignal(SIGPIPE, SIG_DFL);
1698 if (*fname == '|') {
1699 pqsignal(SIGPIPE, SIG_IGN);
1700 ps->queryFout = popen(fname + 1, "w");
1703 ps->queryFout = fopen(fname, "w");
1704 pqsignal(SIGPIPE, SIG_DFL);
1707 if (!ps->queryFout) {
1709 ps->queryFout = stdout;
1712 return ps->queryFout;