Hi Pavel,
On Sat, Oct 13, 2012 at 12:58 AM, Pavel Stehule
<pavel.steh...@gmail.com> wrote:
* merge Shigeru's doc patch
* rename psql regression test from "psql" to "psql_cmd"
Those changes seem good.
Besides, I found an error message which doesn't end with '¥n' in
common.c. In general, messages passed to psql_error end with '¥n',
unless additional information follows. Please see attached patch for
additional change.
After you determine whether it's ok or unnecessary, I'll mark this patch
as "Ready for committer".
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
***************
*** 748,753 **** exec_command(const char *cmd,
--- 748,776 ----
status = PSQL_CMD_SEND;
}
+ /* \gset send query and store result */
+ else if (strcmp(cmd, "gset") == 0)
+ {
+ bool error;
+
+ pset.gvars = psql_scan_slash_vars(scan_state, &error);
+
+ if (!pset.gvars && !error)
+ {
+ psql_error("\\%s: missing required argument\n", cmd);
+ status = PSQL_CMD_NOSEND;
+ }
+ else if (error)
+ {
+ psql_error("\\%s: syntax error\n", cmd);
+ status = PSQL_CMD_NOSEND;
+ tglist_free(pset.gvars);
+ pset.gvars = NULL;
+ }
+ else
+ status = PSQL_CMD_SEND;
+ }
+
/* 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
***************
*** 8,13 ****
--- 8,14 ----
#ifndef PSQLSCAN_H
#define PSQLSCAN_H
+ #include "common.h"
#include "pqexpbuffer.h"
#include "prompt.h"
***************
*** 33,39 **** enum slash_option_type
OT_SQLIDHACK, /* SQL identifier, but don't
downcase */
OT_FILEPIPE, /* it's a filename or pipe */
OT_WHOLE_LINE, /* just snarf the rest of the
line */
! OT_NO_EVAL /* no expansion of
backticks or variables */
};
--- 34,41 ----
OT_SQLIDHACK, /* SQL identifier, but don't
downcase */
OT_FILEPIPE, /* it's a filename or pipe */
OT_WHOLE_LINE, /* just snarf the rest of the
line */
! OT_NO_EVAL, /* no expansion of
backticks or variables */
! OT_VARLIST /* returns variable's
identifier or comma */
};
***************
*** 59,64 **** extern char *psql_scan_slash_option(PsqlScanState state,
--- 61,70 ----
char *quote,
bool semicolon);
+ extern TargetList psql_scan_slash_vars(PsqlScanState state,
+ bool *error);
+
+
extern void psql_scan_slash_command_end(PsqlScanState state);
#endif /* PSQLSCAN_H */
*** a/src/bin/psql/psqlscan.l
--- b/src/bin/psql/psqlscan.l
***************
*** 106,118 **** static char *option_quote;
static int unquoted_option_chars;
static int backtick_start_offset;
/* Return values from yylex() */
#define LEXRES_EOL 0 /* end of input */
#define LEXRES_SEMI 1 /* command-terminating
semicolon found */
! #define LEXRES_BACKSLASH 2 /* backslash command start */
! #define LEXRES_OK 3 /* OK completion of backslash
argument */
!
int yylex(void);
--- 106,122 ----
static int unquoted_option_chars;
static int backtick_start_offset;
+ TargetList gvars;
+
/* Return values from yylex() */
#define LEXRES_EOL 0 /* end of input */
#define LEXRES_SEMI 1 /* command-terminating
semicolon found */
! #define LEXRES_COMMA 2 /* comma as identifiers list
separator */
! #define LEXRES_BACKSLASH 3 /* backslash command start */
! #define LEXRES_IDENT 4 /* valid internal variable
identifier */
! #define LEXRES_OK 5 /* OK completion of backslash
argument */
! #define LEXRES_ERROR 6 /* unexpected char in
identifiers list */
int yylex(void);
***************
*** 167,172 **** static void escape_variable(bool as_ident);
--- 171,177 ----
* <xdolq> $foo$ quoted strings
* <xui> quoted identifier with Unicode escapes
* <xus> quoted string with Unicode escapes
+ * <xvl> comma separated list of variables
*
* 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);
--- 188,194 ----
%x xdolq
%x xui
%x xus
+ %x xvl
/* Additional exclusive states for psql only: lex backslash commands */
%x xslashcmd
%x xslashargstart
***************
*** 628,633 **** other .
--- 634,659 ----
ECHO;
}
+ <xvl>{
+
+ {identifier} {
+ gvars = tglist_add(gvars, yytext);
+ return LEXRES_IDENT;
+ }
+
+ "," {
+ return LEXRES_COMMA;
+ }
+
+ {horiz_space}+ { }
+
+ . |
+ \n {
+ return LEXRES_ERROR;
+ }
+
+ }
+
{xufailed} {
/* throw back all but the initial u/U */
yyless(1);
***************
*** 1449,1454 **** psql_scan_slash_command(PsqlScanState state)
--- 1475,1571 ----
}
/*
+ * returns identifier from identifier list
+ *
+ * when comma_expected is true, when we require comma before identifier
+ * error is true, when unexpected char was identified or missing
+ * comma.
+ *
+ */
+ typedef enum
+ {
+ VARLIST_PARSER_INITIAL,
+ VARLIST_PARSER_EXPECTED_COMMA,
+ VARLIST_PARSER_EXPECTED_COMMA_OR_IDENT,
+ VARLIST_PARSER_ERROR
+ } VarListParserState;
+
+ TargetList
+ psql_scan_slash_vars(PsqlScanState state,
+ bool *error)
+ {
+ PQExpBufferData mybuf;
+ int lexresult;
+ VarListParserState vlpState; /* parser state
*/
+
+ gvars = NULL;
+ vlpState = VARLIST_PARSER_INITIAL;
+
+ /* Must be scanning already */
+ psql_assert(state->scanbufhandle);
+
+ /* 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();
+
+ while (lexresult != LEXRES_EOL)
+ {
+ /* read to EOL, skip processing when some error occurs */
+ if (vlpState != VARLIST_PARSER_ERROR)
+ {
+ switch (lexresult)
+ {
+ case LEXRES_ERROR:
+ vlpState = VARLIST_PARSER_ERROR;
+ break;
+
+ case LEXRES_COMMA:
+ /* insert /dev/null targets */
+ if (vlpState == VARLIST_PARSER_INITIAL
||
+ vlpState ==
VARLIST_PARSER_EXPECTED_COMMA_OR_IDENT)
+ gvars = tglist_add(gvars, NULL);
+ vlpState =
VARLIST_PARSER_EXPECTED_COMMA_OR_IDENT;
+ break;
+
+ case LEXRES_IDENT:
+ /* don't allow var after var without
comma */
+ if (vlpState == VARLIST_PARSER_INITIAL
||
+ vlpState ==
VARLIST_PARSER_EXPECTED_COMMA_OR_IDENT)
+ vlpState =
VARLIST_PARSER_EXPECTED_COMMA;
+ else
+ vlpState = VARLIST_PARSER_ERROR;
+ break;
+
+ default:
+ /* can't get here */
+ fprintf(stderr, "invalid yylex
result\n");
+ exit(1);
+ }
+ }
+
+ /* read next token */
+ BEGIN(xvl);
+ lexresult = yylex();
+ }
+
+ /* append /dev/null target when last token was comma */
+ if (vlpState == VARLIST_PARSER_EXPECTED_COMMA_OR_IDENT)
+ gvars = tglist_add(gvars, NULL);
+
+ *error = vlpState == VARLIST_PARSER_ERROR;
+
+ return gvars;
+ }
+
+ /*
* Parse off the next argument for a backslash command, and return it as a
* malloc'd string. If there are no more arguments, returns NULL.
*
*** 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,39 ----
+ -- \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: syntax error
+ \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
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 105,107 **** test: plancache limit plpgsql copy2 temp domain rangefuncs
prepare without_oid c
--- 105,109 ----
# 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,36 ----
+ -- \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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
