How to do a postgres Select statement which contains round brackets in a batch?

Question

I am trying to do a select in to a postgres DB via batch file. The postgres DB offers a command line interface (psql) where you can pipe in DB commands which is here done in for loop. Look at how pg_cmd is stiched together. The select pd_SelCmd is echoed to pg_SelCall. In the for statement the command is executed but since the select contains round brackets, they cause a miss interpretation and an error: "FROM" cant be processed syntactically at this point.

How can the round brackets be kind of escaped to get the request to work?

The expected response from DB looks like:

          max
-------------------------
2016-12-29 09:40:09.842
(1 Line)

The batch used so far was this

setlocal enabledelayedexpansion
Set "PgRootPath=C:\Program Files\PostgreSql\9.5.5-1\bin"
Call :GetDoneTime 56665454 DONE_TIME
echo = DONE_TIME=!DONE_TIME!
Goto :EOF

:GetDoneTime
set "ERRORLEVEL="
set "MC_UID=%~1"
set "ReturnDoneTimeValueRef=%~2"
set "DataVal=NULL"
set "PGPASSWORD=frontenduser"
set "PGCLIENTENCODING=utf-8"

set pd_SelCmd=SELECT max(t.endDate) FROM Ticket t JOIN Device d on t.device_id = d.id WHERE t.state in ('APPROVED','IN_PROGRESS', 'IN_ACTIVITY') AND d.uid='!MC_UID!';
set pg_SelCall="!PgRootPath!\psql" -U frontenduser -h localhost -d ppsdb
REM if call to psql produces an fatal error, the error number will be passed to for loop on third parameter in third line
set pg_cmd="echo !pd_SelCmd! | !pg_SelCall! || echo/ & echo/ & call echo NULL NULL %%^^^errorlevel%%"
set "pg_cmd=!pg_cmd:)=^)!"

REM Execute PG command. Resulting DataValue obtained from third row
REM Check for errors of call
for /f "skip=2 tokens=1,2,3" %%i in ('!pg_cmd!') do (       
    REM Get value in first and second parameter from split - which is from third row
    set "DataVal=%%i %%j"
    REM If error happend, report it. Error code is obtained in 3rd parameter.
    if "!DataVal!"=="NULL NULL" (
        echo ## Postgres DB operation failed with ERROR: %%~k
        set "DataVal=NULL"
    ) else (
        REM Check if result is not valid
        if "!DataVal:~0,1!"=="(" set "DataVal=NULL"
    )
    goto GotDoneTime
)
:GotDoneTime
    if not '!ReturnDoneTimeValueRef!'=='' set "!ReturnDoneTimeValueRef!=!DataVal!"
    if "!DataVal!"=="NULL" exit /b 1
    exit /b 0

Answer 1

Just a few errors here. Unfortunately, I don't know postgresql, nor do I have the requisite database, so I can't test it, but here goes...

First, labels are not allowed in a code-block (parenthesised series of statements) and :: is a broken label. Within a code block, rem should be used for remarks.

Next, replacing your '!pg_cmd! in the for loop with type q41353737.txt (where q41353737.txt is a file containing the expected data output from the command you posted) then dataval is set to 09:40:09.842 BUT since there is a fourth line in the file, the next line will also be processed and dataval will be set to Line).

To overcome this, you could simply change this to

   rem If error happened, report it. Error code is obtained in 3rd parameter.
   if not '%%~k'=='' echo ## Postgres DB operation failed with ERROR: %%~k
   rem If operation succeeded, get value in second parameter from split - which is from second data column
   if '%%~k'=='' SET DataVal=%%~j
goto done
)
:done

so that only the third line is processed and then the for loop is unceremoniously terminated.

Next problem is the problem of which you complain. The ) is terminating the for ... (, so you need to tell cmd that that particular ) is part of the command to be executed, not of the for, so you need to escape it with a caret (( becomes ^))

The easiest way is probably, just before the for to add a line

set "pg_cmd=!pg_cmd:)=^)!"

which should appropriately prefix all ) in the command withh the requisite caret.

I suspect you'll also have trouble with the other problem characters so a similar process applied to | will probably be required, ie.

set "pg_cmd=!pg_cmd:|=^|!"

Which is where I have to leave it, since I've no way of correctly executing the command itself.