On Sat, Jul 28, 2012 at 06:11:21PM +0200, Pavel Stehule wrote:
> Hello
>
> 2012/7/27 Tom Lane <[email protected]>:
> > Pavel Stehule <[email protected]> writes:
> >> 2012/7/26 David Fetter <[email protected]>:
> >>>>> 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 <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
