sql - Using an environment variable in a PSQL script

Question

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?

question from:https://stackoverflow.com/questions/18725880/using-an-environment-variable-in-a-psql-script

Answer 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