2

I have a SQL script that will be used to make changes in the database. For audit and traceability reasons, I want the script to generate an output file name based on the DBNAME and timestamp. 

db_test=> select 'script_output-' || :'DBNAME' || '-' || to_char(now(),'yyyymmdd-HH24MISS') || '.txt' as spoolfile; spoolfile -------------------------------------------- script_output-db_test-20190718-163936.txt

However, when I try to assign the query result to the \out operator I get the following 

db_test=> \out select 'script_output-' || :'DBNAME' || '-' || to_char(now(),'yyyymmdd-HH24MISS') || '.txt' \out: extra argument "script_output-" ignored \out: extra argument "||" ignored \out: extra argument ":'DBNAME'" ignored \out: extra argument "||" ignored \out: extra argument "-" ignored \out: extra argument "||" ignored \out: extra argument "to_char(now(),yyyymmdd-HH24MISS)" ignored \out: extra argument "||" ignored \out: extra argument ".txt" ignored

The output file actually created was named select 

SDV184022L:~ myuser$ ls -la Downloads/DB-Install/sele* -rw-r--r-- 1 myuser myuser 0B Jul 18 11:42 Downloads/DB-Install/select

Based on these results and lots of searching, I'm going to assume this is not possible so does anyone have an alternative way to accomplish generating a dynamic filename for the output directive?

Improve this question

2 Answers 2

Reset to default
1

Results generated by a query can be transfered to auto-instantiated variables in psql with \gset. The names of the variables are the names of the columns. Try

db_test=> select 'script_output-' || :'DBNAME' || '-' || to_char(now(),'yyyymmdd-HH24MISS') || '.txt' as spoolfile \gset

Then you can do

db_test=> \out :spoolfile

Be aware that the variable is handled with a "macro" kind of expansion. So if the value of :spoolfile happened to contain spaces or backslashes, psql would interpret them as if they had been input on the command line.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the solution, that is exactly what I was looking for. Will add this to my SQL template file.
0

You may use psql variable to store the result of the query by running a psql command within it.

knayak=# \set v_spoolfile `psql -A -t -c "select 'script_output-' || current_database() || '-' ||to_char(now(),'yyyymmdd-HH24MISS') || '.txt' as spoolfile" yourdbname` knayak=# \echo :v_spoolfile script_output-knayak-20190718-225537.txt knayak=# \out :v_spoolfile
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.