PSQL Error Level in Batch For Loop

Question

I am attempting to run a postgres query from within a batch file. However, I have thus far been unable to detect when the command fails. The following is what I have tried thus far:

@FOR /F %%A IN ('PSQL -U userName -d dbName -t -c "SELECT * FROM nonExistantTable" 2^>^&1') DO @(
    ECHO !ERRORLEVEL!
)

I have also tried the following:

1) Adding "CALL" prior to the sql command (CALL PSQL -U ...)
2) Adding "-v ON_ERROR_STOP=1" to the sql command, both with and without the "CALL" command (PSQL -U ... -v ON_ERROR_STOP=1)

However, the value of ERRORLEVEL is always coming out as zero. Does anyone know how to detect an error in a call to PSQL using batch? Specifically, the error I am getting is that the table doesn't exist. Thanks.

Answer 1

The for /f executes the command in a separate cmd instance and the errorlevel from this instance is not exported to the code inside to do clause.

The better way to handle it in pure batch is:

• Send the output of the program to a temporary file
• Check the error level
• If there are no errors process the file output
• In any case delete the temporary file at end

Something like

set "tempFile=%temp%\%~n0.%random%%random%%random%.tmp"
> "%tempFile%" 2>&1 (
    PSQL -U userName -d dbName -t -c "SELECT * FROM nonExistantTable"
)
if not errorlevel 1 (
    for /f "usebackq delims=" %%g in ("%tempFile%") do (
        echo %%g
    )
) else (
    echo ERRORLEVEL: %errorlevel%
)
del /q "%tempFile%"

Answer 2

The answer by @MCND can be summarized as:

1) Run the command normally, redirecting stdout to a temporary file
2) Process the contents of the file based on ERRORLEVEL

Based on that answer, I came up with a solution that fit my particular needs with less code. My case is a bit special, in that the PSQL command does exactly one of the following:

1) Writes the result to stdout, and returns zero
2) Writes an error to stderr, and returns nonzero

In case #1, I want my batch file to print the result and return 0. In case #2, I want to perform some custom logic, print the error, and return nonzero. For that logic, I found the following to work better:

:: Get a temporary file name
@SETLOCAL
@SET TEMP_OUT="%TEMP%\myScriptError.log"

:: Create the command that will be executed
@SET DB_SELECT=SELECT * FROM nonExistantTable
@SET DB_COMMAND=PSQL -U userName -d dbName -t -c "%DB_SELECT%"

:: Run the command
@FOR /F %%A IN ('%DB_COMMAND% 2^>%TEMP_OUT%') DO @(
    REM This code only runs if there are no errors
    SET EXIT_CODE=0
    GOTO :CLEANUP
)

:: Uh-oh, there was an error.  Print it to stderr, and set the exit code.
@MORE %TEMP_OUT% 1>&2
@SET EXIT_CODE=1
...

:: Clean up the temp file and return the result
:CLEANUP
@DEL /F /Q %TEMP_OUT%
@ENDLOCAL & EXIT /B %EXIT_CODE%