Well, if we provided a different SQLSTATE for each qualitatively
different type of libpq error, that might well be useful enough to
justify some risk of application breakage. But replacing a constant
string that we've had for ~15 years with a different constraint string
isn't doing anything about the lack-of-information problem you're
complaining about.
True. Well, the original point here was whether psql ought to be doing
something to mask libpq's (mis) behavior. I'm inclined to think not:
if it doesn't get a SQLSTATE from the PGresult, it should just set the
sqlstate variables to empty strings.
See v9 attached.
--
Fabien.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index a74caf8..b994fcd 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3518,6 +3518,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>
@@ -3654,6 +3664,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>
@@ -3722,6 +3744,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..cc7e3aa 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -493,7 +493,6 @@ ResetCancelConn(void)
#endif
}
-
/*
* AcceptResult
*
@@ -971,6 +970,45 @@ loop_exit:
return success;
}
+/*
+ * Set special variables
+ * - ERROR: true/false, whether an error occurred
+ * - SQLSTATE: code of error, or "00000", or ""
+ * - 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"
+ */
+static void
+SetResultVariables(PGresult *results, bool success)
+{
+ 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 there is no code, use an empty string?
+ * libpq may return such thing on internal errors
+ * (lost connection, EOM).
+ */
+ if (code == NULL)
+ code = "" ;
+
+ 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");
+ }
+}
/*
* ProcessResult: utility function for use by SendQuery() only
@@ -1107,6 +1145,8 @@ ProcessResult(PGresult **results)
first_cycle = false;
}
+ SetResultVariables(*results, success);
+
/* may need this to recover from conn loss during COPY */
if (!first_cycle && !CheckConnection())
return false;
@@ -1214,7 +1254,6 @@ PrintQueryResults(PGresult *results)
return success;
}
-
/*
* SendQuery: send the query string to the backend
* (and print out results)
@@ -1523,7 +1562,11 @@ DescribeQuery(const char *query, double *elapsed_msec)
* good thing because libpq provides no easy way to do that.)
*/
results = PQprepare(pset.db, "", query, 0, NULL);
- if (PQresultStatus(results) != PGRES_COMMAND_OK)
+ OK = PQresultStatus(results) == PGRES_COMMAND_OK;
+
+ SetResultVariables(results, OK);
+
+ if (!OK)
{
psql_error("%s", PQerrorMessage(pset.db));
ClearOrSaveResult(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 bda8960..51a98bf 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -28,6 +28,197 @@ 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 initially 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 working 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 'number of rows:' :ROW_COUNT
+number of rows: 2
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 00000
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+last error message:
+-- 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 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- must have kept previous values
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42601
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: syntax error at or near ";"
+-- 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 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- new values
+\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
+-- cleanup
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
+-- working CURSOR
+DECLARE one CURSOR WITH HOLD FOR SELECT 1 AS one;
+\echo 'error:' :ERROR
+error: false
+\echo 'error code:' :SQLSTATE
+error code: 00000
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+FETCH 2 FROM one;
+ one
+-----
+ 1
+(1 row)
+
+\echo 'error:' :ERROR
+error: false
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 1
+FETCH NEXT FROM one;
+ one
+-----
+(0 rows)
+
+\echo 'error:' :ERROR
+error: false
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+CLOSE one;
+\echo 'error:' :ERROR
+error: false
+-- CURSOR with syntax error
+DECLARE two CURSOR WITH HOLD FOR SELECT 1 + 1 +;
+ERROR: syntax error at or near ";"
+LINE 1: DECLARE two CURSOR WITH HOLD FOR SELECT 1 + 1 +;
+ ^
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42601
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: syntax error at or near ";"
+-- cleanup
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
+-- CURSOR with execution error
+BEGIN;
+CREATE FUNCTION raise_an_error()
+RETURNS INTEGER
+IMMUTABLE STRICT AS $$
+BEGIN
+ RAISE EXCEPTION 'function raise_an_error()';
+ RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+DECLARE cursor_error CURSOR FOR SELECT raise_an_error();
+ERROR: function raise_an_error()
+CONTEXT: PL/pgSQL function raise_an_error() line 3 at RAISE
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: P0001
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: P0001
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: function raise_an_error()
+ROLLBACK;
+-- working description
+SELECT 3 AS three \gdesc
+ Column | Type
+--------+---------
+ three | integer
+(1 row)
+
+\echo 'error:' :ERROR
+error: false
+\echo 'error code:' :SQLSTATE
+error code: 00000
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- describe with an error
+SELECT 4 AS \gdesc
+ERROR: syntax error at end of input
+LINE 1: SELECT 4 AS
+ ^
+\echo 'error:' :ERROR
+error: true
+\echo 'error code:' :SQLSTATE
+error code: 42601
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+last error code: 42601
+\echo 'error message:' :LAST_ERROR_MESSAGE
+error message: syntax error at end of input
+-- cleanup all
+\unset ERROR
+\unset SQLSTATE
+\unset ROW_COUNT
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
-- \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..c3a8844 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -21,6 +21,130 @@
\unset ON_ERROR_ROLLBACK
\echo :ON_ERROR_ROLLBACK
+-- special result variables
+-- these tests are performed early to check that for values after startup
+
+-- 3 initially 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 working 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 'number of rows:' :ROW_COUNT
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'last error message:' :LAST_ERROR_MESSAGE
+
+-- 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 'number of rows:' :ROW_COUNT
+-- must have kept previous values
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+
+-- other query error
+DROP TABLE this_table_does_not_exist;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+-- new values
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+-- cleanup
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
+
+-- working CURSOR
+DECLARE one CURSOR WITH HOLD FOR SELECT 1 AS one;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+FETCH 2 FROM one;
+\echo 'error:' :ERROR
+\echo 'number of rows:' :ROW_COUNT
+FETCH NEXT FROM one;
+\echo 'error:' :ERROR
+\echo 'number of rows:' :ROW_COUNT
+CLOSE one;
+\echo 'error:' :ERROR
+
+-- CURSOR with syntax error
+DECLARE two CURSOR WITH HOLD FOR SELECT 1 + 1 +;
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+-- cleanup
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
+
+-- CURSOR with execution error
+BEGIN;
+
+CREATE FUNCTION raise_an_error()
+RETURNS INTEGER
+IMMUTABLE STRICT AS $$
+BEGIN
+ RAISE EXCEPTION 'function raise_an_error()';
+ RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+DECLARE cursor_error CURSOR FOR SELECT raise_an_error();
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+
+ROLLBACK;
+
+-- working description
+SELECT 3 AS three \gdesc
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+
+-- describe with an error
+SELECT 4 AS \gdesc
+\echo 'error:' :ERROR
+\echo 'error code:' :SQLSTATE
+\echo 'number of rows:' :ROW_COUNT
+\echo 'last error code:' :LAST_ERROR_SQLSTATE
+\echo 'error message:' :LAST_ERROR_MESSAGE
+
+-- cleanup all
+\unset ERROR
+\unset SQLSTATE
+\unset ROW_COUNT
+\unset LAST_ERROR_SQLSTATE
+\unset LAST_ERROR_MESSAGE
+
-- \g and \gx
SELECT 1 as one, 2 as two \g
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
