Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit69835bc

Browse files
committed
Add psql variables to track success/failure of SQL queries.
This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated afterevery query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE,which are updated only when a query fails. The expected usage of theseis for scripting.Fabien Coelho, reviewed by Pavel StehuleDiscussion:https://postgr.es/m/alpine.DEB.2.20.1704042158020.12290@lancre
1 parent6e7baa3 commit69835bc

File tree

6 files changed

+324
-1
lines changed

6 files changed

+324
-1
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3517,6 +3517,16 @@ bar
35173517
</listitem>
35183518
</varlistentry>
35193519

3520+
<varlistentry>
3521+
<term><varname>ERROR</varname></term>
3522+
<listitem>
3523+
<para>
3524+
<literal>true</> if the last SQL query failed, <literal>false</> if
3525+
it succeeded. See also <varname>SQLSTATE</>.
3526+
</para>
3527+
</listitem>
3528+
</varlistentry>
3529+
35203530
<varlistentry>
35213531
<term><varname>FETCH_COUNT</varname></term>
35223532
<listitem>
@@ -3653,6 +3663,19 @@ bar
36533663
</listitem>
36543664
</varlistentry>
36553665

3666+
<varlistentry>
3667+
<term><varname>LAST_ERROR_MESSAGE</varname></term>
3668+
<term><varname>LAST_ERROR_SQLSTATE</varname></term>
3669+
<listitem>
3670+
<para>
3671+
The primary error message and associated SQLSTATE code for the most
3672+
recent failed query in the current <application>psql</> session, or
3673+
an empty string and <literal>00000</> if no error has occurred in
3674+
the current session.
3675+
</para>
3676+
</listitem>
3677+
</varlistentry>
3678+
36563679
<varlistentry>
36573680
<term>
36583681
<varname>ON_ERROR_ROLLBACK</varname>
@@ -3732,6 +3755,16 @@ bar
37323755
</listitem>
37333756
</varlistentry>
37343757

3758+
<varlistentry>
3759+
<term><varname>ROW_COUNT</varname></term>
3760+
<listitem>
3761+
<para>
3762+
The number of rows returned or affected by the last SQL query, or 0
3763+
if the query failed or did not report a row count.
3764+
</para>
3765+
</listitem>
3766+
</varlistentry>
3767+
37353768
<varlistentry>
37363769
<term><varname>SERVER_VERSION_NAME</varname></term>
37373770
<term><varname>SERVER_VERSION_NUM</varname></term>
@@ -3784,6 +3817,17 @@ bar
37843817
</listitem>
37853818
</varlistentry>
37863819

3820+
<varlistentry>
3821+
<term><varname>SQLSTATE</varname></term>
3822+
<listitem>
3823+
<para>
3824+
The error code (see <xref linkend="errcodes-appendix">) associated
3825+
with the last SQL query's failure, or <literal>00000</> if it
3826+
succeeded.
3827+
</para>
3828+
</listitem>
3829+
</varlistentry>
3830+
37873831
<varlistentry>
37883832
<term><varname>USER</varname></term>
37893833
<listitem>

‎src/bin/psql/common.c

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -548,11 +548,58 @@ AcceptResult(const PGresult *result)
548548
}
549549

550550

551+
/*
552+
* Set special variables from a query result
553+
* - ERROR: true/false, whether an error occurred on this query
554+
* - SQLSTATE: code of error, or "00000" if no error, or "" if unknown
555+
* - ROW_COUNT: how many rows were returned or affected, or "0"
556+
* - LAST_ERROR_SQLSTATE: same for last error
557+
* - LAST_ERROR_MESSAGE: message of last error
558+
*
559+
* Note: current policy is to apply this only to the results of queries
560+
* entered by the user, not queries generated by slash commands.
561+
*/
562+
staticvoid
563+
SetResultVariables(PGresult*results,boolsuccess)
564+
{
565+
if (success)
566+
{
567+
constchar*ntuples=PQcmdTuples(results);
568+
569+
SetVariable(pset.vars,"ERROR","false");
570+
SetVariable(pset.vars,"SQLSTATE","00000");
571+
SetVariable(pset.vars,"ROW_COUNT",*ntuples ?ntuples :"0");
572+
}
573+
else
574+
{
575+
constchar*code=PQresultErrorField(results,PG_DIAG_SQLSTATE);
576+
constchar*mesg=PQresultErrorField(results,PG_DIAG_MESSAGE_PRIMARY);
577+
578+
SetVariable(pset.vars,"ERROR","true");
579+
580+
/*
581+
* If there is no SQLSTATE code, use an empty string. This can happen
582+
* for libpq-detected errors (e.g., lost connection, ENOMEM).
583+
*/
584+
if (code==NULL)
585+
code="";
586+
SetVariable(pset.vars,"SQLSTATE",code);
587+
SetVariable(pset.vars,"ROW_COUNT","0");
588+
SetVariable(pset.vars,"LAST_ERROR_SQLSTATE",code);
589+
SetVariable(pset.vars,"LAST_ERROR_MESSAGE",mesg ?mesg :"");
590+
}
591+
}
592+
593+
551594
/*
552595
* ClearOrSaveResult
553596
*
554597
* If the result represents an error, remember it for possible display by
555598
* \errverbose. Otherwise, just PQclear() it.
599+
*
600+
* Note: current policy is to apply this to the results of all queries,
601+
* including "back door" queries, for debugging's sake. It's OK to use
602+
* PQclear() directly on results known to not be error results, however.
556603
*/
557604
staticvoid
558605
ClearOrSaveResult(PGresult*result)
@@ -1107,6 +1154,8 @@ ProcessResult(PGresult **results)
11071154
first_cycle= false;
11081155
}
11091156

1157+
SetResultVariables(*results,success);
1158+
11101159
/* may need this to recover from conn loss during COPY */
11111160
if (!first_cycle&& !CheckConnection())
11121161
return false;
@@ -1526,6 +1575,7 @@ DescribeQuery(const char *query, double *elapsed_msec)
15261575
if (PQresultStatus(results)!=PGRES_COMMAND_OK)
15271576
{
15281577
psql_error("%s",PQerrorMessage(pset.db));
1578+
SetResultVariables(results, false);
15291579
ClearOrSaveResult(results);
15301580
return false;
15311581
}
@@ -1599,6 +1649,7 @@ DescribeQuery(const char *query, double *elapsed_msec)
15991649
_("The command has no result, or the result has no columns.\n"));
16001650
}
16011651

1652+
SetResultVariables(results,OK);
16021653
ClearOrSaveResult(results);
16031654

16041655
returnOK;
@@ -1626,6 +1677,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
16261677
boolis_pipe;
16271678
boolis_pager= false;
16281679
boolstarted_txn= false;
1680+
int64total_tuples=0;
16291681
intntuples;
16301682
intfetch_count;
16311683
charfetch_cmd[64];
@@ -1663,6 +1715,8 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
16631715
results=PQexec(pset.db,buf.data);
16641716
OK=AcceptResult(results)&&
16651717
(PQresultStatus(results)==PGRES_COMMAND_OK);
1718+
if (!OK)
1719+
SetResultVariables(results,OK);
16661720
ClearOrSaveResult(results);
16671721
termPQExpBuffer(&buf);
16681722
if (!OK)
@@ -1738,6 +1792,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
17381792

17391793
OK=AcceptResult(results);
17401794
Assert(!OK);
1795+
SetResultVariables(results,OK);
17411796
ClearOrSaveResult(results);
17421797
break;
17431798
}
@@ -1755,6 +1810,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
17551810
*/
17561811

17571812
ntuples=PQntuples(results);
1813+
total_tuples+=ntuples;
17581814

17591815
if (ntuples<fetch_count)
17601816
{
@@ -1816,6 +1872,21 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec)
18161872
ClosePager(fout);
18171873
}
18181874

1875+
if (OK)
1876+
{
1877+
/*
1878+
* We don't have a PGresult here, and even if we did it wouldn't have
1879+
* the right row count, so fake SetResultVariables(). In error cases,
1880+
* we already set the result variables above.
1881+
*/
1882+
charbuf[32];
1883+
1884+
SetVariable(pset.vars,"ERROR","false");
1885+
SetVariable(pset.vars,"SQLSTATE","00000");
1886+
snprintf(buf,sizeof(buf),INT64_FORMAT,total_tuples);
1887+
SetVariable(pset.vars,"ROW_COUNT",buf);
1888+
}
1889+
18191890
cleanup:
18201891
if (pset.timing)
18211892
INSTR_TIME_SET_CURRENT(before);

‎src/bin/psql/help.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -337,7 +337,7 @@ helpVariables(unsigned short int pager)
337337
* Windows builds currently print one more line than non-Windows builds.
338338
* Using the larger number is fine.
339339
*/
340-
output=PageOutput(147,pager ?&(pset.popt.topt) :NULL);
340+
output=PageOutput(156,pager ?&(pset.popt.topt) :NULL);
341341

342342
fprintf(output,_("List of specially treated variables\n\n"));
343343

@@ -360,6 +360,8 @@ helpVariables(unsigned short int pager)
360360
" if set to \"noexec\", just show them without execution\n"));
361361
fprintf(output,_(" ENCODING\n"
362362
" current client character set encoding\n"));
363+
fprintf(output,_(" ERROR\n"
364+
" true if last query failed, else false\n"));
363365
fprintf(output,_(" FETCH_COUNT\n"
364366
" the number of result rows to fetch and display at a time (0 = unlimited)\n"));
365367
fprintf(output,_(" HISTCONTROL\n"
@@ -374,6 +376,9 @@ helpVariables(unsigned short int pager)
374376
" number of EOFs needed to terminate an interactive session\n"));
375377
fprintf(output,_(" LASTOID\n"
376378
" value of the last affected OID\n"));
379+
fprintf(output,_(" LAST_ERROR_MESSAGE\n"
380+
" LAST_ERROR_SQLSTATE\n"
381+
" message and SQLSTATE of last error, or empty string and \"00000\" if none\n"));
377382
fprintf(output,_(" ON_ERROR_ROLLBACK\n"
378383
" if set, an error doesn't stop a transaction (uses implicit savepoints)\n"));
379384
fprintf(output,_(" ON_ERROR_STOP\n"
@@ -388,6 +393,8 @@ helpVariables(unsigned short int pager)
388393
" specifies the prompt used during COPY ... FROM STDIN\n"));
389394
fprintf(output,_(" QUIET\n"
390395
" run quietly (same as -q option)\n"));
396+
fprintf(output,_(" ROW_COUNT\n"
397+
" number of rows returned or affected by last query, or 0\n"));
391398
fprintf(output,_(" SERVER_VERSION_NAME\n"
392399
" SERVER_VERSION_NUM\n"
393400
" server's version (in short string or numeric format)\n"));
@@ -397,6 +404,8 @@ helpVariables(unsigned short int pager)
397404
" if set, end of line terminates SQL commands (same as -S option)\n"));
398405
fprintf(output,_(" SINGLESTEP\n"
399406
" single-step mode (same as -s option)\n"));
407+
fprintf(output,_(" SQLSTATE\n"
408+
" SQLSTATE of last query, or \"00000\" if no error\n"));
400409
fprintf(output,_(" USER\n"
401410
" the currently connected database user\n"));
402411
fprintf(output,_(" VERBOSITY\n"

‎src/bin/psql/startup.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,6 +165,10 @@ main(int argc, char *argv[])
165165
SetVariable(pset.vars,"VERSION_NAME",PG_VERSION);
166166
SetVariable(pset.vars,"VERSION_NUM",CppAsString2(PG_VERSION_NUM));
167167

168+
/* Initialize variables for last error */
169+
SetVariable(pset.vars,"LAST_ERROR_MESSAGE","");
170+
SetVariable(pset.vars,"LAST_ERROR_SQLSTATE","00000");
171+
168172
/* Default values for variables (that don't match the result of \unset) */
169173
SetVariableBool(pset.vars,"AUTOCOMMIT");
170174
SetVariable(pset.vars,"PROMPT1",DEFAULT_PROMPT1);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp