Here is a version 6.
Small v7 update, sorry for the noise.
Add testing the initial state of all variables.
Fix typos in a comment in tests.
Fix the documentation wrt the current implementation behavior.
--
Fabien.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5bdbc1e..97962d4 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 "00000" and 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..b9cdc44 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -28,6 +28,86 @@ on
\unset ON_ERROR_ROLLBACK
\echo :ON_ERROR_ROLLBACK
off
+-- special result variables
+-- these tests are performed early to check that for values after startup
+-- 3 unset variables
+\echo 'error:' :ERROR
+error: :ERROR
+\echo 'error code:' :SQLSTATE
+error code: :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+number of rows: :ROW_COUNT
+-- variables with default values
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 00000
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message:
+-- first script query, 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..3a55d71 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -21,6 +21,58 @@
\unset ON_ERROR_ROLLBACK
\echo :ON_ERROR_ROLLBACK
+-- special result variables
+-- these tests are performed early to check that for values after startup
+
+-- 3 unset variables
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+
+-- variables with default values
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+
+-- first script query, 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
