executes psql.exe with arguments

execSQL.go

// ExecSQL executes psql.exe with arguments
// assumes cmdArgs either contains sqlfile or statement. example: ("-f", "install.sql" | "-c", <sql statement>  )
// singleTransaction=true to run all commands as a single-transaction/rollback on error
func ExecSQL2(log *logrus.Logger, dbcfg *DbConfig, cmdArgs []string, packageRoot string, logSuffix string, singleTransaction bool) (output []string, err error) {

	log.Println("Setting Environment Variables:")
	os.Setenv("VERBOSITY", "verbose")
	log.Println("VERBOSITY=" + os.Getenv("VERBOSITY"))
	os.Setenv("PGPASSWORD", dbcfg.Password)
	log.Println("PGPASSWORD=" + "**********")
	os.Setenv("PGCLIENTENCODING", "UTF8")
	log.Println("PGCLIENTENCODING=" + os.Getenv("PGCLIENTENCODING"))

	cmdName, err := discoverPgTools("psql")
	if err != nil {
		return output, fmt.Errorf("Error locating psql executable. Options are: Add psql.exe bin directory to PATH or PGTOOLS_DIR" +
			"environment variables.")
	}

	args := []string{"-h", dbcfg.HostName, "-p", dbcfg.Port, "-U", dbcfg.Username, "-d", dbcfg.Database}

	args = append(args, cmdArgs...) // args passed into function
	args = append(
		args,
		"-v", "ON_ERROR_STOP=1", // Exit on first error encountered
		"--pset", "pager=off", // turn off paging
	)

	if singleTransaction { // if singleTransaction bool supplied
		args = append(args, "--single-transaction") // wrap all the commands into a single transaction
	}

	args = append(args, "-a", // print sql commands (not just the output of sql commands)
		"-b", // Print failed SQL commands to standard error output.
		"-e", // Copy all SQL commands sent to the server to standard output as well.
	)

	log.Println("\nExecuting: "+cmdName, strings.Join(args, " ")+"\n")
	cmd := exec.Command(cmdName, args...)

	cmdReader, err := cmd.StdoutPipe()
	if err != nil {
		log.Println("Error creating StdoutPipe for Cmd", err)
		return
	}

	scanner := bufio.NewScanner(cmdReader)
	go func() {
		for scanner.Scan() {
			line := scanner.Text() 			
			output = append(output, line) 
			log.Printf("%s | %s\n", logSuffix, line)
		}
	}()

	cmdReaderErr, err := cmd.StderrPipe()
	if err != nil {
		log.Println("Error creating StdoutErrorPipe for Cmd", err)
		return
	}

	scannerErr := bufio.NewScanner(cmdReaderErr)
	var stdErrOut string // line buffer
	go func() {
		for scannerErr.Scan() {
			log.Printf("%s | %s\n", logSuffix, scannerErr.Text())
			line := scannerErr.Text() 
			stdErrOut = line // store last err to use below
			output = append(output, line) 
		}
	}()

	err = cmd.Start()
	if err != nil {
		log.Println("Error starting Cmd", err)
		return
	}

	if err := cmd.Wait(); err != nil {
		return output, errors.New("Psql returned an error " + stdErrOut)
	}
	
	return
}