Method ExecInDatabasesFromQuery

internal/postgres/exec.go:70–107

ExecInDatabasesFromQuery uses "bash" and "psql" to execute sql in every database returned by the databases query. The sql statement(s) may contain psql variables that are assigned from the variables map. - https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-VARIABLES

(
	ctx context.Context, databases, sql string, variables map[string]string,
)

// psql variables that are assigned from the variables map.
// - https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-VARIABLES
func (exec Executor) ExecInDatabasesFromQuery(
	ctx context.Context, databases, sql string, variables map[string]string,
) (string, string, error) {
	// Use a Bash loop to call `psql` multiple times. The query to run in every
	// database is passed via standard input while the database query is passed
	// as the first argument. Remaining arguments are passed through to `psql`.
	stdin := strings.NewReader(sql)

	// Using make to preallocate args (using 1 (databases) + len(variables)) triggered
	// a CodeQL scanning error that a potentially large value might cause an overflow.
	// To address this, don't preallocate args and have the linter ignore this line.
	args := []string{databases} //nolint:prealloc
	for k, v := range variables {
		args = append(args, "--set="+k+"="+v)
	}

	// The map iteration above is nondeterministic. Sort the variable arguments
	// so that calls to exec are deterministic.
	// - https://golang.org/ref/spec#For_range
	sort.Strings(args[1:])

	const script = `
sql_target=$(< /dev/stdin)
sql_databases="$1"
shift 1

databases=$(psql "$@" -Xw -Aqt --file=- <<< "${sql_databases}")
while IFS= read -r database; do
	PGDATABASE="${database}" psql "$@" -Xw --file=- <<< "${sql_target}"
done <<< "${databases}"
`

	// Execute the script with some error handling enabled.
	var stdout, stderr bytes.Buffer
	err := exec(ctx, stdin, &stdout, &stderr,
		append([]string{"bash", "-ceu", "--", script, "-"}, args...)...)
	return stdout.String(), stderr.String(), err
}