Hello Tom,
Here is a version 6.
A few thoughts about this patch:
* I think the ERROR_CODE variable should instead be named SQLSTATE.
That is what the SQL standard calls this string, and it's also what
just about all our documentation calls it; see PG_DIAG_SQLSTATE
in libpq, or the SQLSTATE 'xxxxx' construct in pl/pgsql, or the
sqlstate attribute of an exception object in plpython, etc etc.
ERROR_CODE -> SQLSTATE.
* I'm not exactly convinced that there's a use-case for STATUS
Removed, but I think it was nice to have, it is easier to interpret than
error codes and their classes that I have not memorized yet:-)
* It might be better if SQLSTATE and ERROR_MESSAGE were left
unchanged by a non-error query. That would reduce the need to
copy them into other variables just because you needed to do
something else before printing them. It'd save a few cycles too.
Added LAST_ERROR_SQLSTATE & MESSAGE, only reset when an error occured.
* Speaking of which, has anyone tried to quantify the performance
impact of this patch? It might well be negligible, but I do not
think we should assume that without evidence.
My guess is negligible. Not sure how to measure this negligible, as many
very fast query should be executed to have something significant. Maybe
100,000 "SELECT 1;" in a script?
* I wonder why you didn't merge this processing into ProcessResult,
instead of inventing an extra function (whose call site seems rather
poorly chosen anyhow --- what's the justification for not counting this
overhead as part of the query runtime?). You could probably eliminate
the refactoring you did, since it wouldn't be necessary to recompute
AcceptResult's result that way.
Variable setting moved at then end of ProcessResult, no new functions,
result is clean, so I should have done it like that in the beginning.
Forgotten help stuff added.
--
Fabien.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5bdbc1e..0bbe1c9 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3475,6 +3475,16 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>ERROR</varname></term>
+ <listitem>
+ <para>
+ Whether the last query failed, as a boolean.
+ See also <varname>SQLSTATE</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>FETCH_COUNT</varname></term>
<listitem>
<para>
@@ -3611,6 +3621,18 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>LAST_ERROR_SQLSTATE</varname></term>
+ <term><varname>LAST_ERROR_MESSAGE</varname></term>
+ <listitem>
+ <para>
+ The error code and associated error message of the last
+ error, or empty strings if no error occured since the
+ beginning of the script.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>
<varname>ON_ERROR_ROLLBACK</varname>
<indexterm>
@@ -3679,6 +3701,25 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>ROW_COUNT</varname></term>
+ <listitem>
+ <para>
+ How many rows were returned or affected by the last query.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>SQLSTATE</varname></term>
+ <listitem>
+ <para>
+ The error code associated to the last query, or
+ <literal>00000</> if no error occured.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>QUIET</varname></term>
<listitem>
<para>
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index b997058..93950fd 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -493,7 +493,6 @@ ResetCancelConn(void)
#endif
}
-
/*
* AcceptResult
*
@@ -1107,6 +1106,35 @@ ProcessResult(PGresult **results)
first_cycle = false;
}
+ /*
+ * Set special variables
+ * - ERROR: TRUE/FALSE, whether an error occurred
+ * - SQLSTATE: code of error, or "00000"
+ * - LAST_ERROR_SQLSTATE: same for last error
+ * - LAST_ERROR_MESSAGE: message of last error
+ * - ROW_COUNT: how many rows were returned or affected, or "0"
+ */
+ if (success)
+ {
+ char *ntuples = PQcmdTuples(*results);
+ SetVariable(pset.vars, "ERROR", "FALSE");
+ SetVariable(pset.vars, "SQLSTATE", "00000");
+ SetVariable(pset.vars, "ROW_COUNT", *ntuples ? ntuples : "0");
+ }
+ else
+ {
+ char *code = PQresultErrorField(*results, PG_DIAG_SQLSTATE);
+ char *mesg = PQresultErrorField(*results, PG_DIAG_MESSAGE_PRIMARY);
+
+ SetVariable(pset.vars, "ERROR", "TRUE");
+ /* if an error was detected, it must have a code! */
+ Assert(code != NULL);
+ SetVariable(pset.vars, "SQLSTATE", code);
+ SetVariable(pset.vars, "LAST_ERROR_SQLSTATE", code);
+ SetVariable(pset.vars, "LAST_ERROR_MESSAGE", mesg ? mesg : "");
+ SetVariable(pset.vars, "ROW_COUNT", "0");
+ }
+
/* may need this to recover from conn loss during COPY */
if (!first_cycle && !CheckConnection())
return false;
@@ -1214,7 +1242,6 @@ PrintQueryResults(PGresult *results)
return success;
}
-
/*
* SendQuery: send the query string to the backend
* (and print out results)
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 4d1c0ec..ae951f5 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -337,7 +337,7 @@ helpVariables(unsigned short int pager)
* Windows builds currently print one more line than non-Windows builds.
* Using the larger number is fine.
*/
- output = PageOutput(147, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(155, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("List of specially treated variables\n\n"));
@@ -360,6 +360,8 @@ helpVariables(unsigned short int pager)
" if set to \"noexec\", just show them without execution\n"));
fprintf(output, _(" ENCODING\n"
" current client character set encoding\n"));
+ fprintf(output, _(" ERROR\n"
+ " whether the last query failed\n"));
fprintf(output, _(" FETCH_COUNT\n"
" the number of result rows to fetch and display at a time (0 = unlimited)\n"));
fprintf(output, _(" HISTCONTROL\n"
@@ -374,6 +376,9 @@ helpVariables(unsigned short int pager)
" number of EOFs needed to terminate an interactive session\n"));
fprintf(output, _(" LASTOID\n"
" value of the last affected OID\n"));
+ fprintf(output, _(" LAST_ERROR_SQLSTATE\n"
+ " LAST_ERROR_MESSAGE\n"
+ " error code and message of last error, or \"00000\" and empty if none\n"));
fprintf(output, _(" ON_ERROR_ROLLBACK\n"
" if set, an error doesn't stop a transaction (uses implicit savepoints)\n"));
fprintf(output, _(" ON_ERROR_STOP\n"
@@ -388,6 +393,8 @@ helpVariables(unsigned short int pager)
" specifies the prompt used during COPY ... FROM STDIN\n"));
fprintf(output, _(" QUIET\n"
" run quietly (same as -q option)\n"));
+ fprintf(output, _(" ROW_COUNT\n"
+ " number of rows of last query, or 0\n"));
fprintf(output, _(" SERVER_VERSION_NAME\n"
" SERVER_VERSION_NUM\n"
" server's version (in short string or numeric format)\n"));
@@ -397,6 +404,8 @@ helpVariables(unsigned short int pager)
" if set, end of line terminates SQL commands (same as -S option)\n"));
fprintf(output, _(" SINGLESTEP\n"
" single-step mode (same as -s option)\n"));
+ fprintf(output, _(" SQLSTATE\n"
+ " error code of last query, or \"00000\" if no error\n"));
fprintf(output, _(" USER\n"
" the currently connected database user\n"));
fprintf(output, _(" VERBOSITY\n"
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 1e48f4a..d020f3f 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -165,6 +165,10 @@ main(int argc, char *argv[])
SetVariable(pset.vars, "VERSION_NAME", PG_VERSION);
SetVariable(pset.vars, "VERSION_NUM", CppAsString2(PG_VERSION_NUM));
+ /* Create variables for last error */
+ SetVariable(pset.vars, "LAST_ERROR_SQLSTATE", "00000");
+ SetVariable(pset.vars, "LAST_ERROR_MESSAGE", "");
+
/* Default values for variables (that don't match the result of \unset) */
SetVariableBool(pset.vars, "AUTOCOMMIT");
SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 7957268..397a1de 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -28,6 +28,74 @@ on
\unset ON_ERROR_ROLLBACK
\echo :ON_ERROR_ROLLBACK
off
+-- special result variables
+-- these test are performed early to check that for values after startup
+-- 2 rows select
+SELECT 1 AS stuff UNION SELECT 2;
+ stuff
+-------
+ 1
+ 2
+(2 rows)
+
+\if :ERROR
+ \echo 'MUST NOT SHOW'
+\else
+ \echo 'ERROR is FALSE as expected'
+ERROR is FALSE as expected
+\endif
+\echo 'error code:' :SQLSTATE
+error code: 00000
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 00000
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message:
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 2
+-- syntax error
+SELECT 1 UNION;
+ERROR: syntax error at or near ";"
+LINE 1: SELECT 1 UNION;
+ ^
+\if :ERROR
+ \echo 'ERROR is TRUE as expected'
+ERROR is TRUE as expected
+\else
+ \echo 'MUST NOT SHOW'
+\endif
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42601
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: syntax error at or near ";"
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- empty query
+;
+\echo 'error:' :ERROR
+error: FALSE
+\echo 'error code:' :SQLSTATE
+error code: 00000
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42601
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: syntax error at or near ";"
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- other query error
+DROP TABLE this_table_does_not_exist;
+ERROR: table "this_table_does_not_exist" does not exist
+\echo 'error:' :ERROR
+error: TRUE
+\echo 'error code:' :SQLSTATE
+error code: 42P01
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42P01
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: table "this_table_does_not_exist" does not exist
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
-- \g and \gx
SELECT 1 as one, 2 as two \g
one | two
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 0556b7c..3713098 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -21,6 +21,49 @@
\unset ON_ERROR_ROLLBACK
\echo :ON_ERROR_ROLLBACK
+-- special result variables
+-- these test are performed early to check that for values after startup
+
+-- 2 rows select
+SELECT 1 AS stuff UNION SELECT 2;
+\if :ERROR
+ \echo 'MUST NOT SHOW'
+\else
+ \echo 'ERROR is FALSE as expected'
+\endif
+\echo 'error code:' :SQLSTATE
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- syntax error
+SELECT 1 UNION;
+\if :ERROR
+ \echo 'ERROR is TRUE as expected'
+\else
+ \echo 'MUST NOT SHOW'
+\endif
+\echo 'error code:' :SQLSTATE
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- empty query
+;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- other query error
+DROP TABLE this_table_does_not_exist;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
-- \g and \gx
SELECT 1 as one, 2 as two \g
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
