Patch proposal: query result history in psql

diff --git a/src/bin/psql/Makefile b/src/bin/psql/Makefile
index 5b77173..1654607 100644
--- a/src/bin/psql/Makefile
+++ b/src/bin/psql/Makefile
@@ -20,7 +20,7 @@ REFDOCDIR= $(top_srcdir)/doc/src/sgml/ref
 
 override CPPFLAGS := -I. -I$(srcdir) -I$(libpq_srcdir) -I$(top_srcdir)/src/bin/pg_dump $(CPPFLAGS)
 
-OBJS=	command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
+OBJS=	ans.o command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
 	startup.o prompt.o variables.o large_obj.o print.o describe.o \
 	tab-complete.o mbprint.o dumputils.o keywords.o kwlookup.o \
 	sql_help.o \
diff --git a/src/bin/psql/ans.c b/src/bin/psql/ans.c
new file mode 100644
index 0000000..697c50f
--- /dev/null
+++ b/src/bin/psql/ans.c
@@ -0,0 +1,463 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2013, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/ans.c
+ */
+
+#include "ans.h"
+
+#include "postgres_fe.h"
+
+static int global_ans_num = 0;
+
+static void CreateTable(PGconn *db, struct _ans* item);
+static char* GetTypeName(PGconn *db, Oid oid);
+static char* BuildData(PGresult* result);
+
+/*
+ * Creates empty entry, serving as list head
+ */
+AnsHistory CreateAnsHistory(void)
+{
+	struct _ans* head;
+	
+	head = pg_malloc(sizeof(struct _ans));
+	
+	head->numColumns = 0;
+	head->columnTypes = NULL;
+	head->columnNames = NULL;
+	head->data = NULL;
+	head->next = NULL;
+	head->tableName = NULL;
+	head->name = NULL;
+	
+	return head;
+}
+
+void
+AddToHistory(AnsHistory history, PGresult* result)
+{
+	struct _ans* item;
+	int numRows, numColumns;
+	int i;
+	char* data;
+	
+	if (!history || !result)
+		return;
+	
+	numColumns  = PQnfields(result);
+	numRows = PQntuples(result);
+	
+	if (numColumns <= 0 || numRows <= 0)
+		return;
+	
+	data = BuildData(result);
+	if (!data)
+		return;
+	
+	item = pg_malloc(sizeof(struct _ans));
+
+	/* read meta-data: column names and types */
+	item->numColumns = numColumns;
+	item->columnNames = pg_malloc(sizeof(char*) * item->numColumns);
+	item->columnTypes = pg_malloc(sizeof(Oid) * item->numColumns);
+	
+	for (i = 0; i < item->numColumns; i++)
+	{
+		char* name;
+		
+		item->columnTypes[i] = PQftype(result, i);
+		
+		name = PQfname(result, i);
+		item->columnNames[i] = pg_malloc(strlen(name) + 1);
+		strcpy(item->columnNames[i], name);
+	}
+	
+	item->data = data;
+	
+	/* Table creation deferred to when the ASN is actually used */
+	item->tableName = NULL;
+
+	/* name */
+	item->name = pg_malloc(10);
+	sprintf(item->name, "ans%d", global_ans_num++);
+	
+	printf(_("Query result stored as :%s\n"), item->name);
+	
+	item->next = history->next;
+	history->next = item;
+}
+
+const char*
+GetOrCreateTable(AnsHistory history, PGconn *db, const char* name)
+{
+	struct _ans* item;
+	
+	if (!history || !name)
+	{
+		return NULL;
+	}
+	
+	item = history->next;
+	
+	while (item)
+	{
+		if (strcmp(item->name, name) == 0)
+		{
+			if (item->tableName)
+				return item->tableName;
+			
+			CreateTable(db, item);
+			if (item->tableName)
+				return item->tableName;
+			else
+				return NULL;
+		}
+		item = item->next;
+	}
+	
+	return NULL;
+}
+
+static
+void 
+CreateTable(PGconn *db, struct _ans* item)
+{
+	char tableNameBuf[32];
+	char copyQuery[64];
+	const int qbufsize=2048;
+	char queryBuf[qbufsize];
+	char* queryPtr;
+	int len;
+	int i;
+	PGresult* qres;
+	
+	if (!item)
+		return;
+	
+	len = snprintf(tableNameBuf, 32, "_table_%s", item->name);
+	if (len >= 32)
+	{
+		return;
+	}
+	len = snprintf(copyQuery, 64, "COPY %s FROM STDIN", tableNameBuf);
+	if (len >= 64)
+	{
+		return;
+	}
+	
+	/* Build CREATE TABLE query */
+	queryPtr = queryBuf;
+	queryPtr += snprintf(queryPtr, qbufsize-(int)(queryPtr-queryBuf), "CREATE TEMP TABLE %s (\n", tableNameBuf);
+	
+	for (i = 0; i < item->numColumns; ++i)
+	{
+		char* typeName;
+		const char* format;
+		
+		typeName = GetTypeName(db, item->columnTypes[i]);
+		if (!typeName)
+			return;
+		
+		if (i == item->numColumns-1)
+			format = "%s %s\n";
+		else
+			format = "%s %s,\n";
+			
+		queryPtr += snprintf(queryPtr, qbufsize-(int)(queryPtr-queryBuf), format, item->columnNames[i], typeName);
+		free(typeName);
+		if (queryPtr >= queryBuf+qbufsize)
+		{
+			return; 
+		}
+	}
+	queryPtr += snprintf(queryPtr, qbufsize-(int)(queryPtr-queryBuf), ");");
+	if (queryPtr >= queryBuf+qbufsize)
+	{
+		return; 
+	}
+	
+	/* create and populate table */
+	PQclear(PQexec(db, "BEGIN"));
+	qres = PQexec(db, queryBuf);
+	if (PQresultStatus(qres) != PGRES_COMMAND_OK)
+	{
+		PQclear(qres);
+		PQclear(PQexec(db, "ROLLBACK"));
+		return;
+	}
+	PQclear(qres);
+
+	qres = PQexec(db, copyQuery);
+	if (PQresultStatus(qres) != PGRES_COPY_IN)
+	{
+		PQclear(qres);
+		PQclear(PQexec(db, "ROLLBACK"));
+		return;
+	}
+	PQclear(qres);
+	
+	PQputCopyData(db, item->data, strlen(item->data));
+	len = PQputCopyEnd(db, NULL);
+	if (len != 1)
+	{
+		PQclear(PQexec(db, "ROLLBACK"));
+		return;
+	}
+	qres = PQgetResult(db);
+	if (PQresultStatus(qres) != PGRES_COMMAND_OK)
+	{
+		PQclear(qres);
+		PQclear(PQexec(db, "ROLLBACK"));
+		return;
+	}
+	PQclear(qres);
+	PQclear(PQexec(db, "COMMIT"));
+	
+	item->tableName = pg_strdup(tableNameBuf);
+}
+
+/* free the typename after use */
+static
+char* 
+GetTypeName(PGconn *db, Oid oid)
+{
+	const int bufsize = 1024;
+	char queryBuf[bufsize];
+	int sres;
+	PGresult* qres;
+	char* typeName;
+	
+	sres = snprintf(queryBuf, bufsize, "SELECT typname FROM pg_type WHERE oid=%d;", oid);
+	if ( sres < 0 || sres > bufsize)
+		return NULL;
+	
+	qres = PQexec(db, queryBuf);
+	if (!qres)
+		return NULL;
+	
+	if (PQresultStatus(qres) != PGRES_TUPLES_OK)
+	{
+		PQclear(qres);
+		return NULL;
+	}
+	
+	if (PQntuples(qres) != 1 && PQnfields(qres) != 1)
+	{
+		PQclear(qres);
+		return NULL;
+	}
+
+	typeName = pg_strdup(PQgetvalue(qres, 0, 0));
+	PQclear(qres);
+	return typeName;
+}
+
+/* Returns lenght of string after escaping */
+static
+int GetEscapedLen(const char* c)
+{
+	int len = 0;
+	for(; *c; c++)
+	{
+		switch(*c)
+		{
+			case '\\':
+			case '\b':
+			case '\f':
+			case '\r':
+			case '\n':
+			case '\t':
+			case '\v':
+				len++;
+			default:
+				len++;
+		}
+	}
+	
+	return len;
+}
+
+/* Copies c to dst, escaping. Retuns pointer past the last character copied.
+ * Assumes theere is enough room in the dst buffer
+ */
+static
+char*
+EscapeForCopy(char* dst, const char* c)
+{
+	for(; *c; c++)
+	{
+		switch(*c)
+		{
+			case '\\':
+				*(dst++) = '\\';
+				*(dst++) = '\\';
+				break;
+				
+			case '\b':
+				*(dst++) = '\\';
+				*(dst++) = 'b';
+				break;
+				
+			case '\f':
+				*(dst++) = '\\';
+				*(dst++) = 'f';
+				break;
+				
+			case '\r':
+				*(dst++) = '\\';
+				*(dst++) = 'r';
+				break;
+				
+			case '\n':
+				*(dst++) = '\\';
+				*(dst++) = 'n';
+				break;
+				
+			case '\t':
+				*(dst++) = '\\';
+				*(dst++) = 't';
+				break;
+				
+			case '\v':
+				*(dst++) = '\\';
+				*(dst++) = 'v';
+				break;
+				
+			default:
+				*(dst++) = *c;
+		}
+	}
+	return dst;
+}
+
+/* Convert query data into data buffer in COPY TEXT format.
+ * returns NULL on error
+ * caller owns the data
+ */
+static 
+char* 
+BuildData(PGresult* result)
+{
+	int cols, rows;
+	int bufferSize;
+	int r,c;
+	char* buffer;
+	char* dataPtr;
+	
+	if (!result)
+		return NULL;
+	
+	cols = PQnfields(result);
+	rows = PQntuples(result);
+	
+	/* first pass - measure the size */
+	bufferSize = 0;
+	for(r = 0; r < rows; r++)
+	{
+		for(c = 0; c < cols; c++)
+		{
+			if (PQgetisnull(result, r, c))
+			{
+				bufferSize += 2; /* 2 = size of null literal \N */
+			}
+			else
+			{
+				bufferSize += GetEscapedLen(PQgetvalue(result, r, c));
+			}
+			bufferSize +=1; /* column or row delimiter*/
+		}
+	}
+	bufferSize += 4; /* end-of-data marker \. + NULL terminiator */
+	
+	/* second pass = build the buffer */
+	buffer = pg_malloc(bufferSize);
+	dataPtr = buffer;
+	
+	for(r = 0; r < rows; r++)
+	{
+		for(c = 0; c < cols; c++)
+		{
+			if (PQgetisnull(result, r, c))
+			{
+				strcpy(dataPtr, "\\N");
+				dataPtr += 2;
+			}
+			else
+			{
+				char* value = PQgetvalue(result, r, c);
+				dataPtr = EscapeForCopy(dataPtr, value);
+			}
+			if (c == cols-1)
+			{
+				strcpy(dataPtr, "\n");
+			}
+			else
+			{
+				strcpy(dataPtr, "\t");
+			}
+			dataPtr += 1;
+		}
+	}
+	strcpy(dataPtr, "\\.\n");
+	
+	return buffer;
+}
+
+void AnsClearTableNames(AnsHistory history)
+{
+	struct _ans* item = history;
+	
+	while(item->next)
+	{
+		item = item->next;
+		
+		if (item->tableName)
+		{
+			free(item->tableName);
+			item->tableName = NULL;
+		}
+	}
+	
+}
+
+void
+DestroyAnsHistory(PGconn *db, AnsHistory item)
+{
+	if (!item)
+		return;
+	
+	if (item->next)
+		DestroyAnsHistory(db, item->next);
+	
+	if (item->data)
+		free(item->data);
+	
+	if(item->name)
+		free(item->name);
+	
+	if (item->columnTypes)
+		free(item->columnTypes);
+	
+	if (item->numColumns && item->columnNames)
+	{
+		int i;
+		for(i = 0; i < item->numColumns; i++)
+			free(item->columnNames[i]);
+		
+		free(item->columnNames);
+	}
+	
+	if (item->tableName)
+	{
+		const int bufsize = 32;
+		char deleteQuery[bufsize];
+
+		snprintf(deleteQuery, bufsize, "DROP TABLE %s", item->tableName);
+		PQclear(PQexec(db, deleteQuery));
+	}
+	
+	free(item);
+}
diff --git a/src/bin/psql/ans.h b/src/bin/psql/ans.h
new file mode 100644
index 0000000..033630d
--- /dev/null
+++ b/src/bin/psql/ans.h
@@ -0,0 +1,48 @@
+/*
+ * psql - the PostgreSQL interactive terminal
+ *
+ * Copyright (c) 2013, PostgreSQL Global Development Group
+ *
+ * src/bin/psql/ans.h
+ */
+#ifndef ANS_H
+#define ANS_H
+
+#include "libpq-fe.h"
+
+
+/* Cache of last N query results, stored in a format ready to insert itno tempary table when needed */
+struct _ans 
+{
+	int		numColumns;
+	Oid		*columnTypes;
+	char	**columnNames;
+	char	*data;
+	
+	char	*name; // history item name
+	char	*tableName; // corresponding table in DB. NULL if not created yet
+	
+	struct _ans	*next;
+};
+
+typedef struct _ans* AnsHistory;
+
+AnsHistory	CreateAnsHistory(void);
+void		AddToHistory(AnsHistory history, PGresult* result);
+void		DestroyAnsHistory(PGconn *db, AnsHistory item);
+
+
+/*
+ * Takes variable name and checks wether it matches the name of existing history item.
+ * If none found, returns NULL.
+ * If found, but table not created yet, creates the table first.
+ */
+const char* GetOrCreateTable(AnsHistory history, PGconn *db, const char* name);
+
+/* Should be called when new connection is open.
+ * Because all the tables used by ANS are temporary and kept in current DB, thety are no longer valid
+ * after the client connect to new database
+ */
+void AnsClearTableNames(AnsHistory history);
+
+#endif
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0e99794..550eebc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -40,6 +40,7 @@
 #include "pqexpbuffer.h"
 #include "dumputils.h"
 
+#include "ans.h"
 #include "common.h"
 #include "copy.h"
 #include "describe.h"
@@ -51,6 +52,7 @@
 #include "psqlscan.h"
 #include "settings.h"
 #include "variables.h"
+#include "ans.h"
 
 
 /* functions for use in this file */
@@ -205,6 +207,40 @@ exec_command(const char *cmd,
 			success = do_pset("format", "unaligned", &pset.popt, pset.quiet);
 	}
 
+	/* \ans - toggle query result history */
+	else if (strcmp(cmd, "ans") == 0)
+	{
+		char	 *opt = psql_scan_slash_option(scan_state,
+												 OT_NORMAL, NULL, false);
+
+		if (opt)
+			pset.ans_enabled = ParseVariableBool(opt);
+		else
+			pset.ans_enabled = !pset.ans_enabled;
+		
+		if (!pset.quiet)
+		{
+			if (pset.ans_enabled)
+				puts(_("Query result history is on."));
+			else
+			{
+				puts(_("Query result history is off."));
+			}
+		}
+		free(opt);
+	}
+	/* \ansclean - clear query result history */
+	else if (strcmp(cmd, "ansclean") == 0)
+	{
+		DestroyAnsHistory(pset.db, pset.ans);
+		pset.ans = CreateAnsHistory();
+		
+		if (!pset.quiet)
+		{
+			puts(_("Query result history cleaned."));
+		}
+	}
+
 	/* \C -- override table title (formerly change HTML caption) */
 	else if (strcmp(cmd, "C") == 0)
 	{
@@ -1697,6 +1733,7 @@ do_connect(char *dbname, char *user, char *host, char *port)
 	PQsetNoticeProcessor(n_conn, NoticeProcessor, NULL);
 	pset.db = n_conn;
 	SyncVariables();
+	AnsClearTableNames(pset.ans);
 	connection_warnings(false); /* Must be after SyncVariables */
 
 	/* Tell the user about the new connection */
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 3dea92c..579bf45 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -23,6 +23,7 @@
 #include "command.h"
 #include "copy.h"
 #include "mbprint.h"
+#include "ans.h"
 
 
 
@@ -950,7 +951,11 @@ SendQuery(const char *query)
 
 		/* but printing results isn't: */
 		if (OK && results)
+		{
 			OK = PrintQueryResults(results);
+			if (pset.ans_enabled)
+				AddToHistory(pset.ans, results);
+		}
 	}
 	else
 	{
@@ -1227,7 +1232,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
 		}
 
 		printQuery(results, &my_popt, pset.queryFout, pset.logfile);
-
+		
 		PQclear(results);
 
 		/* after the first result set, disallow header decoration */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 379dead..729b97a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -289,6 +289,11 @@ slashUsage(unsigned short int pager)
 					  "  \\lo_import FILE [COMMENT]\n"
 					  "  \\lo_list\n"
 					  "  \\lo_unlink LOBOID      large object operations\n"));
+	fprintf(output, "\n");
+
+	fprintf(output, _("Query result history\n"));
+	fprintf(output, _("  \\ans [on|off]          toggle query result history (currently %s)\n"), ON(pset.ans_enabled));
+	fprintf(output, _("  \\ansclean              clean query result history\n"));
 
 	ClosePager(output);
 }
diff --git a/src/bin/psql/psqlscan.l b/src/bin/psql/psqlscan.l
index d61387d..686fbcc 100644
--- a/src/bin/psql/psqlscan.l
+++ b/src/bin/psql/psqlscan.l
@@ -737,11 +737,24 @@ other			.
 					}
 					else
 					{
-						/*
-						 * if the variable doesn't exist we'll copy the
-						 * string as is
+						/* 
+						 * This could be ANS variable, check it.
 						 */
-						ECHO;
+						const char* tableName;
+						
+						tableName = GetOrCreateTable(pset.ans, pset.db, varname);
+						if (tableName)
+						{
+							push_new_buffer(tableName, varname);
+						}
+						else
+						{
+							/*
+							* if the variable doesn't exist we'll copy the
+							* string as is
+							*/
+							ECHO;
+						}
 					}
 
 					free(varname);
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index e78aa9a..62c9137 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -11,6 +11,7 @@
 
 #include "variables.h"
 #include "print.h"
+#include "ans.h"
 
 #define DEFAULT_FIELD_SEP "|"
 #define DEFAULT_RECORD_SEP "\n"
@@ -93,6 +94,9 @@ typedef struct _psqlSettings
 	FILE	   *logfile;		/* session log file handle */
 
 	VariableSpace vars;			/* "shell variable" repository */
+	
+	AnsHistory	ans;			/* query result (answer) history.*/
+	bool		ans_enabled;	/* local query result storage enabled */
 
 	/*
 	 * The remaining fields are set by assign hooks associated with entries in
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 5d7fe6e..e0a7799 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -147,6 +147,9 @@ main(int argc, char *argv[])
 	SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
 	SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
 	SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
+	
+	pset.ans = CreateAnsHistory();
+	pset.ans_enabled = 0;
 
 	parse_psql_options(argc, argv, &options);
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7d2c812..1d41587 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -891,7 +891,7 @@ psql_completion(char *text, int start, int end)
 	};
 
 	static const char *const backslash_commands[] = {
-		"\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright",
+		"\\a", "\\ans", "\\ansclean", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright",
 		"\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\df",
 		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
 		"\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du",