proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..2cf54bf 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,27 @@ exec_command(const char *cmd,
 		status = PSQL_CMD_SEND;
 	}
 
+	/* \gstore [filename], \gbstore [filename] -- send query and store result in (binary) file */
+	else if (strcmp(cmd, "gstore") == 0 ||
+			  (strcmp(cmd, "gbstore") == 0))
+	{
+		char	   *fname = psql_scan_slash_option(scan_state,
+												   OT_FILEPIPE, NULL, false);
+
+		if (!fname)
+			pset.gfname = pg_strdup("");
+		else
+		{
+			expand_tilde(&fname);
+			pset.gfname = pg_strdup(fname);
+		}
+
+		pset.raw_flag = true;
+		pset.binary_result = (strcmp(cmd, "gbstore") == 0);
+		free(fname);
+		status = PSQL_CMD_SEND;
+	}
+
 	/* help */
 	else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
 	{
@@ -1064,7 +1085,6 @@ exec_command(const char *cmd,
 		free(opt2);
 	}
 
-
 	/* \o -- set query output */
 	else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0)
 	{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..b2e437a 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -854,6 +854,85 @@ StoreQueryTuple(const PGresult *result)
 	return success;
 }
 
+/*
+ * StoreRawResult: the returned value (possibly binary) is displayed
+ * or stored in file. The result should be exactly one row, one column.
+ */
+static bool
+StoreRawResult(const PGresult *result)
+{
+	bool		success = true;
+
+	if (PQntuples(result) < 1)
+	{
+		psql_error("no rows returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQntuples(result) > 1)
+	{
+		psql_error("more than one row returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) < 1)
+	{
+		psql_error("no columns returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) > 1)
+	{
+		psql_error("more than one column returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQgetisnull(result, 0, 0))
+	{
+		psql_error("returned value is null for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else
+	{
+		char	   *value;
+		int			length;
+		FILE	   *fout = NULL;
+		bool		is_pipe = false;
+
+		value = PQgetvalue(result, 0, 0);
+		length = PQgetlength(result, 0, 0);
+
+		if (pset.gfname && *(pset.gfname) != '\0')
+		{
+			if (!openQueryOutputFile(pset.gfname, &fout, &is_pipe))
+				success = false;
+			if (success && is_pipe)
+				disable_sigpipe_trap();
+		}
+
+		if (success)
+		{
+			success = fwrite(value, 1, length, fout != NULL ? fout : pset.queryFout) == length;
+			if (!success)
+				psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (success)
+				success = fflush(fout != NULL ? fout : pset.queryFout) == 0;
+
+			if (!success)
+				psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (fout != NULL)
+			{
+				if (is_pipe)
+				{
+					pclose(fout);
+					restore_sigpipe_trap();
+				}
+				else
+					fclose(fout);
+			}
+		}
+	}
+
+	return success;
+}
 
 /*
  * ExecQueryTuples: assuming query result is OK, execute each query
@@ -1124,6 +1203,8 @@ PrintQueryResults(PGresult *results)
 				success = ExecQueryTuples(results);
 			else if (pset.crosstab_flag)
 				success = PrintResultsInCrosstab(results);
+			else if (pset.raw_flag)
+				success = StoreRawResult(results);
 			else
 				success = PrintQueryTuples(results);
 			/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1278,7 +1359,8 @@ SendQuery(const char *query)
 	}
 
 	if (pset.fetch_count <= 0 || pset.gexec_flag ||
-		pset.crosstab_flag || !is_select_command(query))
+		pset.crosstab_flag || !is_select_command(query) ||
+		pset.raw_flag)
 	{
 		/* Default fetch-it-all-and-print mode */
 		instr_time	before,
@@ -1287,7 +1369,16 @@ SendQuery(const char *query)
 		if (pset.timing)
 			INSTR_TIME_SET_CURRENT(before);
 
-		results = PQexec(pset.db, query);
+		if (pset.binary_result)
+			results = PQexecParams(pset.db, query,
+									0,
+									NULL,
+									NULL,
+									NULL,
+									NULL,
+									pset.binary_result);
+		else
+			results = PQexec(pset.db, query);
 
 		/* these operations are included in the timing result: */
 		ResetCancelConn();
@@ -1404,7 +1495,7 @@ SendQuery(const char *query)
 
 sendquery_cleanup:
 
-	/* reset \g's output-to-filename trigger */
+	/* reset \g, \g[b]store output-to-filename trigger */
 	if (pset.gfname)
 	{
 		free(pset.gfname);
@@ -1421,6 +1512,10 @@ sendquery_cleanup:
 	/* reset \gexec trigger */
 	pset.gexec_flag = false;
 
+	/* reset \gstore, gbstore trigger */
+	pset.raw_flag = false;
+	pset.binary_result = false;
+
 	/* reset \crosstabview trigger */
 	pset.crosstab_flag = false;
 	for (i = 0; i < lengthof(pset.ctv_args); i++)
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..216833e 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -95,6 +95,8 @@ typedef struct _psqlSettings
 	bool		gexec_flag;		/* one-shot flag to execute query's results */
 	bool		crosstab_flag;	/* one-shot request to crosstab results */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
+	bool		raw_flag;		/* one-shot flag to work with exact one value */
+	bool		binary_result;	/* one-shot flag - enforce binary result format */
 
 	bool		notty;			/* stdin or stdout is not a tty (as determined
 								 * on startup) */

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..7e2fa96 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1944,6 +1944,31 @@ hello 10
         </listitem>
       </varlistentry>
 
+
+      <varlistentry>
+        <term><literal>\gstore [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+        <term><literal>\gstore [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
+        <term><literal>\gbstore [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
+        <term><literal>\gbstore [ |<replaceable class="parameter">command</replaceable> ]</literal></term>
+        <listitem>
+        <para>
+         Sends the current query input buffer to the server and stores the
+         raw query's output into stores the query's output in <replaceable
+         class="parameter">filename</replaceable> or pipes the output
+         to the shell command <replaceable
+         class="parameter">command</replaceable>.  The file or command is
+         written to only if the query successfully returns exactly one row
+         one column non null result, not if the query fails or is a 
+         non-data-returning SQL command. For example:
+<programlisting>
+=&gt; <userinput>SELECT avatar FROM users WHERE id = 123</userinput>
+-&gt; <userinput>\gbstore ~/avatar.png</userinput>
+</programlisting>
+        </para>
+        </listitem>
+      </varlistentry>
+
+
       <varlistentry>
         <term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
         <listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..e8fabb9 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,27 @@ exec_command(const char *cmd,
 		status = PSQL_CMD_SEND;
 	}
 
+	/* \gstore [filename], \gbstore [filename] -- send query and store result in (binary) file */
+	else if (strcmp(cmd, "gstore") == 0 ||
+			  (strcmp(cmd, "gbstore") == 0))
+	{
+		char	   *fname = psql_scan_slash_option(scan_state,
+												   OT_FILEPIPE, NULL, false);
+
+		if (!fname)
+			pset.gfname = pg_strdup("");
+		else
+		{
+			expand_tilde(&fname);
+			pset.gfname = pg_strdup(fname);
+		}
+
+		pset.raw_flag = true;
+		pset.binres_flag = (strcmp(cmd, "gbstore") == 0);
+		free(fname);
+		status = PSQL_CMD_SEND;
+	}
+
 	/* help */
 	else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
 	{
@@ -1064,7 +1085,6 @@ exec_command(const char *cmd,
 		free(opt2);
 	}
 
-
 	/* \o -- set query output */
 	else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0)
 	{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..d4b4f15 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -854,6 +854,85 @@ StoreQueryTuple(const PGresult *result)
 	return success;
 }
 
+/*
+ * StoreRawResult: the returned value (possibly binary) is displayed
+ * or stored in file. The result should be exactly one row, one column.
+ */
+static bool
+StoreRawResult(const PGresult *result)
+{
+	bool		success = true;
+
+	if (PQntuples(result) < 1)
+	{
+		psql_error("no rows returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQntuples(result) > 1)
+	{
+		psql_error("more than one row returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) < 1)
+	{
+		psql_error("no columns returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) > 1)
+	{
+		psql_error("more than one column returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQgetisnull(result, 0, 0))
+	{
+		psql_error("returned value is null for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else
+	{
+		char	   *value;
+		int			length;
+		FILE	   *fout = NULL;
+		bool		is_pipe = false;
+
+		value = PQgetvalue(result, 0, 0);
+		length = PQgetlength(result, 0, 0);
+
+		if (pset.gfname && *(pset.gfname) != '\0')
+		{
+			if (!openQueryOutputFile(pset.gfname, &fout, &is_pipe))
+				success = false;
+			if (success && is_pipe)
+				disable_sigpipe_trap();
+		}
+
+		if (success)
+		{
+			success = fwrite(value, 1, length, fout != NULL ? fout : pset.queryFout) == length;
+			if (!success)
+				psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (success)
+				success = fflush(fout != NULL ? fout : pset.queryFout) == 0;
+
+			if (!success)
+				psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (fout != NULL)
+			{
+				if (is_pipe)
+				{
+					pclose(fout);
+					restore_sigpipe_trap();
+				}
+				else
+					fclose(fout);
+			}
+		}
+	}
+
+	return success;
+}
 
 /*
  * ExecQueryTuples: assuming query result is OK, execute each query
@@ -1124,6 +1203,8 @@ PrintQueryResults(PGresult *results)
 				success = ExecQueryTuples(results);
 			else if (pset.crosstab_flag)
 				success = PrintResultsInCrosstab(results);
+			else if (pset.raw_flag)
+				success = StoreRawResult(results);
 			else
 				success = PrintQueryTuples(results);
 			/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1278,7 +1359,8 @@ SendQuery(const char *query)
 	}
 
 	if (pset.fetch_count <= 0 || pset.gexec_flag ||
-		pset.crosstab_flag || !is_select_command(query))
+		pset.crosstab_flag || !is_select_command(query) ||
+		pset.raw_flag)
 	{
 		/* Default fetch-it-all-and-print mode */
 		instr_time	before,
@@ -1287,7 +1369,16 @@ SendQuery(const char *query)
 		if (pset.timing)
 			INSTR_TIME_SET_CURRENT(before);
 
-		results = PQexec(pset.db, query);
+		if (pset.binres_flag)
+			results = PQexecParams(pset.db, query,
+									0,
+									NULL,
+									NULL,
+									NULL,
+									NULL,
+									pset.binres_flag);
+		else
+			results = PQexec(pset.db, query);
 
 		/* these operations are included in the timing result: */
 		ResetCancelConn();
@@ -1404,7 +1495,7 @@ SendQuery(const char *query)
 
 sendquery_cleanup:
 
-	/* reset \g's output-to-filename trigger */
+	/* reset \g, \g[b]store output-to-filename trigger */
 	if (pset.gfname)
 	{
 		free(pset.gfname);
@@ -1421,6 +1512,10 @@ sendquery_cleanup:
 	/* reset \gexec trigger */
 	pset.gexec_flag = false;
 
+	/* reset \gstore, gbstore trigger */
+	pset.raw_flag = false;
+	pset.binres_flag = false;
+
 	/* reset \crosstabview trigger */
 	pset.crosstab_flag = false;
 	for (i = 0; i < lengthof(pset.ctv_args); i++)
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index a69c4dd..7f337f9 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -168,7 +168,7 @@ slashUsage(unsigned short int pager)
 	 * Use "psql --help=commands | wc" to count correctly.  It's okay to count
 	 * the USE_READLINE line even in builds without that.
 	 */
-	output = PageOutput(113, pager ? &(pset.popt.topt) : NULL);
+	output = PageOutput(115, pager ? &(pset.popt.topt) : NULL);
 
 	fprintf(output, _("General\n"));
 	fprintf(output, _("  \\copyright             show PostgreSQL usage and distribution terms\n"));
@@ -176,6 +176,8 @@ slashUsage(unsigned short int pager)
 	fprintf(output, _("  \\g [FILE] or ;         execute query (and send results to file or |pipe)\n"));
 	fprintf(output, _("  \\gexec                 execute query, then execute each value in its result\n"));
 	fprintf(output, _("  \\gset [PREFIX]         execute query and store results in psql variables\n"));
+	fprintf(output, _("  \\gstore [FILE]         execute query and store result to file or |pipe\n"));
+	fprintf(output, _("  \\gbstore [FILE]        execute query and store bin result to file or |pipe\n"));
 	fprintf(output, _("  \\q                     quit psql\n"));
 	fprintf(output, _("  \\crosstabview [COLUMNS] execute query and display results in crosstab\n"));
 	fprintf(output, _("  \\watch [SEC]           execute query every SEC seconds\n"));
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 8cfe9d2..74a99e6 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -95,6 +95,8 @@ typedef struct _psqlSettings
 	bool		gexec_flag;		/* one-shot flag to execute query's results */
 	bool		crosstab_flag;	/* one-shot request to crosstab results */
 	char	   *ctv_args[4];	/* \crosstabview arguments */
+	bool		raw_flag;		/* one-shot flag to work with exact one value */
+	bool		binres_flag;	/* one-shot flag - enforce binary result format */
 
 	bool		notty;			/* stdin or stdout is not a tty (as determined
 								 * on startup) */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index cd64c39..166e3a7 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1336,7 +1336,8 @@ psql_completion(const char *text, int start, int end)
 		"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS",
 		"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
 		"\\e", "\\echo", "\\ef", "\\encoding", "\\errverbose", "\\ev",
-		"\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
+		"\\f", "\\g", "\\gbstore", "\\gexec", "\\gset", "gstore",
+		"\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
 		"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
 		"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
 		"\\s", "\\set", "\\setenv", "\\sf", "\\sv", "\\t", "\\T",
@@ -3240,8 +3241,8 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
 	else if (TailMatchesCS1("\\sv*"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
-	else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\i|\\include|"
-							"\\ir|\\include_relative|\\o|\\out|"
+	else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\gbstore|\\gstore|"
+							"\\i|\\include|\\ir|\\include_relative|\\o|\\out|"
 							"\\s|\\w|\\write|\\lo_import"))
 	{
 		completion_charp = "\\";