Using An Environment Variable In A PSQL Script

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

Solution 1:

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

Solution 2:

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.

Solution 3:

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';