Is it possible to use a Linux environment variable inside a .sql file? I'm using the copy/select query to write to an output file, and I'll like to put that directory in a variable. So I want to do something like:
COPY (SELECT * FROM a)
TO $outputdir/a.csv
Outputdir would be set in my environment. Is this possible?
You can store the result of a shell command inside a psql variable like this:
\set afile `echo "$outputdir/a.csv"`
COPY (SELECT * FROM a) TO :'afile';
Another (better in my opinion) solution is to use only psql variables, see this answer of mine about psql variables, which is similar to your example. A example for your case would be:
\set outputdir '/path/to/output'
\set afile :outputdir '/a.csv'
COPY (SELECT * FROM a) TO :'afile';
Note that, in the example, you need to set the variable inside the script file, but you can skip the first line if you set it when you call psql:
psql --set=outputdir="$outputdir" <conn parameters> -f /path/to/yourscript.sql
echo $outputdir syntax for accessing environment variables works perfectly on Unix. On Windows a different incantation is required; see my answer below.This appears to work for your use case, provided you single quote the output file name as I mentioned. It will escape any double quotes as well contained within the SQL.
psql -c "$(eval echo '"' $(<envvars.sql | sed 's/"/\\"/g') '"')"
Of course, note that if your file contains any dollar quoted variables, the shell is going to try to interpret as a variable, and your script will break, so you will need to escape any dollar signs you need preserved literally with a backslash.
See also the second snippet in the accepted answer to this question for a possibly more robust answer.
The accepted answer is correct for PostgreSQL running on Unix. Under Windows a different incantation is required for obtaining the value of the environment variable from the CMD shell and for avoiding the carriage return returned by the echo command.
\set afile `set /p=%outputdir%/a.csv`
COPY (SELECT * FROM a) TO :'afile';
