On Sat, Jul 28, 2012 at 06:11:21PM +0200, Pavel Stehule wrote: > Hello > > 2012/7/27 Tom Lane <t...@sss.pgh.pa.us>: > > Pavel Stehule <pavel.steh...@gmail.com> writes: > >> 2012/7/26 David Fetter <da...@fetter.org>: > >>>>> How about > >>>>> \gset var1,,,var2,var3... > > > >>>> I don't like this - you can use fake variable - and ignoring some > >>>> variable has no big effect on client > > > >>> Why assign to a variable you'll never use? > > > >> so why you get data from server, when you would not to use it ? > > > > Yeah. I don't see why you'd be likely to write a select that computes > > columns you don't actually want. > > > >> Tom - your proposal release of stored dataset just before next > >> statement, not like now on the end of statement? > > > > Huh? I think you'd assign the values to the variables and then PQclear > > the result right away. > > yes - I didn't understand \g mechanism well. > > Here is patch - it is not nice at this moment and it is little bit > longer than I expected - but it works > > It supports David's syntax > > postgres=# select 'Hello', 'World' \gset a,b > postgres=# \echo :'a' :'b' > 'Hello' 'World' > postgres=# select 'Hello', 'World'; > ?column? │ ?column? > ──────────┼────────── > Hello │ World > (1 row) > > postgres=# \gset a > to few target variables > postgres=# \gset a, > postgres=# \echo :'a' > 'Hello' > > Regards > > Pavel
Teensy code cleanup (trailing space) and SGML docs added. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
*** a/doc/src/sgml/ref/psql-ref.sgml --- b/doc/src/sgml/ref/psql-ref.sgml *************** *** 1489,1495 **** 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> --- 1489,1495 ---- 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> *************** *** 1623,1628 **** Tue Oct 26 21:40:57 CEST 1999 --- 1623,1640 ---- </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 target list a corresponding list of psql + variables. + </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) + { + 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 *************** *** 826,831 **** PrintQueryResults(PGresult *results) --- 826,928 ---- 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 sucessfully, 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("to 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\"", + 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 isnot supported by \\gset command\n"); + success = false; + break; + + case PGRES_BAD_RESPONSE: + case PGRES_NONFATAL_ERROR: + case PGRES_FATAL_ERROR: + success = false; + 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 *************** *** 953,959 **** SendQuery(const char *query) /* but printing results isn't: */ if (OK && results) ! OK = PrintQueryResults(results); } else { --- 1050,1061 ---- /* but printing results isn't: */ if (OK && results) ! { ! if (pset.gvars) ! OK = StoreQueryResult(results); ! else ! OK = PrintQueryResults(results); ! } } else { *************** *** 1668,1670 **** expand_tilde(char **filename) --- 1770,1822 ---- return *filename; } + + + /* + * Add name of internal variable to query targer 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 *************** *** 63,66 **** extern const char *session_username(void); --- 71,77 ---- 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,123 ---- 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 ! #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 ! #define LEXRES_NONE 7 int yylex(void); *************** *** 167,172 **** static void escape_variable(bool as_ident); --- 172,178 ---- * <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); --- 189,195 ---- %x xdolq %x xui %x xus + %x xvl /* Additional exclusive states for psql only: lex backslash commands */ %x xslashcmd %x xslashargstart *************** *** 628,633 **** other . --- 635,660 ---- 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) --- 1476,1550 ---- } /* + * 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. + * + */ + TargetList + psql_scan_slash_vars(PsqlScanState state, + bool *error) + { + PQExpBufferData mybuf; + int lexresult = LEXRES_NONE; + int lexresult_prev; + bool is_lexer_error = false; + + + /* 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); + + gvars = NULL; + + do + { + lexresult_prev = lexresult; + + BEGIN(xvl); + lexresult = yylex(); + + /* read to end */ + if (is_lexer_error) + continue; + + if (lexresult == LEXRES_ERROR) + { + is_lexer_error = true; + continue; + } + + if (lexresult_prev == LEXRES_IDENT && lexresult == LEXRES_IDENT) + { + is_lexer_error = true; + continue; + } + + if (!is_lexer_error && (lexresult_prev == LEXRES_COMMA || lexresult_prev == LEXRES_NONE) + && lexresult == LEXRES_COMMA) + gvars = tglist_add(gvars, NULL); + + } while (lexresult != LEXRES_EOL); + + if (!is_lexer_error && lexresult_prev == LEXRES_COMMA) + gvars = tglist_add(gvars, NULL); + + *error = is_lexer_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 *************** *** 811,817 **** 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", --- 811,817 ---- "\\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",
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers