From: Julian Assange Date: Tue, 27 Feb 1996 06:29:04 +1100 (EST) Subject: [PG95]: psql/monitor/libpq enhancements (now does html3, alignment, pipes etc) Here are my patches against the postgres95-1.01 release. Unfortunately they didn't make the 1.01 release deadline. libpq and psql.c have been modified to do various things they didn't do before (plus some optimisations/bug fixes et al). I've included a small demo transcript below. Note that all of of the display functionality/intelligence you see here, can be had merely by calling the new LIBPQ PQprint() routine with the appropriate arguments/options, including the HTML3 output guff. In order to apply the patch save it to a file (say "pg95-proff.101.diff"). Your postgres95 top level directory should be called "postgres95" and you should be in the directory immediately above it. Apply the patch with: $ patch -p0 \? \a -- toggle field-alignment (currenty on) \c [] -- set html3 caption (currently '') \C -- connect to new database (currently 'users') \d [] -- list tables in database or columns in
\d * -- list tables in database and columns in all tables \e [] -- edit the current query buffer or \f [] -- change field separater (currently '|') \g [] -- send query to backend [and place results in ] \g | -- send query to backend and pipe results into \h [] -- help on syntax of sql commands \h * -- complete description of all sql commands \H -- toggle html3 output (currently off) \i -- read and execute queries from filename \l -- list all databases \o [] -- send query results to or stdout \o | -- pipe query results through \p -- print the current query buffer \q -- quit \r [] -- edit then send contents to backend \s [] -- print history or save it in \S -- toggle standard sql type-setting (currently off) \t -- toggle table output header (currently on) \T [] -- set html3.0
options (currently '') \x -- toggle expanded output (currently off) \z -- zorch current query buffer (i.e clear it) \! [] -- shell escape or command \? -- help users=> \H turned on HTML3.0 tablular output users=> \T border=2 users=> select username, uid, sex from users where uid%300=0;
Retrieved 3 tuples * 3 fields
usernameuidsex
jbray2400male
pitch2100male
brad3600male
users=> \S turned on standard SQL separaters and padding field separater changed to '|' users=> select username, uid, sex from users where uid%300=0; +----------+------+------+ | username | uid | sex | +----------+------+------+ | jbray | 2400 | male | +----------+------+------+ | pitch | 2100 | male | +----------+------+------+ | brad | 3600 | male | +----------+------+------+ users=> \S turned off standard SQL separaters and padding field separater changed to '|' users=> select username, uid, sex from users where uid%300=0; username| uid|sex - --------+----+---- jbray |2400|male pitch |2100|male brad |3600|male users=> \a turned off field alignment users=> \t turned off output headers users=> \f \t field separater changed to ' ' users=> select username, uid, sex from users where uid%300=0; jbray 2400 male pitch 2100 male brad 3600 male users=> select username, uid, sex from users where uid%300=0 users=> \g|wc 3 9 47 users=> \a turned on field alignment users=> \x -- this works in html mode too! turned on expanded table representation users=> \f \ = field separater changed to ' =' users=> \t turned on output headers users=> select username, uid, sex from users where uid%300=0; Field = Value - -- RECORD 0 -- username = jbray uid = 2400 sex = male - -- RECORD 1 -- username = pitch uid = 2100 sex = male - -- RECORD 2 -- username = brad uid = 3600 sex = male users=>\q - -- CUT HERE! -- - --- src/bin/psql/psql.c.old Sat Feb 24 12:12:07 1996 +++ src/bin/psql/psql.c Tue Feb 27 05:36:10 1996 @@ -16,8 +16,8 @@ #include #include #include +#include #include - - #include "libpq-fe.h" #include "stringutils.h" @@ -32,52 +32,57 @@ #include "history.h" #else #include - -#include +#include #endif #endif #define MAX_QUERY_BUFFER 20000 - -#define MAX_FIELD_SEP_LENGTH 40 #define COPYBUFSIZ 8192 - -#define DEFAULT_FIELD_SEP " " +#define DEFAULT_FIELD_SEP "|" #define DEFAULT_EDITOR "vi" #define DEFAULT_SHELL "/bin/sh" typedef struct _psqlSettings { - - int echoQuery; /* if 1, echo the query before sending it */ - - int quiet; /* run quietly, no messages, no promt */ - - int singleStep; /* if 1, prompt for each query */ - - int singleLineMode; /* if 1, query terminated by newline */ - - int useReadline; /* use the readline routines or not */ - - int printHeader; /* print output field headers or not */ - - int fillAlign; /* fill align the fields */ - - FILE *queryFout; /* where to send the query results */ - - char fieldSep[MAX_FIELD_SEP_LENGTH]; /* field separator */ + PGconn *db; /* connection to backend */ + FILE *queryFout; /* where to send the query results */ + PQprintOpt opt; /* options to be passed to PQprint */ + char *prompt; /* prompt to display */ + char *gfname; /* one-shot file output argument for \g */ + bool notty; /* input or output is not a tty */ + bool pipe; /* queryFout is from a popen() */ + bool echoQuery; /* echo the query before sending it */ + bool quiet; /* run quietly, no messages, no promt */ + bool singleStep; /* prompt before for each query */ + bool singleLineMode; /* query terminated by newline */ + bool useReadline; /* use libreadline routines */ } PsqlSettings; /* declarations for functions in this file */ static void usage(char* progname); static void slashUsage(); - -static void handleCopyOut(PGresult *res, int quiet); - -static void handleCopyIn(PGresult *res, int quiet); - -static int tableList(PGconn* conn, int deep_tablelist); - -static int tableDesc(PGconn* conn, char* table); +static void handleCopyOut(PGresult *res, bool quiet); +static void handleCopyIn(PGresult *res, bool quiet); +static int tableList(PsqlSettings *ps, bool deep_tablelist); +static int tableDesc(PsqlSettings *ps, char *table); char* gets_noreadline(char* prompt, FILE* source); char* gets_readline(char* prompt, FILE* source); char* gets_fromFile(char* prompt, FILE* source); - -int listAllDbs(PGconn *db, PsqlSettings *settings); - -int SendQuery(PGconn* db, char* query, PsqlSettings *settings); - -int HandleSlashCmds(PGconn** db_ptr, +int listAllDbs(PsqlSettings *settings); +int SendQuery(PsqlSettings *settings, char* query); +int HandleSlashCmds(PsqlSettings *settings, char *line, - - char** prompt_ptr, - - char *query, - - PsqlSettings *settings); - -int MainLoop(PGconn** db_ptr, FILE *source, PsqlSettings *settings); - -FILE* setFout(char *fname); + char *query); +int MainLoop(PsqlSettings *settings, FILE *source); +/* probably should move this into libpq */ +void PQprint(FILE *fp, + PGresult *res, + PQprintOpt *po + ); +FILE* setFout(PsqlSettings *ps, char *fname); /* * usage @@ -89,22 +94,24 @@ { fprintf(stderr,"Usage: %s [options] [dbname]\n",progname); fprintf(stderr,"\t -a authsvc set authentication service\n"); - - fprintf(stderr,"\t -A turn off fill-justification when printing out attributes\n"); + fprintf(stderr,"\t -A turn off alignment when printing out attributes\n"); fprintf(stderr,"\t -c query run single query (slash commands too)\n"); fprintf(stderr,"\t -d dbName specify database name\n"); fprintf(stderr,"\t -e echo the query sent to the backend\n"); fprintf(stderr,"\t -f filename use file as a source of queries\n"); fprintf(stderr,"\t -F sep set the field separator (default is " ")\n"); - - fprintf(stderr,"\t -h help information\n"); - - fprintf(stderr,"\t -H host set database server host\n"); + fprintf(stderr,"\t -h host set database server host\n"); + fprintf(stderr,"\t -H turn on html3.0 table output\n"); fprintf(stderr,"\t -l list available databases\n"); fprintf(stderr,"\t -n don't use readline library\n"); - - fprintf(stderr,"\t -o filename send output to filename\n"); + fprintf(stderr,"\t -o filename send output to filename or (|pipe)\n"); fprintf(stderr,"\t -p port set port number\n"); fprintf(stderr,"\t -q run quietly (no messages, no prompts)\n"); fprintf(stderr,"\t -s single step mode (prompts for each query)\n"); fprintf(stderr,"\t -S single line mode (i.e. query terminated by newline)\n"); - - fprintf(stderr,"\t -T turn off printing of attribute names\n"); + fprintf(stderr,"\t -t turn off printing of attribute headers\n"); + fprintf(stderr,"\t -T html set html3.0 table command options (cf. -H)\n"); + fprintf(stderr,"\t -x turn on expanded output (field names on left)"); exit(1); } @@ -113,29 +120,60 @@ * print out usage for the backslash commands */ +char *on(bool f) +{ + return f? "on": "off"; +} + static void - -slashUsage() +slashUsage(PsqlSettings *ps) { - - fprintf(stderr,"\t \\a -- toggle fill-justification of display of attributes\n"); + fprintf(stderr,"\t \\a -- toggle field-alignment (currenty %s)\n", on(ps->opt.align)); + fprintf(stderr,"\t \\c [] -- set html3 caption (currently '%s')\n", ps->opt.caption? ps->opt.caption: ""); + fprintf(stderr,"\t \\C -- connect to new database (currently '%s')\n", PQdb(ps->db)); fprintf(stderr,"\t \\d [] -- list tables in database or columns in
\n"); fprintf(stderr,"\t \\d * -- list tables in database and columns in all tables\n"); fprintf(stderr,"\t \\e [] -- edit the current query buffer or \n"); - - fprintf(stderr,"\t \\f -- change field separator\n"); - - fprintf(stderr,"\t \\g -- query to backend\n"); - - fprintf(stderr,"\t \\h -- help on syntax of sql commands\n"); + fprintf(stderr,"\t \\f [] -- change field separater (currently '%s')\n", ps->opt.fieldSep); + fprintf(stderr,"\t \\g [] -- send query to backend [and place results in ]\n"); + fprintf(stderr,"\t \\g | -- send query to backend and pipe results into \n"); + fprintf(stderr,"\t \\h [] -- help on syntax of sql commands\n"); fprintf(stderr,"\t \\h * -- complete description of all sql commands\n"); - - fprintf(stderr,"\t \\g -- send query to backend\n"); - - fprintf(stderr,"\t \\i -- read queries from filename\n"); + fprintf(stderr,"\t \\H -- toggle html3 output (currently %s)\n", on(ps->opt.html3)); + fprintf(stderr,"\t \\i -- read and execute queries from filename\n"); fprintf(stderr,"\t \\l -- list all databases\n"); - - fprintf(stderr,"\t \\o [] -- send query results file named or stdout\n"); + fprintf(stderr,"\t \\o [] -- send query results to or stdout\n"); + fprintf(stderr,"\t \\o | -- pipe query results through \n"); fprintf(stderr,"\t \\p -- print the current query buffer\n"); fprintf(stderr,"\t \\q -- quit\n"); - - fprintf(stderr,"\t \\s [] -- save or print history\n"); - - fprintf(stderr,"\t \\t -- toggle output field headers (defaults to on)\n"); - - fprintf(stderr,"\t \\! [] -- shell escape\n"); + fprintf(stderr,"\t \\r [] -- edit then send contents to backend\n"); + fprintf(stderr,"\t \\s [] -- print history or save it in \n"); + fprintf(stderr,"\t \\S -- toggle standard sql type-setting (currently %s)\n", on(ps->opt.standard)); + fprintf(stderr,"\t \\t -- toggle table output header (currently %s)\n", on(ps->opt.header)); + fprintf(stderr,"\t \\T [] -- set html3.0
options (currently '%s')\n", ps->opt.tableOpt? ps->opt.tableOpt: ""); + fprintf(stderr,"\t \\x -- toggle expanded output (currently %s)\n", on(ps->opt.expanded)); + fprintf(stderr,"\t \\z -- zorch current query buffer (i.e clear it)\n"); + fprintf(stderr,"\t \\! [] -- shell escape or command\n"); fprintf(stderr,"\t \\? -- help\n"); } +PGresult * +PSQLexec(PsqlSettings *ps, char *query) +{ + PGresult *res = PQexec(ps->db, query); + if (!res) + fputs(PQerrorMessage(ps->db), stderr); + else + { + if (PQresultStatus(res)==PGRES_COMMAND_OK || + PQresultStatus(res)==PGRES_TUPLES_OK) + return res; + if (!ps->quiet) + fputs(PQerrorMessage(ps->db), stderr); + PQclear(res); + } + return NULL; +} /* * listAllDbs * @@ -144,50 +182,37 @@ * * */ - -int - -listAllDbs(PGconn *db, PsqlSettings *settings) + +int +listAllDbs(PsqlSettings *ps) { PGresult *results; char* query = "select * from pg_database;"; - - results = PQexec(db, query); - - if (results == NULL) { - - fprintf(stderr,"%s", PQerrorMessage(db)); + if (!(results=PSQLexec(ps, query))) return 1; - - } - - - - if (PQresultStatus(results) != PGRES_TUPLES_OK) - - { - - fprintf(stderr,"Unexpected error from executing: %s\n", query); - - return 2; - - } else { - - PQdisplayTuples(results, - - settings->queryFout, - - settings->fillAlign, - - settings->fieldSep, - - settings->printHeader, - - settings->quiet); + PQprint(ps->queryFout, + results, + &ps->opt); PQclear(results); return 0; } } /* - - * tableList (PGconn* conn) * * List The Database Tables * returns 0 if all went well * */ int - -tableList (PGconn* conn, int deep_tablelist) +tableList (PsqlSettings *ps, bool deep_tablelist) { char listbuf[256]; int nColumns; int i; - - char* ru; char* rk; char* rr; @@ -203,17 +228,8 @@ add in the int4oideq function */ strcat(listbuf," and usesysid = relowner"); strcat(listbuf," ORDER BY relname "); - - res = PQexec(conn,listbuf); - - if (res == NULL) { - - fprintf(stderr,"%s", PQerrorMessage(conn)); - - return (-1); - - } - - - - if ((PQresultStatus(res) != PGRES_TUPLES_OK) || (PQntuples(res) <= 0)) { - - fprintf(stderr,"No tables found in database %s.\n", PQdb(conn)); - - PQclear(res); - - return (-1); - - } + if (!(res=PSQLexec(ps, listbuf))) + return -1; /* first, print out the attribute names */ nColumns = PQntuples(res); @@ -236,14 +252,14 @@ PQclear(res); for (i=0; i < nColumns; i++) { - - tableDesc(conn,table[i]); + tableDesc(ps, table[i]); } free(table); } else { /* Display the information */ - - printf ("\nDatabase = %s\n", PQdb(conn)); + printf ("\nDatabase = %s\n", PQdb(ps->db)); printf (" +------------------+----------------------------------+----------+\n"); printf (" | Owner | Relation | Type |\n"); printf (" +------------------+----------------------------------+----------+\n"); @@ -272,7 +288,7 @@ } /* - - * Describe a table (PGconn* conn, char* table) + * Describe a table * * Describe the columns in a database table. * returns 0 if all went well @@ -280,7 +296,7 @@ * */ int - -tableDesc (PGconn* conn, char* table) +tableDesc (PsqlSettings *ps, char *table) { char descbuf[256]; int nColumns; @@ -302,16 +318,8 @@ strcat(descbuf," and a.attrelid = c.oid "); strcat(descbuf," and a.atttypid = t.oid "); strcat(descbuf," ORDER BY attnum "); - - res = PQexec(conn,descbuf); - - if (res == NULL) { - - fprintf(stderr,"%s", PQerrorMessage(conn)); - - return (-1); - - } - - if ((PQresultStatus(res) != PGRES_TUPLES_OK) || (PQntuples(res) <= 0)) { - - fprintf(stderr,"Couldn't find table %s!\n", table); - - PQclear(res); - - return (-1); - - } + if (!(res = PSQLexec(ps, descbuf))) + return -1; /* first, print out the attribute names */ nColumns = PQntuples(res); if (nColumns > 0) @@ -332,15 +340,15 @@ rsize = atoi(PQgetvalue(res,i,3)); if (strcmp(rtype, "text") == 0) { printf ("%-32.32s |", rtype); - - printf (" %-6s |", "var" ); + printf ("%6s |", "var" ); } else if (strcmp(rtype, "bpchar") == 0) { printf ("%-32.32s |", "char"); - - printf (" %-6i |", rsize > 0 ? rsize - 4 : 0 ); + printf ("%6i |", rsize > 0 ? rsize - 4 : 0 ); } else if (strcmp(rtype, "varchar") == 0) { printf ("%-32.32s |", rtype); - - printf (" %-6i |", rsize > 0 ? rsize - 4 : 0 ); + printf ("%6i |", rsize > 0 ? rsize - 4 : 0 ); } else { /* array types start with an underscore */ @@ -355,9 +363,9 @@ free(newname); } if (rsize > 0) - - printf ("%-6i |", rsize); + printf ("%6i |", rsize); else - - printf ("%-6s |", "var"); + printf ("%6s |", "var"); } printf("\n"); } @@ -396,7 +404,6 @@ return (readline(prompt)); } - - /* * gets_fromFile prompt source * @@ -426,14 +433,12 @@ } /* - - * SendQuery: - - SendQuery: send the query string to the backend - - * + * SendQuery: send the query string to the backend * return 0 if the query executed successfully * returns 1 otherwise */ int - -SendQuery(PGconn* db, char* query, PsqlSettings *settings) +SendQuery(PsqlSettings *settings, char *query) { PGresult* results; PGnotify* notify; @@ -454,20 +459,41 @@ gets_fromFile("",stdin); } - - results = PQexec(db, query); + results = PQexec(settings->db, query); if (results == NULL) { - - fprintf(stderr,"%s",PQerrorMessage(db)); + fprintf(stderr,"%s",PQerrorMessage(settings->db)); return 1; } switch (PQresultStatus(results)) { case PGRES_TUPLES_OK: - - PQdisplayTuples(results, - - settings->queryFout, - - settings->fillAlign, - - settings->fieldSep, - - settings->printHeader, - - settings->quiet); + if (settings->gfname) + { + PsqlSettings ps=*settings; + FILE *fp; + ps.queryFout=stdout; + fp=setFout(&ps, settings->gfname); + if (!fp || fp==stdout) + { + status = 1; + break; + } + PQprint(fp, + results, + &(settings->opt)); + if (ps.pipe) + pclose(fp); + else + fclose(fp); + settings->gfname=NULL; + break; + } else + { + PQprint(settings->queryFout, + results, + &(settings->opt)); + fflush(settings->queryFout); + } PQclear(results); break; case PGRES_EMPTY_QUERY: @@ -475,7 +501,7 @@ break; case PGRES_COMMAND_OK: if (!settings->quiet) - - fprintf(stdout,"%s\n",PQcmdStatus(results)); + fprintf(stdout,"%s\n", PQcmdStatus(results)); break; case PGRES_COPY_OUT: handleCopyOut(results, settings->quiet); @@ -487,13 +513,12 @@ case PGRES_FATAL_ERROR: case PGRES_BAD_RESPONSE: status = 1; - - fprintf(stderr,"%s",PQerrorMessage(db)); + fprintf(stderr,"%s",PQerrorMessage(settings->db)); break; - - } /* check for asynchronous returns */ - - notify = PQnotifies(db); + notify = PQnotifies(settings->db); if (notify) { fprintf(stderr,"ASYNC NOTIFY of '%s' from backend pid '%d' received\n", notify->relname, notify->be_pid); @@ -504,6 +529,71 @@ } +void +editFile(char *fname) +{ + char *editorName; + char *sys; + editorName = getenv("EDITOR"); + if (!editorName) + editorName = DEFAULT_EDITOR; + sys=malloc(strlen(editorName)+strlen(fname)+32+1); + if (!sys) + { + perror("malloc"); + exit(1); + } + sprintf(sys, "exec '%s' '%s'", editorName, fname); + system(sys); + free(sys); +} + +bool +toggle(PsqlSettings *settings, bool *sw, char *msg) +{ + *sw= !*sw; + if (!settings->quiet) + fprintf(stderr, "turned %s %s\n", on(*sw), msg); + return *sw; +} + +char * +decode(char *s) +{ + char *p, *d; + bool esc=0; + for (d=p=s; *p; p++) + { + char c=*p; + if (esc) + { + switch(*p) + { + case 'n': + c='\n'; + break; + case 'r': + c='\r'; + break; + case 't': + c='\t'; + break; + case 'f': + c='\f'; + break; + } + esc=0; + } else + if (c=='\\') + { + esc=1; + continue; + } + *d++=c; + } + *d='\0'; +} + /* HandleSlashCmds: @@ -519,189 +609,161 @@ 2 - terminate processing of this query entirely */ int - -HandleSlashCmds(PGconn** db_ptr, +HandleSlashCmds(PsqlSettings *settings, char* line, - - char** prompt_ptr, - - char *query, - - PsqlSettings *settings) + char *query) { - - int status = 0; - - PGconn* db = *db_ptr; - - char* dbname = PQdb(db); + int status = 1; char *optarg = NULL; int len; len = strlen(line); if (len > 2) + { optarg = leftTrim(line+2); + decode(optarg); + } switch (line[1]) { - - case 'a': /* toggles to fill fields on output */ - - if (settings->fillAlign) - - settings->fillAlign = 0; - - else - - settings->fillAlign = 1; - - if (!settings->quiet) - - fprintf(stderr,"turning %s fill-justification\n", - - (settings->fillAlign) ? "on" : "off" ); + case 'a': /* toggles to align fields on output */ + toggle(settings, &settings->opt.align, "field alignment"); break; - - case 'c': /* \c means connect to new database */ + case 'c': /* define new caption */ + if (settings->opt.caption) + free(settings->opt.caption); + if (!optarg) + settings->opt.caption=NULL; + else + if (!(settings->opt.caption=dupstr(optarg))) + { + perror("malloc"); + exit(1); + } + break; + case 'C': /* \C means connect to new database */ { + char *dbname=PQdb(settings->db); if (!optarg) { fprintf(stderr,"\\c must be followed by a database name\n"); - - status = 1; break; } - - if (strcmp(optarg, dbname) == 0) { - - fprintf(stderr,"already connected to %s\n", dbname); - - status = 1; - - break; - - } - - else { - - PGconn *olddb; + { + PGconn *olddb=settings->db; - - printf("closing connection to database:%s\n", dbname); - - olddb = db; - - db = PQsetdb(PQhost(olddb), PQport(olddb), NULL, NULL, optarg); - - *db_ptr = db; + printf("closing connection to database: %s\n", dbname); + settings->db = PQsetdb(PQhost(olddb), PQport(olddb), NULL, NULL, optarg); printf("connecting to new database: %s\n", optarg); - - if (PQstatus(db) == CONNECTION_BAD) { - - fprintf(stderr,"%s\n", PQerrorMessage(db)); + if (PQstatus(settings->db) == CONNECTION_BAD) { + fprintf(stderr,"%s\n", PQerrorMessage(settings->db)); printf("reconnecting to %s\n", dbname); - - db = PQsetdb(PQhost(olddb), PQport(olddb), + settings->db = PQsetdb(PQhost(olddb), PQport(olddb), NULL, NULL, dbname); - - *db_ptr = db; - - if (PQstatus(db) == CONNECTION_BAD) { + if (PQstatus(settings->db) == CONNECTION_BAD) { fprintf(stderr, - - "could not reconnect to %s. exiting\n", dbname); + "could not reconnect to %s. exiting\n", dbname); exit(2); } - - status = 1; break; } PQfinish(olddb); - - free(*prompt_ptr); - - *prompt_ptr = malloc(strlen(optarg) + 10); - - sprintf(*prompt_ptr,"%s=> ", optarg); - - status = 1; + free(settings->prompt); + settings->prompt = malloc(strlen(PQdb(settings->db)) + 10); + sprintf(settings->prompt,"%s=> ", PQdb(settings->db)); break; } } break; case 'd': /* \d describe tables or columns in a table */ - - { if (!optarg) { - - tableList(db,0); - - status = 1; + tableList(settings, 0); break; - - } - - if ( strcmp(optarg,"*") == 0 ) { - - tableList(db, 0); - - tableList(db, 1); + } + if (strcmp(optarg, "*") == 0 ) { + tableList(settings, 0); + tableList(settings, 1); } else { - - tableDesc(db,optarg); + tableDesc(settings, optarg); } - - status = 1; break; - - } case 'e': { - - char s[256]; int fd; - - int ql = strlen(query); - - int f_arg = 0; + char tmp[64]; + char *fname; int cc; + int ql = strlen(query); if (optarg) - - { - - f_arg = 1; - - strcpy(s, optarg); - - } + fname=optarg; else - - { - - sprintf(s, "/tmp/psql.%d.%d", getuid(), getpid()); - - unlink(s); + { + sprintf(tmp, "/tmp/psql.%d.%d", getuid(), getpid()); + fname=tmp; + unlink(tmp); if (ql) { - - if ((fd=open(s, O_EXCL|O_CREAT|O_WRONLY, 0600))==-1) + if ((fd=open(tmp, O_EXCL|O_CREAT|O_WRONLY, 0600))==-1) { - - perror(s); + perror(tmp); break; } if (query[ql-1]!='\n') strcat(query, "\n"); if (write(fd, query, ql)!=ql) { - - perror(s); + perror(tmp); close(fd); - - unlink(s); + unlink(tmp); break; } close(fd); } } + editFile(fname); + if ((fd=open(fname, O_RDONLY))==-1) { - - char sys[256]; - - char *editorName; - - editorName = getenv("EDITOR"); - - if (editorName == NULL) - - editorName = DEFAULT_EDITOR; - - sprintf(sys, "exec %s %s", editorName, s); - - system(sys); - - } - - if ((fd=open(s, O_RDONLY))==-1) - - { - - if (!f_arg) - - unlink(s); + perror(fname); + if (!optarg) + unlink(fname); break; } if ((cc=read(fd, query, MAX_QUERY_BUFFER))==-1) { - - perror(s); + perror(fname); close(fd); - - if (!f_arg) - - unlink(s); + if (!optarg) + unlink(fname); break; } query[cc]='\0'; close(fd); - - if (!f_arg) - - unlink(s); + if (!optarg) + unlink(fname); rightTrim(query); if (query[strlen(query)-1]==';') return 0; break; } - - case 'f': - - if (optarg) - - strcpy(settings->fieldSep,optarg); - - else - - strcpy(settings->fieldSep,DEFAULT_FIELD_SEP); - - break; - - case 'g': /* \g means send query */ - - status = 0; - - break; - - case 'i': /* \i is include file */ - - { - - FILE* fd; - - - - if (!optarg) { - - fprintf(stderr,"\\i must be followed by a file name\n"); - - status = 1; - - break; + case 'f': + { + char *fs=DEFAULT_FIELD_SEP; + if (optarg) + fs=optarg; + if (settings->opt.fieldSep); + free(settings->opt.fieldSep); + if (!(settings->opt.fieldSep=dupstr(fs))) + { + perror("malloc"); + exit(1); } - - - - if ( (fd = fopen(optarg, "r")) == NULL) - - { - - fprintf(stderr,"file named %s could not be opened\n",optarg); - - status = 1; - - break; - - } - - MainLoop(&db, fd, settings); - - fclose(fd); - - status = 1; + if (!settings->quiet) + fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep); break; - - } + } + case 'g': /* \g means send query */ + settings->gfname = optarg; + status = 0; + break; case 'h': { char* cmd; @@ -727,7 +789,7 @@ numCmds = numCmds - 1; - - if ( strcmp(cmd,"*") == 0 ) { + if (strcmp(cmd, "*") == 0 ) { all_help=1; } @@ -748,69 +810,154 @@ if (i == numCmds && ! all_help) printf("command not found, try \\h with no arguments to see available help\n"); } - - status = 1; + break; + } + case 'i': /* \i is include file */ + { + FILE* fd; + + if (!optarg) { + fprintf(stderr,"\\i must be followed by a file name\n"); + break; + } + + if ((fd = fopen(optarg, "r")) == NULL) + { + fprintf(stderr,"file named %s could not be opened\n",optarg); + break; + } + MainLoop(settings, fd); + fclose(fd); break; } case 'l': /* \l is list database */ - - listAllDbs(db,settings); - - status = 1; + listAllDbs(settings); + break; + case 'H': + if (toggle(settings, &settings->opt.html3, "HTML3.0 tablular output")) + settings->opt.standard = 0; break; case 'o': - - settings->queryFout = setFout(optarg); + setFout(settings, optarg); break; case 'p': - - if (query) { - - fputs(query, stdout); - - fputc('\n', stdout); - - } - - break; + if (query) + { + fputs(query, stdout); + fputc('\n', stdout); + } + break; case 'q': /* \q is quit */ status = 2; break; - - case 's': /* \s is save history to a file */ - - { - - char* fname; - - - - if (!optarg) { - - fprintf(stderr,"\\s must be followed by a file name\n"); - - status = 1; - - break; - - } - - - - fname = optarg; - - if (write_history(fname) != 0) + case 'r': + { + FILE* fd; + static char *lastfile; + struct stat st, st2; + if (optarg) + { + if (lastfile) + free(lastfile); + lastfile=malloc(strlen(optarg+1)); + if (!lastfile) + { + perror("malloc"); + exit(1); + } + strcpy(lastfile, optarg); + } else if (!lastfile) + { + fprintf(stderr,"\\r must be followed by a file name initially\n"); + break; + } + stat(lastfile, &st); + editFile(lastfile); + if ((stat(lastfile, &st2) == -1) || ((fd = fopen(lastfile, "r")) == NULL)) { - - fprintf(stderr,"cannot write history to %s\n",fname); + perror(lastfile); + break; } - - status = 1; + if (st2.st_mtime==st.st_mtime) + { + if (!settings->quiet) + fprintf(stderr, "warning: %s not modified. query not executed\n", lastfile); + fclose(fd); + break; + } + MainLoop(settings, fd); + fclose(fd); break; } - - case 't': - - if ( settings->printHeader ) - - settings->printHeader = 0; + case 's': /* \s is save history to a file */ + if (!optarg) + optarg="/dev/tty"; + if (write_history(optarg) != 0) + fprintf(stderr,"cannot write history to %s\n",optarg); + break; + case 'S': /* standard SQL output */ + if (toggle(settings, &settings->opt.standard, "standard SQL separaters and padding")) + { + settings->opt.html3 = settings->opt.expanded = 0; + settings->opt.align = settings->opt.header = 1; + free(settings->opt.fieldSep); + settings->opt.fieldSep=dupstr("|"); + if (!settings->quiet) + fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep); + } else + { + free(settings->opt.fieldSep); + settings->opt.fieldSep=dupstr(DEFAULT_FIELD_SEP); + if (!settings->quiet) + fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep); + } + break; + case 't': /* toggle headers */ + toggle(settings, &settings->opt.header, "output headers"); + break; + case 'T': /* define html
option */ + if (settings->opt.tableOpt) + free(settings->opt.tableOpt); + if (!optarg) + settings->opt.tableOpt=NULL; else - - settings->printHeader = 1; - - if (!settings->quiet) - - fprintf(stderr,"turning %s printing of field headers\n", - - (settings->printHeader) ? "on" : "off" ); + if (!(settings->opt.tableOpt=dupstr(optarg))) + { + perror("malloc"); + exit(1); + } break; + case 'x': + toggle(settings, &settings->opt.expanded, "expanded table representation"); + break; + case 'z': /* zorch buffer */ + query[0]='\0'; + if (!settings->quiet) + fprintf(stderr, "zorched current query buffer\n"); + break; case '!': if (!optarg) { - - char sys[256]; + char *sys; char *shellName; shellName = getenv("SHELL"); if (shellName == NULL) shellName = DEFAULT_SHELL; + sys = malloc(strlen(shellName)+16); + if (!sys) + { + perror("malloc"); + exit(1); + } sprintf(sys,"exec %s", shellName); system(sys); + free(sys); } else system(optarg); break; default: case '?': /* \? is help */ - - slashUsage(); - - status = 1; + slashUsage(settings); break; } return status; @@ -825,19 +972,14 @@ *db_ptr must be initialized and set */ + int - -MainLoop(PGconn** db_ptr, - - FILE* source, - - PsqlSettings *settings) +MainLoop(PsqlSettings *settings, FILE* source) { - - char* prompt; /* readline prompt */ char* line; /* line of input*/ int len; /* length of the line */ char query[MAX_QUERY_BUFFER]; /* multi-line query storage */ - - PGconn* db = *db_ptr; - - char* dbname = PQdb(db); int exitStatus = 0; - - int slashCmdStatus = 0; /* slashCmdStatus can be: 0 - send currently constructed query to backend (i.e. we got a \g) @@ -845,18 +987,21 @@ 2 - terminate processing of this query entirely */ - - int send_query = 0; - - int interactive; + bool sendQuery = 0; + bool querySent = 0; + bool interactive; READ_ROUTINE GetNextLine; - - interactive = (source == stdin); - - + interactive = ((source == stdin) && !settings->notty); +#define PROMPT "=> " if (interactive) { - - prompt = malloc(strlen(dbname) + 10); + if (settings->prompt) + free(settings->prompt); + settings->prompt = malloc(strlen(PQdb(settings->db)) + strlen(PROMPT) + 1); if (settings->quiet) - - prompt[0] = '\0'; + settings->prompt[0] = '\0'; else - - sprintf(prompt,"%s=> ", dbname); + sprintf(settings->prompt,"%s%s", PQdb(settings->db), PROMPT); if (settings->useReadline) { using_history(); GetNextLine = gets_readline; @@ -870,7 +1015,7 @@ query[0] = '\0'; /* main loop for getting queries and executing them */ - - while ((line = GetNextLine(prompt, source)) != NULL) + while ((line = GetNextLine(settings->prompt, source)) != NULL) { exitStatus = 0; line = rightTrim(line); /* remove whitespaces on the right, incl. \n's */ @@ -890,6 +1035,11 @@ free(line); continue; } + if (line[0] != '\\' && querySent) + { + query[0]='\0'; + querySent = 0; + } len = strlen(line); @@ -898,34 +1048,31 @@ /* do the query immediately if we are doing single line queries or if the last character is a semicolon */ - - send_query = settings->singleLineMode || (line[len-1] == ';') ; + sendQuery = settings->singleLineMode || (line[len-1] == ';') ; /* normally, \ commands have to be start the line, but for backwards compatibility with monitor, check for \g at the end of line */ - - if (len > 2 && !send_query) + if (len > 2 && !sendQuery) { if (line[len-1]=='g' && line[len-2]=='\\') { - - send_query = 1; + sendQuery = 1; line[len-2]='\0'; } } /* slash commands have to be on their own line */ if (line[0] == '\\') { - - slashCmdStatus = HandleSlashCmds(db_ptr, + slashCmdStatus = HandleSlashCmds(settings, line, - - &prompt, - - query, - - settings); - - db = *db_ptr; /* in case \c changed the database */ + query); if (slashCmdStatus == 1) continue; if (slashCmdStatus == 2) break; if (slashCmdStatus == 0) - - send_query = 1; + sendQuery = 1; } else if (strlen(query) + len > MAX_QUERY_BUFFER) @@ -941,30 +1088,29 @@ else strcpy(query,line); - - if (send_query && query[0] != '\0') + if (sendQuery && query[0] != '\0') { /* echo the line read from the file, unless we are in single_step mode, because single_step mode will echo anyway */ - - if (!interactive && !settings->singleStep) - - fprintf(stderr,"%s\n",query); + if (!interactive && !settings->singleStep && !settings->quiet) + fprintf(stderr,"%s\n", query); - - exitStatus = SendQuery(db, query, settings); - - query[0] = '\0'; + exitStatus = SendQuery(settings, query); + querySent = 1; } free(line); /* free storage malloc'd by GetNextLine */ } /* while */ - - return exitStatus; + return exitStatus; } int main(int argc, char** argv) { extern char* optarg; - - extern int optind, opterr; + extern int optind; - - PGconn *db; char* dbname = NULL; char* host = NULL; char* port = NULL; @@ -975,31 +1121,27 @@ char* singleQuery = NULL; - - int listDatabases = 0 ; + bool listDatabases = 0 ; int exitStatus = 0; - - int singleSlashCmd = 0; + bool singleSlashCmd = 0; int c; - - - -#ifdef NOREADLINE - - settings.useReadline = 0; - -#else - - settings.useReadline = 1; - -#endif - - - - settings.quiet = 0; - - settings.fillAlign = 1; - - settings.printHeader = 1; - - settings.echoQuery = 0; - - settings.singleStep = 0; - - settings.singleLineMode = 0; + memset(&settings, 0, sizeof settings); + settings.opt.align = 1; + settings.opt.header = 1; settings.queryFout = stdout; - - strcpy(settings.fieldSep, DEFAULT_FIELD_SEP); + settings.opt.fieldSep=dupstr(DEFAULT_FIELD_SEP); + if (!isatty(0) || !isatty(1)) + settings.quiet = settings.notty = 1; + else +#ifndef NOREADLINE + settings.useReadline = 1; +#endif - - while ((c = getopt(argc, argv, "Aa:c:d:ef:F:lhH:nso:p:qST")) != EOF) { + while ((c = getopt(argc, argv, "Aa:c:d:ef:F:lhH:nso:p:qStTx")) != EOF) { switch (c) { case 'A': - - settings.fillAlign = 0; + settings.opt.align = 0; break; case 'a': fe_setauthsvc(optarg, errbuf); @@ -1020,20 +1162,23 @@ qfilename = optarg; break; case 'F': - - strncpy(settings.fieldSep,optarg,MAX_FIELD_SEP_LENGTH); - - break; + settings.opt.fieldSep=dupstr(optarg); + break; case 'l': listDatabases = 1; break; - - case 'H': + case 'h': host = optarg; break; + case 'H': + settings.opt.html3 = 1; + break; case 'n': - - settings.useReadline = 0; - - break; + settings.useReadline = 0; + break; case 'o': - - settings.queryFout = setFout(optarg); - - break; + setFout(&settings, optarg); + break; case 'p': port = optarg; break; @@ -1046,10 +1191,15 @@ case 'S': settings.singleLineMode = 1; break; + case 't': + settings.opt.header = 0; + break; case 'T': - - settings.printHeader = 0; - - break; - - case 'h': + settings.opt.tableOpt = dupstr(optarg); + break; + case 'x': + settings.opt.expanded = 0; + break; default: usage(argv[0]); break; @@ -1062,16 +1212,16 @@ if (listDatabases) dbname = "template1"; - - db = PQsetdb(host, port, NULL, NULL, dbname); - - dbname = PQdb(db); + settings.db = PQsetdb(host, port, NULL, NULL, dbname); + dbname = PQdb(settings.db); - - if (PQstatus(db) == CONNECTION_BAD) { + if (PQstatus(settings.db) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.\n", dbname); - - fprintf(stderr,"%s",PQerrorMessage(db)); + fprintf(stderr,"%s",PQerrorMessage(settings.db)); exit(1); } if (listDatabases) { - - exit(listAllDbs(db,&settings)); + exit(listAllDbs(&settings)); } if (!settings.quiet && !singleQuery && !qfilename) { @@ -1087,7 +1237,6 @@ /* read in a file full of queries instead of reading in queries interactively */ char *line; - - char prompt[100]; if ( singleSlashCmd ) { /* Not really a query, but "Do what I mean, not what I say." */ @@ -1097,24 +1246,25 @@ line = malloc(strlen(qfilename) + 5); sprintf(line,"\\i %s", qfilename); } - - HandleSlashCmds(&db, line, (char**)prompt, "", &settings); + HandleSlashCmds(&settings, line, ""); } else { if (singleQuery) { - - exitStatus = SendQuery(db, singleQuery, &settings); + exitStatus = SendQuery(&settings, singleQuery); } else - - exitStatus = MainLoop(&db, stdin, &settings); + exitStatus = MainLoop(&settings, stdin); } - - PQfinish(db); + PQfinish(settings.db); return exitStatus; } +#define COPYBUFSIZ 8192 static void - -handleCopyOut(PGresult *res, int quiet) +handleCopyOut(PGresult *res, bool quiet) { bool copydone = false; char copybuf[COPYBUFSIZ]; @@ -1148,7 +1298,7 @@ static void - -handleCopyIn(PGresult *res, int quiet) +handleCopyIn(PGresult *res, bool quiet) { bool copydone = false; bool firstload; @@ -1207,24 +1357,38 @@ PQendcopy(res->conn); } - - /* try to open fname and return a FILE*, if it fails, use stdout, instead */ + FILE* - -setFout(char *fname) +setFout(PsqlSettings *ps, char *fname) { - - FILE *queryFout; - - - - if (!fname) - - queryFout = stdout; - - else { - - queryFout = fopen(fname, "w"); - - if (!queryFout) { - - perror(fname); - - queryFout = stdout; + if (ps->queryFout && ps->queryFout != stdout) + { + if (ps->pipe) + pclose(ps->queryFout); + else + fclose(ps->queryFout); } - - } - - - - return queryFout; + if (!fname) + ps->queryFout = stdout; + else + { + if (*fname == '|') + { + signal(SIGPIPE, SIG_IGN); + ps->queryFout = popen(fname+1, "w"); + ps->pipe = 1; + } + else + { + ps->queryFout = fopen(fname, "w"); + ps->pipe = 0; + } + if (!ps->queryFout) { + perror(fname); + ps->queryFout = stdout; + } + } + return ps->queryFout; } - - - --- src/bin/monitor/monitor.c.old Sat Feb 24 12:08:28 1996 +++ src/bin/monitor/monitor.c Tue Feb 27 01:53:16 1996 @@ -642,6 +642,7 @@ { PGresult *result; int retval = 0; + PQprintOpt opt; result = PQexec(conn, query); @@ -657,7 +658,11 @@ break; case PGRES_TUPLES_OK: /* PQprintTuples(result,stdout,PrintAttNames,TerseOutput,COLWIDTH); */ - - PQdisplayTuples(result,stdout,1,"|",PrintAttNames,TerseOutput); +/* PQdisplayTuples(result,stdout,1,"|",PrintAttNames,TerseOutput); */ + memset(&opt, 0, sizeof opt); + opt.header = opt.align = opt.standard = 1; + opt.fieldSep = "|"; + PQprint(stdout, result, &opt); break; case PGRES_COPY_OUT: handle_copy_out(result); - --- src/libpq/fe-exec.c.old Sat Feb 24 12:22:23 1996 +++ src/libpq/fe-exec.c Tue Feb 27 05:57:00 1996 @@ -7,7 +7,7 @@ * * * IDENTIFICATION - - * $Header: /usr/local/devel/pglite/cvs/src/libpq/fe-exec.c,v 1.10 1996/02/24 01:22:23 jolly Exp $ + * $Header: /home/jolly/POSTGRES_MASTER_CVS/src/libpq/fe-exec.c,v 1.9 1995/10/31 07:51:57 jolly Exp $ * *------------------------------------------------------------------------- */ @@ -18,7 +18,6 @@ #include "postgres.h" #include "libpq/pqcomm.h" #include "libpq-fe.h" - -#include /* the tuples array in a PGresGroup has to grow to accommodate the tuples */ /* returned. Each time, we grow by this much: */ @@ -39,7 +38,6 @@ static void addTuple(PGresult *res, PGresAttValue *tup); static PGresAttValue* getTuple(PGconn *conn, PGresult *res, int binary); static PGresult* makeEmptyPGresult(PGconn *conn, ExecStatusType status); - -static void fill(int length, int max, char filler, FILE *fp); /* * PQclear - @@ -607,133 +605,13 @@ } } - -/* simply send out max-length number of filler characters to fp */ - -static void - -fill (int length, int max, char filler, FILE *fp) - -{ - - int count; - - char filltmp[2]; - - - - filltmp[0] = filler; - - filltmp[1] = 0; - - count = max - length; - - while (count-- >= 0) - - { - - fprintf(fp, "%s", filltmp); - - } - - } - - - - - -/* - - * PQdisplayTuples() - - * - - * a better version of PQprintTuples() - - * that can optionally do padding of fields with spaces and use different - - * field separators - - */ - -void - -PQdisplayTuples(PGresult *res, - - FILE *fp, /* where to send the output */ - - int fillAlign, /* pad the fields with spaces */ - - char *fieldSep, /* field separator */ - - int printHeader, /* display headers? */ - - int quiet - - ) - -{ - -#define DEFAULT_FIELD_SEP " " - - - - char *pager; - - int i, j; - - int nFields; - - int nTuples; - - int fLength[MAX_FIELDS]; - - int usePipe = 0; - - - - if (fieldSep == NULL) - - fieldSep == DEFAULT_FIELD_SEP; - - - - if (fp == NULL) - - fp = stdout; - - if (fp == stdout) { - - /* try to pipe to the pager program if possible */ - - pager=getenv("PAGER"); - - if (pager != NULL) { - - fp = popen(pager, "w"); - - if (fp) { - - usePipe = 1; - - signal(SIGPIPE, SIG_IGN); - - } else - - fp = stdout; - - } - - } - - - - /* Get some useful info about the results */ - - nFields = PQnfields(res); - - nTuples = PQntuples(res); - - - - /* Zero the initial field lengths */ - - for (j=0 ; j < nFields; j++) { - - fLength[j] = strlen(PQfname(res,j)); - - } - - /* Find the max length of each field in the result */ - - /* will be somewhat time consuming for very large results */ - - if (fillAlign) { - - for (i=0; i < nTuples; i++) { - - for (j=0 ; j < nFields; j++) { - - if (PQgetlength(res,i,j) > fLength[j]) - - fLength[j] = PQgetlength(res,i,j); - - } - - } - - } - - - - if (printHeader) { - - /* first, print out the attribute names */ - - for (i=0; i < nFields; i++) { - - fputs(PQfname(res,i), fp); - - if (fillAlign) - - fill (strlen (PQfname(res,i)), fLength[i], ' ', fp); - - fputs(fieldSep,fp); - - } - - fprintf(fp, "\n"); - - - - /* Underline the attribute names */ - - for (i=0; i < nFields; i++) { - - if (fillAlign) - - fill (0, fLength[i], '-', fp); - - fputs(fieldSep,fp); - - } - - fprintf(fp, "\n"); - - } - - - - /* next, print out the instances */ - - for (i=0; i < nTuples; i++) { - - for (j=0 ; j < nFields; j++) { - - fprintf(fp, "%s", PQgetvalue(res,i,j)); - - if (fillAlign) - - fill (strlen (PQgetvalue(res,i,j)), fLength[j], ' ', fp); - - fputs(fieldSep,fp); - - } - - fprintf(fp, "\n"); - - } - - - - if (!quiet) - - fprintf (fp, "\nQuery returned %d row%s.\n",PQntuples(res), - - (PQntuples(res) == 1) ? "" : "s"); - - - - fflush(fp); - - if (usePipe) { - - pclose(fp); - - signal(SIGPIPE, SIG_DFL); - - } - -} - - /* - - * PQprintTuples() + * print_tuples() * * This is the routine that prints out the tuples that - - * are returned from the backend. + * are returned from the backend. * Right now all columns are of fixed length, * this should be changed to allow wrap around for * tuples values that are wider. @@ -802,6 +680,295 @@ fprintf(fout, "|\n%s\n",tborder); } } +} + +/* + * new PQprintTuples routine (proff@suburbia.net) + * PQprintOpt is a typedef (structure) that containes + * various flags and options. consult libpq-fe.h for + * details + */ + +void +PQprint(FILE *fout, + PGresult *res, + PQprintOpt *po + ) +{ + int nFields; + + nFields = PQnfields(res); + + if ( nFields > 0 ) { /* only print tuples with at least 1 field. */ + int i,j; + int nTups; + int *fieldMax=NULL; /* keep -Wall happy */ + unsigned char *fieldNotNum=NULL; /* keep -Wall happy */ + char **fields=NULL; /*keep -Wall happy */ + char **fieldNames; + int fieldMaxLen=0; + char *border=NULL; + int numFieldName; + int fs_len=strlen(po->fieldSep); + nTups = PQntuples(res); + if (!(fieldNames=(char **)calloc(nFields, sizeof (char *)))) + { + perror("calloc"); + exit(1); + } + if (!(fieldNotNum=(unsigned char *)calloc(nFields, 1))) + { + perror("calloc"); + exit(1); + } + if (!(fieldMax=(int *)calloc(nFields, sizeof(int)))) + { + perror("calloc"); + exit(1); + } + for (numFieldName=0; po->fieldName && po->fieldName[numFieldName]; numFieldName++); + for (j=0; j < nFields; j++) + { + int len; + char *s=(jfieldName[j][0])? po->fieldName[j]: PQfname(res, j); + fieldNames[j]=s; + len=s? strlen(s): 0; + fieldMax[j] = len; + /* + if (po->header && len<5) + len=5; + */ + len+=fs_len; + if (len>fieldMaxLen) + fieldMaxLen=len; + } + if (!po->expanded && (po->align || po->html3)) + { + if (!(fields=(char **)calloc(nFields*(nTups+1), sizeof(char *)))) + { + perror("calloc"); + exit(1); + } + } else + if (po->header && !po->html3) + { + if (po->expanded) + { + if (po->align) + fprintf(fout, "%-*s%s Value\n", fieldMaxLen-fs_len, "Field", po->fieldSep); + else + fprintf(fout, "%s%sValue\n", "Field", po->fieldSep); + } else + { + int len=0; + for (j=0; j < nFields; j++) + { + char *s=fieldNames[j]; + fputs(s, fout); + len+=strlen(s)+fs_len; + if ((j+1)fieldSep, fout); + } + fputc('\n', fout); + for (len-=fs_len; len--; fputc('-', fout)); + fputc('\n', fout); + } + } + if (po->expanded && po->html3) + { + if (po->caption) + fprintf(fout, "

%s

\n", po->caption); + else + fprintf(fout, "

Query retrieved %d tuples * %d fields

\n", nTups, nFields); + } + for (i = 0; i < nTups; i++) { + char buf[8192*2+1]; + if (po->expanded) + { + if (po->html3) + fprintf(fout, "
\n", po->tableOpt? po->tableOpt: "", i); + else + fprintf(fout, "-- RECORD %d --\n", i); + } + for (j = 0; j < nFields; j++) { + char *pval, *p, *o; + int plen; + if ((plen=PQgetlength(res,i,j))<1 || !(pval=PQgetvalue(res,i,j)) || !*pval) + { + if (po->align || po->expanded) + continue; + goto efield; + } + for (p=pval, o=buf; *p; *(o++)=*(p++)) + { + if ((fs_len==1 && (*p==*(po->fieldSep))) || *p=='\\') + *(o++)='\\'; + if (po->align && !((*p >='0' && *p<='9') || *p=='.' || *p=='E' || *p=='e' || *p==' ' || *p=='-')) + fieldNotNum[j]=1; + } + *o='\0'; + if (!po->expanded && (po->align || po->html3)) + { + int n=strlen(buf); + if (n>fieldMax[j]) + fieldMax[j]=n; + if (!(fields[i*nFields+j]=(char *)malloc(n+1))) + { + perror("malloc"); + exit(1); + } + strcpy(fields[i*nFields+j], buf); + } else + { + if (po->expanded) + { + if (po->html3) + fprintf(fout, "\n", + fieldNames[j], fieldNotNum[j]? "left": "right", buf); + else + { + if (po->align) + fprintf(fout, "%-*s%s %s\n", fieldMaxLen-fs_len, fieldNames[j], po->fieldSep, buf); + else + fprintf(fout, "%s%s%s\n", fieldNames[j], po->fieldSep, buf); + } + } + else + { + if (!po->html3) + { + fputs(buf, fout); +efield: + if ((j+1)fieldSep, fout); + else + fputc('\n', fout); + } + } + } + } + if (po->html3 && po->expanded) + fputs("
%d
%s%s
\n", fout); + } + if (!po->expanded && (po->align || po->html3)) + { + if (po->html3) + { + if (po->header) + { + if (po->caption) + fprintf(fout, "\n", po->tableOpt? po->tableOpt: "", po->caption); + else + fprintf(fout, "
%s
\n", po->tableOpt? po->tableOpt: "", nTups, nFields); + } else + fprintf(fout, "
Retrieved %d tuples * %d fields
", po->tableOpt? po->tableOpt: ""); + } + if (po->header) + { + if (po->html3) + fputs("", fout); + else + { + int tot=0; + int n=0; + char *p; + for (; nstandard? 2: 0); + if (po->standard) + tot+=fs_len*2+2; + if (!(p=border=malloc(tot+1))) + { + perror("malloc"); + exit(1); + } + if (po->standard) + { + char *fs=po->fieldSep; + while (*fs++) + *p++='+'; + } + for (j=0; j standard? 2:0) ; len--; *p++='-'); + if (po->standard || (j+1)fieldSep; + while (*fs++) + *p++='+'; + } + } + *p='\0'; + if (po->standard) + fprintf(fout, "%s\n", border); + } + if (po->standard) + fputs(po->fieldSep, fout); + for (j=0; j < nFields; j++) + { + char *s=PQfname(res, j); + if (po->html3) + { + fprintf(fout, "", fieldNotNum[j]? "left": "right", + fieldNames[j]); + } else + { + int n=strlen(s); + if (n>fieldMax[j]) + fieldMax[j]=n; + if (po->standard) + fprintf(fout, fieldNotNum[j]? " %-*s ": " %*s ", fieldMax[j], s); + else + fprintf(fout, fieldNotNum[j]? "%-*s": "%*s", fieldMax[j], s); + if (po->standard || (j+1)fieldSep, fout); + } + } + if (po->html3) + fputs("\n", fout); + else + fprintf(fout, "\n%s\n", border); + } + for (i = 0; i < nTups; i++) + { + if (po->html3) + fputs("", fout); + else + if (po->standard) + fputs(po->fieldSep, fout); + + for (j = 0; j < nFields; j++) + { + char *p=fields[i*nFields+j]; + if (po->html3) + fprintf(fout, "", fieldNotNum[j]? "left": "right", p? p: ""); + + else + { + fprintf(fout, fieldNotNum[j]? (po->standard? " %-*s ": "%-*s"): (po->standard? " %*s ": "%*s"), fieldMax[j], p? p: ""); + if (po->standard || (j+1)fieldSep, fout); + } + if (p) + free(p); + } + if (po->html3) + fputs("", fout); + else + if (po->standard) + fprintf(fout, "\n%s", border); + fputc('\n', fout); + } + free(fields); + } + free(fieldMax); + free(fieldNotNum); + free(fieldNames); + if (border) + free(border); + if (po->html3 && !po->expanded) + fputs("
%s
%s
\n", fout); + } } - --- src/libpq/libpq-fe.h.old Sat Feb 24 12:22:26 1996 +++ src/libpq/libpq-fe.h Tue Feb 27 03:37:20 1996 @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - - * $Id: libpq-fe.h,v 1.7 1996/02/24 01:22:26 jolly Exp $ + * $Id: libpq-fe.h,v 1.6 1995/08/01 20:28:21 jolly Exp $ * *------------------------------------------------------------------------- */ @@ -127,6 +127,17 @@ PGconn* conn; } PGresult; +typedef struct _PQprintOpt { + bool header; /* print output field headers or not */ + bool align; /* fill align the fields */ + bool standard; /* old brain dead format */ + bool html3; /* output html tables */ + bool expanded; /* expand tables */ + char *fieldSep; /* field separator */ + char *tableOpt; /* insert to HTML */ + char *caption; /* HTML
*/ + char **fieldName; /* null terminated array of repalcement field names */ +} PQprintOpt; /* === in fe-connect.c === */ /* make a new client connection to the backend */ @@ -165,13 +176,6 @@ extern char* PQgetvalue(PGresult *res, int tup_num, int field_num); extern int PQgetlength(PGresult *res, int tup_num, int field_num); extern void PQclear(PGresult* res); - -/* PQdisplayTuples() is a better version of PQprintTuples() */ - -extern void PQdisplayTuples(PGresult *res, - - FILE *fp, /* where to send the output */ - - int fillAlign, /* pad the fields with spaces */ - - char *fieldSep, /* field separator */ - - int printHeader, /* display headers? */ - - int quiet); extern void PQprintTuples(PGresult* res, FILE* fout, /* output stream */ int printAttName,/* print attribute names or not*/ @@ -179,6 +183,10 @@ int width /* width of column, if 0, use variable width */ ); +extern void PQprint(FILE* fout, /* output stream */ + PGresult* res, + PQprintOpt *ps /* option structure */ + ); extern PGnotify* PQnotifies(PGconn *conn); extern PGresult* PQfn(PGconn* conn, int fnid, - --- doc/man/psql.1.old Sun Feb 25 16:40:00 1996 +++ doc/man/psql.1 Tue Feb 27 01:38:32 1996 @@ -30,12 +30,10 @@ filename] [\c .BR "-h" - -help] - -.br - -.in +5n +hostname] [\c .BR "-H" - -hostname] +] [\c .BR "-l" port] @@ -59,14 +57,22 @@ .BR "-S" ] [\c - -.BR "-T" +.BR "-t" +] +[\c +.BR "-x" ] [dbname] .in -5n .SH DESCRIPTION psql is a interactive query front-end to \*(PG. It enables you to type in queries interactively, issue them to \*(PG, and see the query - -results. It is designed to be an enhanced version of the older +results. +.IR psql +can be used in a pipe sequence, and automatically detects when it +is not listening or talking to a real tty. +.IR psql +is designed to be an enhanced version of the older .IR "monitor" program. .PP @@ -128,10 +134,7 @@ .IR "filename" as the source of queries instead of reading queries interactively. .TP - -.BR "-h" - -Give help information about psql. - -.TP - -.BR "-H" " hostname" +.BR "-h" " hostname" Specifies the hostname of the machine on which the .IR postmaster is running. Defaults to the name of the local host, or the value of @@ -139,6 +142,11 @@ .SM PGHOST environment variable (if set). .TP +.BR "-H" +Turns on +.SM HTML3.0 +tabular output. +.TP .BR "-l" Lists all available databases .TP @@ -170,11 +178,17 @@ Run ins single-line mode where each query is terminated by a newline, instead of a semicolon. .TP - -.BR "-T" +.BR "-t" Turn off printing of attributes names. This is useful with the .BR -c option in shell scripts. +.TP +.BR "-x" +Turns on extended field mode. When enabled each tuple will have its field +names printed on the left with the field values printed on the right. +This is useful for tuples which are otherwise too long to fit into +one screen line. HTML tuple output supports this mode also. .PP You may set environment variables to avoid typing some of the above options. See the @@ -221,7 +235,9 @@ .SH "PSQL COMMANDS" .IP "\ea" Toggle fill justification when printing out attributes. - -.IP "\ec \fIdbname\fR" +.IP "\ec \fIcaption\fR" +Set the HTML3.0 table caption. +.IP "\eC \fIdbname\fR" Establish a connection to a new database. The previous connection is closed. .IP "\ed [\fItable\fR]" List tables in the database, or if @@ -231,9 +247,17 @@ If table name is .IR *, list all tables and column information for each tables. - -.IP "\ef \fIseparator\fR" +.IP "\ee [\fIfilename\fR]" +Edit the current querry buffer or \fIfile\fR. +.IP "\ef [\fIseparator\fR]" Set the field separator. Default is a single blank space. - -.IP "\eh \fIcommand\fR" +.IP "\eg [\fI|command\fR] | [\fIfilename\fR]" +Send the current query input buffer to the backend and optionally +save the output in +.IR filename +or pipe the output into +.IR "|command". +.IP "\eh [\fIcommand\fR]" Give syntax help on the specified SQL command. If the .IR command is not specified, list all the commands for which syntax help is @@ -242,32 +266,39 @@ is .IR *, give syntax help on all SQL commands. - -.IP \eg - -Send the current query input buffer to the backend. .IP "\ei \fIfilename\fR" Read queries from .IR filename into the query input buffer. .IP "\el" List all the databases in the server. - -.IP "\eo \fIfilename\fR" - -Send query results to - -.IR filename. - -If - -.IR filename - -is not specified, send query results to - -.IR stdout. +.IP "\eo [\fI|command\fR] | [\fIfilename\fR]" +Send query results to +.IR filename . +Or pipe into +.IR command . +If no arguments are specified, send query results to +.IR stdout . .IP "\ep" Print the current query buffer. .IP \eq Quit the psql program. - -.IP "\es \fIhistory\fR" - -Save the command line history to a file. (Only available if psql is +.IP "\er [\fIfilename\fR]" +Edit \fIfilename\fR then send the contents to the backend. +.IP "\es [\fIfilename\fR]" +Print or save the command line history to \fIfilename\fR. (Only available if psql is configured to use readline) +.IP "\eS" +Toggle standard SQL output (i.e extra borders characters). .IP "\et" Toggle display of output attribute name headers (defaults to on). - -.IP "\e! \fIcommand\fR" - -Escape to shell and execute +.IP "\ex" +Toggles extended field mode. When enabled each tuple will have its field +names printed on the left with the field values printed on the right. +This is useful for tuples which are otherwise too long to fit into +one screen line. HTML tuple output mode supports this flag too. +.IP "\e! [\fIcommand\fR]" +Escape to shell or execute .IR command. .IP \e? Get help information about the \e commands. - --- doc/man/libpq.3pq.old Sun Feb 25 16:40:10 1996 +++ doc/man/libpq.3pq Tue Feb 27 01:43:37 1996 @@ -287,27 +287,33 @@ char* PQoidStatus(PGresult *res); .)C .PP - -.b PQdisplayTuples +.b PQprint .ip - -Prints out all the tuples and, optionally, the attribute names to the - -specified output stream. The programs +Prints out all the tuples in an intelligent manner. The .b psql - -and - -.b monitor - -both use - -.i PQdisplayTuples - -for output. +program uses this function for its output. .(C - -void PQdisplayTuples( - - PGresult* res, - - FILE* fout, /* output stream */ - - int fillAlign, /* pad the fields with spaces? */ - - char *fieldSep, /* string to use as the field separator */ - - int printHeader, /* display attribute headers */ - - int quiet, /* print the number of rows returned ? */ - - ); +void PQprint( + FILE* fout, /* output stream */ + PGresult* res, /* query results */ + PQprintOpt *ps /* option structure */ + ); .)C - -.PP +.i PQprintOpt +is a typedef'ed structure as defined below. +.(C +typedef struct _PQprintOpt { + bool header; /* print output field headers */ + bool align; /* fill align the fields */ + bool standard; /* old brain dead format (needs align) */ + bool html3; /* output html3+ tables */ + bool expanded; /* expand tables */ + char *fieldSep; /* field separator */ + char *caption; /* html table caption (or NULL) */ + char **fieldName; /* null terminated array of field names (or NULL) */ +} PQprintOpt; +.)C +.lp .b PQclear .ip Frees the storage associated with the PGresult. Every query result