Hello Tom,
Yep, I thought I was optimistic:-) Can I add a special SQLSTATE for that
situation where libpq did not report an error?
Meh. If we're going to do that I think it might be better to hack
libpq itself to do so, ie, force PQresultErrorField(..., PG_DIAG_SQLSTATE)
to always return something. But it seems like a hack either way.
I would not have took the liberty to hack into libpq internals for such a
small front-end feature. However I agree that having libpq always return some
diagnostic, even if it means "something unclear happened, sorry not to be
very precise", would be better.
Here is an attempt at implementing your suggestions.
I added two error codes, which is debatable. One is used hardcoded by
libpq if no diagnostic is found, and the other by psql if libpq returned
something empty, which might happen if psql is linked with an older libpq,
maybe. I do not know how to trigger such errors anyway, so this is rather
academic.
I put back SetResultVariables function which is called twice, for SQL
queries and the new descriptions. It worked out of the box with DECLARE
which is just another SQL statement, so maybe I did not understood the
cursor issue you were signaling...
--
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..bbffcac 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -493,7 +493,6 @@ ResetCancelConn(void)
#endif
}
-
/*
* AcceptResult
*
@@ -971,6 +970,44 @@ loop_exit:
return success;
}
+/*
+ * 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"
+ */
+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");
+
+ /*
+ * Ensure that something sensible is shown,
+ * without assumption about libpq implementation
+ */
+ if (code == NULL || *code == '\0')
+ code = "PQ001" /* ERROR_LIBPQ_EMPTY_SQLSTATE */ ;
+
+ 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 +1144,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 +1253,6 @@ PrintQueryResults(PGresult *results)
return success;
}
-
/*
* SendQuery: send the query string to the backend
* (and print out results)
@@ -1523,7 +1561,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/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index c24bce6..678aa02 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -2717,6 +2717,9 @@ PQresultErrorField(const PGresult *res, int fieldcode)
if (pfield->code == fieldcode)
return pfield->contents;
}
+ /* special case if sqlstate was found */
+ if (fieldcode == PG_DIAG_SQLSTATE)
+ return "PQ000" /* aka ERROR_LIBPQ_MISSING_SQLSTATE */;
return NULL;
}
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
