Fwd: Stalled post to pgsql-hackers

*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
***************
*** 1483,1490 **** testdb=>
          way. Use <command>\i</command> for that.) This means that
          if the query ends with (or contains) a semicolon, it is
          immediately executed. Otherwise it will merely wait in the
!         query buffer; type semicolon or <literal>\g</> to send it, or
!         <literal>\r</> to cancel.
          </para>
  
          <para>
--- 1483,1490 ----
          way. Use <command>\i</command> for that.) This means that
          if the query ends with (or contains) a semicolon, it is
          immediately executed. Otherwise it will merely wait in the
!         query buffer; type semicolon, <literal>\g</> or
!         <literal>\gset</literal> to send it, or <literal>\r</> to cancel.
          </para>
  
          <para>
***************
*** 1617,1622 **** Tue Oct 26 21:40:57 CEST 1999
--- 1617,1648 ----
        </varlistentry>
  
        <varlistentry>
+         <term><literal>\gset</literal> <replaceable class="parameter">variable</replaceable> [ ,<replaceable class="parameter">variable</replaceable> ... ] </term>
+ 
+         <listitem>
+         <para>
+          Sends the current query input buffer to the server and stores the
+          query's output into corresponding <replaceable
+          class="parameter">variable</replaceable>.  The preceding query must
+          return only one row, and the number of variables must be same as the
+          number of elements in <command>SELECT</command> list.  If you don't
+          need any of items in <command>SELECT</command> list, you can omit
+          corresponding <replaceable class="parameter">variable</replaceable>.
+          Example:
+ <programlisting>
+ foo=&gt; SELECT 'hello', 'wonderful', 'world!' \gset var1,,var3 
+ foo=&gt; \echo :var1 :var3
+ hello world!
+ </programlisting>
+         </para>
+         <para>
+          When this command fails, then related <replaceable
+          class="parameter">variables</replaceable> has undefined content.
+         </para>
+         </listitem>
+       </varlistentry>
+ 
+       <varlistentry>
          <term><literal>\h</literal> or <literal>\help</literal> <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
          <listitem>
          <para>
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 71,77 **** static void printSSLInfo(void);
  static void checkWin32Codepage(void);
  #endif
  
! 
  
  /*----------
   * HandleSlashCmds:
--- 71,86 ----
  static void checkWin32Codepage(void);
  #endif
  
! /*
!  * Possible states for simple state machine, that is used for
!  * parsing target list - list of varnames separated by comma.
!  */
! typedef enum
! {
! 	VARLIST_INITIAL,
! 	VARLIST_EXPECTED_COMMA,
! 	VARLIST_EXPECTED_COMMA_OR_IDENT
! } VarlistParserState;
  
  /*----------
   * HandleSlashCmds:
***************
*** 748,753 **** exec_command(const char *cmd,
--- 757,870 ----
  		status = PSQL_CMD_SEND;
  	}
  
+ 	/* \gset send query and store result */
+ 	else if (strcmp(cmd, "gset") == 0)
+ 	{
+ 		char *packet;
+ 		VarlistParserState state = VARLIST_INITIAL;
+ 
+ 		/* expected valid target list */
+ 		success = true;
+ 		pset.gvars = NULL;
+ 
+ 		/*
+ 		 * So we would to enable subtitution, but we know so target varname should
+ 		 * not use comma. Recheck of variable name is done by variable setting routine.
+ 		 * psql_scan_slash_option can returns zero or more identifiers separated by
+ 		 * comma. We cennot to use OT_WHOLE_LINE, because variable substitution is not
+ 		 * supported there.
+ 		 */
+ 		while ((packet = psql_scan_slash_option(scan_state,
+ 											 OT_NORMAL, NULL, false)))
+ 		{
+ 			char *token = packet;
+ 			bool process_comma = false;
+ 
+ 			while (success && (*token || process_comma))
+ 			{
+ 				if (process_comma)
+ 				{
+ 					if (state == VARLIST_INITIAL ||
+ 							state == VARLIST_EXPECTED_COMMA_OR_IDENT)
+ 						pset.gvars = tglist_add(pset.gvars, NULL);
+ 					state = VARLIST_EXPECTED_COMMA_OR_IDENT;
+ 
+ 					process_comma = false;
+ 				}
+ 				else
+ 				{
+ 					char *ptr = token;
+ 
+ 					/* skip initial blank chars */
+ 					while (*ptr && isblank(*ptr))
+ 						ptr++;
+ 
+ 					token = ptr;
+ 
+ 					/* search separator */
+ 					while (*ptr && *ptr != ',')
+ 						ptr++;
+ 
+ 					/* replace separator by zero char */
+ 					if (*ptr == ',')
+ 					{
+ 						process_comma = true;
+ 						*ptr = '\0';
+ 					}
+ 
+ 					/* process token when it is non empty string */
+ 					if (token != ptr)
+ 					{
+ 						if (state == VARLIST_INITIAL ||
+ 								state == VARLIST_EXPECTED_COMMA_OR_IDENT)
+ 						{
+ 							pset.gvars = tglist_add(pset.gvars, token);
+ 							state = VARLIST_EXPECTED_COMMA;
+ 						}
+ 						else
+ 						{
+ 							success = false;
+ 							break;
+ 						}
+ 					}
+ 
+ 					/* move pointer to next token when comma is found */
+ 					if (process_comma)
+ 						token = ptr + 1;
+ 					else
+ 						/* leave when there are no next token */
+ 						break;
+ 				}
+ 			}
+ 			free(packet);
+ 		}
+ 
+ 		/* final check and target list completation */
+ 		if (pset.gvars != NULL)
+ 		{
+ 			if (success)
+ 			{
+ 				if (state == VARLIST_EXPECTED_COMMA_OR_IDENT)
+ 					pset.gvars = tglist_add(pset.gvars, NULL);
+ 				status = PSQL_CMD_SEND;
+ 			}
+ 			else
+ 			{
+ 				psql_error("\\%s: syntax error\n", cmd);
+ 				tglist_free(pset.gvars);
+ 				pset.gvars = NULL;
+ 				status = PSQL_CMD_NOSEND;
+ 
+ 			}
+ 		}
+ 		else
+ 		{
+ 			psql_error("\\%s: missing required argument\n", cmd);
+ 			success = false;
+ 			status = PSQL_CMD_NOSEND;
+ 		}
+ 	}
+ 
  	/* help */
  	else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
  	{
*** a/src/bin/psql/command.h
--- b/src/bin/psql/command.h
***************
*** 16,21 **** typedef enum _backslashResult
--- 16,22 ----
  {
  	PSQL_CMD_UNKNOWN = 0,		/* not done parsing yet (internal only) */
  	PSQL_CMD_SEND,				/* query complete; send off */
+ 	PSQL_CMD_NOSEND,			/* query complete, don't send */
  	PSQL_CMD_SKIP_LINE,			/* keep building query */
  	PSQL_CMD_TERMINATE,			/* quit program */
  	PSQL_CMD_NEWEDIT,			/* query buffer was changed (e.g., via \e) */
*** a/src/bin/psql/common.c
--- b/src/bin/psql/common.c
***************
*** 816,821 **** PrintQueryResults(PGresult *results)
--- 816,925 ----
  	return success;
  }
  
+ /*
+  * StoreQueryResult: store first row of result to selected variables
+  *
+  * Note: Utility function for use by SendQuery() only.
+  *
+  * Returns true if the query executed successfully, false otherwise.
+  */
+ static bool
+ StoreQueryResult(PGresult *result)
+ {
+ 	bool success;
+ 
+ 	switch (PQresultStatus(result))
+ 	{
+ 		case PGRES_TUPLES_OK:
+ 			{
+ 				int	i;
+ 
+ 				if (PQntuples(result) < 1)
+ 				{
+ 					psql_error("no data found\n");
+ 					success = false;
+ 				}
+ 				else if (PQntuples(result) > 1)
+ 				{
+ 					psql_error("too many rows\n");
+ 					success = false;
+ 				}
+ 				else
+ 				{
+ 					TargetListData *iter = (TargetListData *) pset.gvars;
+ 
+ 					success = true;
+ 
+ 					for (i = 0; i < PQnfields(result); i++)
+ 					{
+ 						if (!iter)
+ 						{
+ 							psql_error("too few target variables\n");
+ 							success = false;
+ 							break;
+ 						}
+ 
+ 						if (iter->name)
+ 						{
+ 							char	*value;
+ 
+ 							if (PQgetisnull(result, 0, i))
+ 								value = pset.popt.nullPrint ? pset.popt.nullPrint : "";
+ 							else
+ 								value = PQgetvalue(result, 0, i);
+ 
+ 							if (!SetVariable(pset.vars, iter->name, value))
+ 							{
+ 								psql_error("invalid variable name: \"%s\"\n",
+ 										   iter->name);
+ 								success = false;
+ 								break;
+ 							}
+ 						}
+ 
+ 						iter = iter->next;
+ 					}
+ 
+ 					if (success && iter != NULL)
+ 					{
+ 						psql_error("too many target variables\n");
+ 						success = false;
+ 					}
+ 				}
+ 			}
+ 			break;
+ 
+ 		case PGRES_COMMAND_OK:
+ 		case PGRES_EMPTY_QUERY:
+ 			psql_error("no data found\n");
+ 			success = false;
+ 			break;
+ 
+ 		case PGRES_COPY_OUT:
+ 		case PGRES_COPY_IN:
+ 			psql_error("COPY is not supported by \\gset command\n");
+ 			success = false;
+ 			break;
+ 
+ 		case PGRES_BAD_RESPONSE:
+ 		case PGRES_NONFATAL_ERROR:
+ 		case PGRES_FATAL_ERROR:
+ 			success = false;
+ 			psql_error("bad response\n");
+ 			break;
+ 
+ 		default:
+ 			success = false;
+ 			psql_error("unexpected PQresultStatus: %d\n",
+ 					   PQresultStatus(result));
+ 			break;
+ 	}
+ 
+ 	tglist_free(pset.gvars);
+ 	pset.gvars = NULL;
+ 
+ 	return success;
+ }
  
  /*
   * SendQuery: send the query string to the backend
***************
*** 943,949 **** SendQuery(const char *query)
  
  		/* but printing results isn't: */
  		if (OK && results)
! 			OK = PrintQueryResults(results);
  	}
  	else
  	{
--- 1047,1058 ----
  
  		/* but printing results isn't: */
  		if (OK && results)
! 		{
! 			if (pset.gvars)
! 				OK = StoreQueryResult(results);
! 			else
! 				OK = PrintQueryResults(results);
! 		}
  	}
  	else
  	{
***************
*** 1067,1072 **** ExecQueryUsingCursor(const char *query, double *elapsed_msec)
--- 1176,1182 ----
  	instr_time	before,
  				after;
  	int			flush_error;
+ 	bool		store_result = pset.gvars != NULL;
  
  	*elapsed_msec = 0;
  
***************
*** 1182,1188 **** ExecQueryUsingCursor(const char *query, double *elapsed_msec)
  			did_pager = true;
  		}
  
! 		printQuery(results, &my_popt, pset.queryFout, pset.logfile);
  
  		PQclear(results);
  
--- 1292,1322 ----
  			did_pager = true;
  		}
  
! 		if (pset.gvars)
! 		{
! 			/* initial iteration when tartget are variables */
! 			OK = StoreQueryResult(results);
! 			if (!OK)
! 			{
! 				flush_error = fflush(pset.queryFout);
! 				PQclear(results);
! 				break;
! 			}
! 		}
! 		else if (store_result)
! 		{
! 			/* must be second loop */
! 			if (ntuples > 0)
! 			{
! 				psql_error("too many rows\n");
! 				flush_error = fflush(pset.queryFout);
! 				PQclear(results);
! 				OK = false;
! 				break;
! 			}
! 		}
! 		else
! 			printQuery(results, &my_popt, pset.queryFout, pset.logfile);
  
  		PQclear(results);
  
***************
*** 1658,1660 **** expand_tilde(char **filename)
--- 1792,1844 ----
  
  	return *filename;
  }
+ 
+ 
+ /*
+  * Add name of internal variable to query target list
+  *
+  */
+ TargetList
+ tglist_add(TargetList tglist, const char *name)
+ {
+ 	TargetListData		*tgf;
+ 
+ 	tgf = pg_malloc(sizeof(TargetListData));
+ 	tgf->name = name ? pg_strdup(name) : NULL;
+ 	tgf->next = NULL;
+ 
+ 	if (tglist)
+ 	{
+ 		TargetListData *iter = (TargetListData *) tglist;
+ 
+ 		while (iter->next)
+ 			iter = iter->next;
+ 
+ 		iter->next = tgf;
+ 
+ 		return tglist;
+ 	}
+ 	else
+ 		return (TargetList) tgf;
+ }
+ 
+ /*
+  * Release target list
+  *
+  */
+ void
+ tglist_free(TargetList tglist)
+ {
+ 	TargetListData *iter = (TargetListData *) tglist;
+ 
+ 	while (iter)
+ 	{
+ 		TargetListData *next = iter->next;
+ 
+ 		if (iter->name)
+ 			free(iter->name);
+ 
+ 		free(iter);
+ 		iter = next;
+ 	}
+ }
*** a/src/bin/psql/common.h
--- b/src/bin/psql/common.h
***************
*** 21,26 ****
--- 21,34 ----
  
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
  
+ typedef struct _target_field
+ {
+ 	char	   *name;
+ 	struct _target_field *next;
+ } TargetListData;
+ 
+ typedef struct TargetListData *TargetList;
+ 
  /*
   * Safer versions of some standard C library functions. If an
   * out-of-memory condition occurs, these functions will bail out
***************
*** 62,65 **** extern const char *session_username(void);
--- 70,76 ----
  
  extern char *expand_tilde(char **filename);
  
+ extern TargetList  tglist_add(TargetList tglist, const char *name);
+ extern void tglist_free(TargetList tglist);
+ 
  #endif   /* COMMON_H */
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***************
*** 162,174 **** slashUsage(unsigned short int pager)
  {
  	FILE	   *output;
  
! 	output = PageOutput(94, pager);
  
  	/* if you add/remove a line here, change the row count above */
  
  	fprintf(output, _("General\n"));
  	fprintf(output, _("  \\copyright             show PostgreSQL usage and distribution terms\n"));
  	fprintf(output, _("  \\g [FILE] or ;         execute query (and send results to file or |pipe)\n"));
  	fprintf(output, _("  \\h [NAME]              help on syntax of SQL commands, * for all commands\n"));
  	fprintf(output, _("  \\q                     quit psql\n"));
  	fprintf(output, "\n");
--- 162,175 ----
  {
  	FILE	   *output;
  
! 	output = PageOutput(95, pager);
  
  	/* if you add/remove a line here, change the row count above */
  
  	fprintf(output, _("General\n"));
  	fprintf(output, _("  \\copyright             show PostgreSQL usage and distribution terms\n"));
  	fprintf(output, _("  \\g [FILE] or ;         execute query (and send results to file or |pipe)\n"));
+ 	fprintf(output, _("  \\gset NAME [, NAME [..]] execute query and store result in internal variables\n"));
  	fprintf(output, _("  \\h [NAME]              help on syntax of SQL commands, * for all commands\n"));
  	fprintf(output, _("  \\q                     quit psql\n"));
  	fprintf(output, "\n");
*** a/src/bin/psql/mainloop.c
--- b/src/bin/psql/mainloop.c
***************
*** 327,332 **** MainLoop(FILE *source)
--- 327,340 ----
  					/* flush any paren nesting info after forced send */
  					psql_scan_reset(scan_state);
  				}
+ 				else if (slashCmdStatus == PSQL_CMD_NOSEND)
+ 				{
+ 					resetPQExpBuffer(query_buf);
+ 					/* reset parsing state since we are rescanning whole line */
+ 					psql_scan_reset(scan_state);
+ 					line_saved_in_history = true;
+ 					success = false;
+ 				}
  				else if (slashCmdStatus == PSQL_CMD_NEWEDIT)
  				{
  					/* rescan query_buf as new input */
*** a/src/bin/psql/settings.h
--- b/src/bin/psql/settings.h
***************
*** 8,14 ****
  #ifndef SETTINGS_H
  #define SETTINGS_H
  
! 
  #include "variables.h"
  #include "print.h"
  
--- 8,14 ----
  #ifndef SETTINGS_H
  #define SETTINGS_H
  
! #include "common.h"
  #include "variables.h"
  #include "print.h"
  
***************
*** 73,78 **** typedef struct _psqlSettings
--- 73,79 ----
  	printQueryOpt popt;
  
  	char	   *gfname;			/* one-shot file output argument for \g */
+ 	TargetList  gvars;			/* one-shot target list argument for \gset */
  
  	bool		notty;			/* stdin or stdout is not a tty (as determined
  								 * on startup) */
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 856,862 **** psql_completion(char *text, int start, int end)
  		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
  		"\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du",
  		"\\e", "\\echo", "\\ef", "\\encoding",
! 		"\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
  		"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
  		"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
  		"\\set", "\\sf", "\\t", "\\T",
--- 856,862 ----
  		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
  		"\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du",
  		"\\e", "\\echo", "\\ef", "\\encoding",
! 		"\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l",
  		"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
  		"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
  		"\\set", "\\sf", "\\t", "\\T",
*** /dev/null
--- b/src/test/regress/expected/psql_cmd.out
***************
*** 0 ****
--- 1,68 ----
+ -- \gset
+ select 10, 20, 'Hello';
+  ?column? | ?column? | ?column? 
+ ----------+----------+----------
+        10 |       20 | Hello
+ (1 row)
+ 
+ \gset gset_test01, gset_test02, gset_test03
+ \echo :gset_test01 :gset_test02 :gset_test03
+ 10 20 Hello
+ select 10, 20, 'Hello World'
+ \gset gset_test01, gset_test02, gset_test03
+ \echo :gset_test01 :gset_test02 :gset_test03
+ 10 20 Hello World
+ -- errors
+ \gset ,,
+ \gset ,
+ too few target variables
+ \gset ,,,
+ too many target variables
+ \gset
+ \gset: missing required argument
+ -- using badly varnames - detected when variable is accessed
+ -- note: row comments are not supported by psql lexer when command option
+ -- is read - so these statements finish by syntax error - not expected
+ -- "missing required argument" error.
+ \set --varname 10
+ \set: error while setting variable
+ \gset --varname1, varname2, varname3
+ invalid variable name: "--varname1"
+ \gset gset_test04,,
+ \echo :gset_test04
+ 10
+ \gset ,,gset_test05
+ \echo :gset_test05
+ Hello World
+ \gset ,gset_test06 ,
+ \echo :gset_test06
+ 20
+ -- should to work with cursor too
+ \set FETCH_COUNT 1
+ select 'a', 'b', 'c'
+ \gset gset_test01, gset_test02, gset_test03
+ \echo :gset_test01 :gset_test02 :gset_test03
+ a b c
+ select 1,2 \gset x,y \\ \echo :x
+ 1
+ select 1,2 \gset x,y \echo :x \echo :y
+ 1
+ 2
+ select 1,2 \gset x,y \\ \g \echo :x :y
+  ?column? | ?column? 
+ ----------+----------
+         1 |        2
+ (1 row)
+ 
+ 1 2
+ select 1,2 \g \gset x,y \echo :x :y
+  ?column? | ?column? 
+ ----------+----------
+         1 |        2
+ (1 row)
+ 
+ 1 2
+ \pset null '(null)'
+ select NULL \gset var1
+ \echo :var1
+ (null)
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 109,111 **** test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c
--- 109,113 ----
  
  # run stats by itself because its delay may be insufficient under heavy load
  test: stats
+ 
+ test: psql_cmd
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 134,136 **** test: largeobject
--- 134,137 ----
  test: with
  test: xml
  test: stats
+ test: psql_cmd
*** /dev/null
--- b/src/test/regress/sql/psql_cmd.sql
***************
*** 0 ****
--- 1,57 ----
+ -- \gset
+ 
+ select 10, 20, 'Hello';
+ 
+ \gset gset_test01, gset_test02, gset_test03
+ 
+ \echo :gset_test01 :gset_test02 :gset_test03
+ 
+ select 10, 20, 'Hello World'
+ 
+ \gset gset_test01, gset_test02, gset_test03
+ 
+ \echo :gset_test01 :gset_test02 :gset_test03
+ 
+ -- errors
+ \gset ,,
+ \gset ,
+ \gset ,,,
+ \gset
+ 
+ -- using badly varnames - detected when variable is accessed
+ -- note: row comments are not supported by psql lexer when command option
+ -- is read - so these statements finish by syntax error - not expected
+ -- "missing required argument" error.
+ \set --varname 10
+ \gset --varname1, varname2, varname3
+ 
+ 
+ \gset gset_test04,,
+ \echo :gset_test04
+ 
+ \gset ,,gset_test05
+ \echo :gset_test05
+ 
+ \gset ,gset_test06 ,
+ \echo :gset_test06
+ 
+ -- should to work with cursor too
+ \set FETCH_COUNT 1
+ 
+ select 'a', 'b', 'c'
+ 
+ \gset gset_test01, gset_test02, gset_test03
+ 
+ \echo :gset_test01 :gset_test02 :gset_test03
+ 
+ select 1,2 \gset x,y \\ \echo :x
+ 
+ select 1,2 \gset x,y \echo :x \echo :y
+ 
+ select 1,2 \gset x,y \\ \g \echo :x :y
+ 
+ select 1,2 \g \gset x,y \echo :x :y
+ 
+ \pset null '(null)'
+ select NULL \gset var1
+ \echo :var1