Hello Pavel,
After some discussions about what could be useful since psql scripts now
accepts tests, this patch sets a few variables which can be used by psql
after a "front door" (i.e. actually typed by the user) query:
- RESULT_STATUS: the status of the query
- ERROR: whether the query failed
- ERROR_MESSAGE: ...
- ROW_COUNT: #rows affected
SELECT * FROM ;
\if :ERROR
\echo oops
\q
\endif
I'm not sure that the names are right. Maybe STATUS would be better than
RESULT_STATUS.
I am sending review of this patch:
1. I agree so STATUS is better name, than RESULT status.
Ok, looks simpler.
Currently it returns values with prefix PGRES (like PGRES_FATAL_ERROR,
PGRES_TUPLES_OK). Maybe we should to cut this prefix. FATAL_ERROR,
TUPLES_OK looks better for custom level. The PGRES prefix has not sense
in psql.
Indeed. I skipped "PGRES_".
2. I propose availability to read ERROR_CODE - sometimes it can be more
practical than parsing error possible translated message
Ok.
3. The fields ERROR_MESSAGE and ROW_COUNT are set only when it has sense.
This behave is maybe too strict for psql and the processing needs more
nesting \if command. What do you think about -1 or 0 for ROW_COUNT (for
DDL) and "" for ERROR_MESSAGE when there are not any error? It will be
consistent with already implemented LASTOID variable (and other state psql
variables). Using default values are not strict clean, but it can reduce
complexity of psql scripts.
My intention was that it could be tested with the "is defined" syntax,
which is yet to be agreed upon and implemented, so maybe generating empty
string is a better option.
For ROW_COUNT, I think that it should be consistent with what PL/pgSQL
does, so it think that 0 should be the default.
4. all regress tests passed
5. there are not any problem with doc building
Please find attached a v2 which hopefully takes into account all your
points above.
Open question: should it gather more PQerrorResultField, or the two
selected one are enough? If more, which should be included?
--
Fabien.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 3b86612..d33da32 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3449,6 +3449,35 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>ERROR</varname></term>
+ <listitem>
+ <para>
+ Whether the last query failed, as a boolean.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>ERROR_CODE</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>ERROR_MESSAGE</varname></term>
+ <listitem>
+ <para>
+ If the last query failed, the associated error message,
+ otherwise an empty string.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>FETCH_COUNT</varname></term>
<listitem>
<para>
@@ -3653,6 +3682,24 @@ bar
</varlistentry>
<varlistentry>
+ <term><varname>STATUS</varname></term>
+ <listitem>
+ <para>
+ Text status of the last query.
+ </para>
+ </listitem>
+ </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>QUIET</varname></term>
<listitem>
<para>
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a2f1259..4a3c6a9 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -1213,6 +1213,62 @@ PrintQueryResults(PGresult *results)
return success;
}
+/*
+ * Set special variables for "front door" queries
+ * - STATUS: last query status
+ * - ERROR: TRUE/FALSE, whether an error occurred
+ * - ERROR_CODE: code if an error occured, or "00000"
+ * - ERROR_MESSAGE: message if an error occured, or ""
+ * - ROW_COUNT: how many rows were returned or affected, or "0"
+ */
+static void
+SetResultVariables(PGresult *results)
+{
+ bool success;
+ ExecStatusType restat = PQresultStatus(results);
+ char *code = PQresultErrorField(results, PG_DIAG_SQLSTATE);
+ char *mesg = PQresultErrorField(results, PG_DIAG_MESSAGE_PRIMARY);
+
+ SetVariable(pset.vars, "STATUS", PQresStatus(restat) + strlen("PGRES_"));
+ SetVariable(pset.vars, "ERROR_CODE", code ? code : "00000");
+ SetVariable(pset.vars, "ERROR_MESSAGE", mesg ? mesg : "");
+
+ /* check all possible PGRES_ */
+ switch (restat)
+ {
+ case PGRES_EMPTY_QUERY:
+ case PGRES_TUPLES_OK:
+ case PGRES_COMMAND_OK:
+ case PGRES_COPY_OUT:
+ case PGRES_COPY_IN:
+ case PGRES_COPY_BOTH:
+ case PGRES_SINGLE_TUPLE:
+ success = true;
+ break;
+ case PGRES_BAD_RESPONSE:
+ case PGRES_NONFATAL_ERROR:
+ case PGRES_FATAL_ERROR:
+ success = false;
+ break;
+ default:
+ /* dead code */
+ success = false;
+ psql_error("unexpected PQresultStatus: %d\n", restat);
+ break;
+ }
+
+ if (success)
+ {
+ char *ntuples = PQcmdTuples(results);
+ SetVariable(pset.vars, "ROW_COUNT", *ntuples ? ntuples : "0");
+ SetVariable(pset.vars, "ERROR", "TRUE");
+ }
+ else
+ {
+ SetVariable(pset.vars, "ROW_COUNT", "0");
+ SetVariable(pset.vars, "ERROR", "FALSE");
+ }
+}
/*
* SendQuery: send the query string to the backend
@@ -1346,6 +1402,9 @@ SendQuery(const char *query)
elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
}
+ /* set special variables to reflect the result status */
+ SetResultVariables(results);
+
/* but printing results isn't: */
if (OK && results)
OK = PrintQueryResults(results);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index d602aee..eaf538a 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2904,6 +2904,60 @@ bar 'bar' "bar"
\echo 'should print #8-1'
should print #8-1
\endif
+-- special result variables
+-- 2 rows select
+SELECT 1 AS stuff UNION SELECT 2;
+ stuff
+-------
+ 1
+ 2
+(2 rows)
+
+\echo 'status:' :STATUS
+status: TUPLES_OK
+\echo 'error code:' :ERROR_CODE
+error code: 00000
+\echo 'error message:' :ERROR_MESSAGE
+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;
+ ^
+\echo 'status:' :STATUS
+status: FATAL_ERROR
+\if :ERROR
+ \echo 'Oops, an error occured...'
+\endif
+\echo 'error code:' :ERROR_CODE
+error code: 42601
+\echo 'error message:' :ERROR_MESSAGE
+error message: syntax error at or near ";"
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
+-- empty query
+;
+\echo 'status:' :STATUS
+status: EMPTY_QUERY
+\echo 'error code:' :ERROR_CODE
+error code: 00000
+\echo 'error message:' :ERROR_MESSAGE
+error message:
+\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 'status:' :STATUS
+status: FATAL_ERROR
+\echo 'error code:' :ERROR_CODE
+error code: 42P01
+\echo 'error message:' :ERROR_MESSAGE
+error message: table "this_table_does_not_exist" does not exist
+\echo 'number of rows:' :ROW_COUNT
+number of rows: 0
-- SHOW_CONTEXT
\set SHOW_CONTEXT never
do $$
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b56a05f..79efa74 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -526,6 +526,39 @@ select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
\echo 'should print #8-1'
\endif
+-- special result variables
+
+-- 2 rows select
+SELECT 1 AS stuff UNION SELECT 2;
+\echo 'status:' :STATUS
+\echo 'error code:' :ERROR_CODE
+\echo 'error message:' :ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- syntax error
+SELECT 1 UNION;
+\echo 'status:' :STATUS
+\if :ERROR
+ \echo 'Oops, an error occured...'
+\endif
+\echo 'error code:' :ERROR_CODE
+\echo 'error message:' :ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- empty query
+;
+\echo 'status:' :STATUS
+\echo 'error code:' :ERROR_CODE
+\echo 'error message:' :ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
+-- other query error
+DROP TABLE this_table_does_not_exist;
+\echo 'status:' :STATUS
+\echo 'error code:' :ERROR_CODE
+\echo 'error message:' :ERROR_MESSAGE
+\echo 'number of rows:' :ROW_COUNT
+
-- SHOW_CONTEXT
\set SHOW_CONTEXT never
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
