Geek Answers Handbook

Is it possible to refer to an environment variable in sql postgres command?

For example, let's say I wanted to import a CSV file from a path on the same computer where the postgres server is running.

The system has the environment variable MyPath '/path/to/my/csv/file/' .

I could easily import this CSV file as follows:

 COPY MyTable FROM '/path/to/my/csv/file/myTable.csv' DELIMITERS ',' CSV HEADER;

Is it possible to refer to the MyPath variable from this sql postgres command? Something in the following lines:

 COPY MyTable FROM get_environmental_variable('MyPath') || 'myTable.csv' DELIMITERS ',' CSV HEADER;
+6
source share
2 answers

Try this on startup

  psql --set 'var=foo' -c '\i thesqlscript'

And this is in the request

 update table set column = :var;

This is taken from this question on the forum.

If you are using an older version of postgres, this is similar to the same question asked in the postgres forum (although this is many years ago). There is no direct way, but they gave a couple of workarounds.

+6
source

If you install PL / R , you can use the plr_environ () function to get a list of environment variables and their values ​​as COPY MyTable from (SELECT VALUE FROM plr_environ() WHERE name = 'MyPath') ; other stored procedure languages ​​probably have similar methods - in plpython, I would suggest that you can use os.environ [MyPath], plperlu has access to the% ENV hash, etc. If you need more help, leave a comment, and I will be glad to look into this further for you.

+3
source

More articles:


All Articles