Hi Pavel, On Tue, Oct 16, 2012 at 6:59 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > here is updated patch, I moved lot of code from lexer to command.com, > and now more \gset doesn't disable other backslash commands on same > line.
* lexer changes IIUC, new function psql_scan_varlist_varname scans input and returns a variable name or a comma at each call, and command.c handles the error such as invalid # of variables. This new design seems better than old one. However, IMHO the name psql_scan_varlist_varname sounds redundant and unintuitive. I'd prefer psql_scan_slash_varlist, because it indicates that that function is expected to be used for arguments of backslash commands, like psql_scan_slash_command and psql_scan_slash_option. Thoughts? * multiple meta command Now both of the command sequences $ SELECT 1, 2 \gset var1, var2 \g foo.txt $ SELECT 1, 2 \g foo.txt \gset var1, var2 set var1 and v2 to "1" and "2" respectively, and also write the result into foo.txt. This would be what users expected. * Duplication of variables I found an issue we have not discussed. Currently \gset accepts same variable names in the list, and stores last SELECT item in duplicated variables. For instance, $ SELECT 1, 2 \gset var, var stores "2" into var. I think this behavior is acceptable, but it might be worth mentioning in document. * extra fixes I fixed some minor issues below. Please see attached v10 patch for details. * remove unused macro OT_VARLIST * remove unnecessary #include directive for common.h * fill comment within 80 columns * indent short variable name with tab * add regression test case for combination of \g and \gset * bug on FETCH_COUNT = 1 When FETCH_COUNT is set to 1, and the number of rows returned is 1 too, \gset shows extra "(1 row)". This would be a bug in ExecQueryUsingCursor. Please see the last test case in regression test psql_cmd. I'll mark this patch as "waiting author". Regards, -- Shigeru HANADA
*** 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,1644 ---- </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=> SELECT 'hello', 'wonderful', 'world!' \gset var1,,var3 + foo=> \echo :var1 :var3 + hello world! + </programlisting> + </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,831 ---- status = PSQL_CMD_SEND; } + /* \gset send query and store result */ + else if (strcmp(cmd, "gset") == 0) + { + bool value_is_valid; + char *value; + VarlistParserState state = VARLIST_INITIAL; + + /* expected valid target list */ + success = true; + + pset.gvars = NULL; + while ((value = psql_scan_varlist_varname(scan_state, &value_is_valid))) + { + if (value_is_valid && success) + { + if (strcmp(value, ",") == 0) + { + if (state == VARLIST_INITIAL || + state == VARLIST_EXPECTED_COMMA_OR_IDENT) + pset.gvars = tglist_add(pset.gvars, NULL); + state = VARLIST_EXPECTED_COMMA_OR_IDENT; + } + else + { + if (state == VARLIST_INITIAL || + state == VARLIST_EXPECTED_COMMA_OR_IDENT) + { + pset.gvars = tglist_add(pset.gvars, value); + state = VARLIST_EXPECTED_COMMA; + } + else + success = false; + } + } + else + { + success = false; + /* but continue, we would to read to eol */ + } + + free(value); + } + + /* final check and target list completation */ + if (pset.gvars != NULL) + { + if (success) + { + if (state == VARLIST_EXPECTED_COMMA_OR_IDENT && success) + 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,919 ---- 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) + { + if (!SetVariable(pset.vars, iter->name, + PQgetvalue(result, 0, i))) + { + 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 { --- 1041,1052 ---- /* 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) --- 1170,1177 ---- instr_time before, after; int flush_error; + bool initial_loop; + bool store_result = pset.gvars != NULL; *elapsed_msec = 0; *************** *** 1133,1138 **** ExecQueryUsingCursor(const char *query, double *elapsed_msec) --- 1238,1246 ---- /* clear any pre-existing error indication on the output stream */ clearerr(pset.queryFout); + /* we would to allow store result to variables only once */ + initial_loop = true; + for (;;) { if (pset.timing) *************** *** 1182,1188 **** ExecQueryUsingCursor(const char *query, double *elapsed_msec) did_pager = true; } ! printQuery(results, &my_popt, pset.queryFout, pset.logfile); PQclear(results); --- 1290,1312 ---- did_pager = true; } ! if (pset.gvars) ! { ! OK = StoreQueryResult(results); ! if (!OK) ! { ! flush_error = fflush(pset.queryFout); ! break; ! } ! } ! else if (store_result && !initial_loop && ntuples > 0) ! { ! psql_error("too many rows\n"); ! flush_error = fflush(pset.queryFout); ! break; ! } ! else ! printQuery(results, &my_popt, pset.queryFout, pset.logfile); PQclear(results); *************** *** 1208,1213 **** ExecQueryUsingCursor(const char *query, double *elapsed_msec) --- 1332,1339 ---- if (ntuples < pset.fetch_count || cancel_pressed || flush_error || ferror(pset.queryFout)) break; + + initial_loop = false; } /* close \g argument file/pipe, restore old setting */ *************** *** 1658,1660 **** expand_tilde(char **filename) --- 1784,1836 ---- 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/psqlscan.h --- b/src/bin/psql/psqlscan.h *************** *** 59,64 **** extern char *psql_scan_slash_option(PsqlScanState state, --- 59,67 ---- char *quote, bool semicolon); + extern char *psql_scan_varlist_varname(PsqlScanState state, bool *is_ok); + + extern void psql_scan_slash_command_end(PsqlScanState state); #endif /* PSQLSCAN_H */ *** a/src/bin/psql/psqlscan.l --- b/src/bin/psql/psqlscan.l *************** *** 167,172 **** static void escape_variable(bool as_ident); --- 167,173 ---- * <xdolq> $foo$ quoted strings * <xui> quoted identifier with Unicode escapes * <xus> quoted string with Unicode escapes + * <xvl> comma separated list of varnames * * Note: we intentionally don't mimic the backend's <xeu> state; we have * no need to distinguish it from <xe> state, and no good way to get out *************** *** 183,188 **** static void escape_variable(bool as_ident); --- 184,190 ---- %x xdolq %x xui %x xus + %x xvl /* Additional exclusive states for psql only: lex backslash commands */ %x xslashcmd %x xslashargstart *************** *** 628,633 **** other . --- 630,667 ---- ECHO; } + <xvl>{ + + {identifier} { + ECHO; + return LEXRES_OK; + } + + "," { + ECHO; + return LEXRES_OK; + } + + "\\\\" { + return LEXRES_EOL; + } + + "\\" { + yyless(0); + return LEXRES_EOL; + } + + {horiz_space}+ { } + + . | + \n { + ECHO; + return LEXRES_EOL; + } + + } + + {xufailed} { /* throw back all but the initial u/U */ yyless(1); *************** *** 1937,1939 **** escape_variable(bool as_ident) --- 1971,2022 ---- */ emit(yytext, yyleng); } + + /* + * psql_scan_varlist - scan variable list for \gset command + * + * Returns next variable name or comma from variable list. When returned + * string is not empty and out parameter is_ok is false, then this string + * contains unexpected character. Returns NULL, when EOL is detected. + */ + char * + psql_scan_varlist_varname(PsqlScanState state, bool *is_ok) + { + PQExpBufferData mybuf; + int lexresult; + + /* Must be scanning already */ + psql_assert(state->scanbufhandle); + + /* Build a local buffer */ + initPQExpBuffer(&mybuf); + + /* Set up static variables that will be used by yylex */ + cur_state = state; + output_buf = &mybuf; + + if (state->buffer_stack != NULL) + yy_switch_to_buffer(state->buffer_stack->buf); + else + yy_switch_to_buffer(state->scanbufhandle); + + BEGIN(xvl); + lexresult = yylex(); + + if (lexresult == LEXRES_OK) + { + *is_ok = true; + } + else if (lexresult == LEXRES_EOL && mybuf.len == 0) + { + *is_ok = true; + termPQExpBuffer(&mybuf); + return NULL; + } + else + { + *is_ok = false; + } + + return mybuf.data; + } *** a/src/bin/psql/settings.h --- b/src/bin/psql/settings.h *************** *** 9,14 **** --- 9,15 ---- #define SETTINGS_H + #include "common.h" #include "variables.h" #include "print.h" *************** *** 73,78 **** typedef struct _psqlSettings --- 74,80 ---- 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,64 ---- + -- \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 + \gset ,, -- error + \gset: syntax error + \gset , -- error + \gset: syntax error + \gset ,,, -- error + \gset: syntax error + \gset -- error + \gset: missing required argument + \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 + (1 row) + + \echo :gset_test01 :gset_test02 :gset_test03 + a b c + select 1,2 \gset x,y \\ \echo :x + (1 row) + + 1 + select 1,2 \gset x,y \echo :x \echo :y + (1 row) + + 1 + 2 + select 1,2 \gset x,y \\ \g \echo :x :y + (1 row) + + ?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 *** 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,45 ---- + -- \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 + + \gset ,, -- error + \gset , -- error + \gset ,,, -- error + \gset -- error + + \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 +
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers