many years ago I wrote / posted this handy function:
Code:
psql(sql, database="", debug=0, host="", username="") {
global psql_path ; by _dave_
database := database ? database : "PTPGSQL1"
host := host ? host : "localhost"
username := username ? username : "postgres"
stamp = %A_Now%
Random, r, 1000, 9999
file1 := stamp . "_" . A_ScriptName . "_" . r . "_in.txt"
file2 := stamp . "_" . A_ScriptName . "_" . r . "_out.txt"
file3 := stamp . "_" . A_ScriptName . "_" . r . "_error.txt"
command := "psql.exe -A -t -h " . host . " -p 5432 -U " . username . " -d """ . database . """ -f " . file1 . " -o " . file2 . " 2> " . file3 ; . " 2>&1"
Loop 3
FileDelete, % file%a_index%
FileAppend, \f '\t'`n, %file1%
FileAppend, %sql%`n, %file1%
FileAppend, \q`n, %file1%
RunWait, %comspec% /c %command%, , Hide
FileGetSize, errorsize, %file3%
If ( errorsize > 0 ) {
FileRead, errortext, %File3%
FileRead, errorcause, %File1%
Loop 3
FileDelete, % file%a_index%
if (debug) {
Msgbox, %errorcause%`n`n`n%errortext%
ExitApp
} else {
FileAppend,
( join
%A_Now% - An Error Occured:`n`nInput:`n%errorcause%`n
`nOutput:`n%errortext%`n`n`n`n
), %A_Scriptname%_Error_Log.txt
}
}
FileRead, sqlout, %File2%
Loop 3
FileDelete, % file%a_index%
StringTrimRight, sqlout, sqlout, 2
return %sqlout%
}
You'll need to set auth type "md5" to "trust" for localhost in pg_hba.conf, or use other methods to get past password authentication - can't pass it in on the command line non-interactively.
You'll need to place psql.exe in the same folder as a script using this function. And maybe some DLLs too if your PATH isn't set up for them (it will tell you).
you can set default database / host / username by editing the top few lines of this function.
usage example:
Code:
msgbox, % psql("select count(id) from players;")