CODEWITHSUNDEEP
linuxbashshell
Martin
Martin

Reputation: 100

Import file to script and fill variables inside the file

I have script import.sh

. properties
sql=$(cat sql.sql)
${psql_path}/psql \
  -X \
  --set ON_ERROR_STOP=on \
  --set AUTOCOMMIT=on \
  --echo-all \
  ${sql}
  ${dbname} > ${log_file} 2>> ${log_file}

and file properties

psql_path="/usr/pgsql-9.6/bin"
dbname="postgres"
path="/opt/files"
log_file="ok.log"

and file sql.sql

  -c "truncate table SCHEMA.TABLE1;" \
  -c "\\copy SCHEMA.TABLE1 FROM '${path}/TABLE1.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \
  -c "truncate table SCHEMA.TABLE2;" \
  -c "\\copy SCHEMA.TABLE1 FROM '${path}/TABLE2.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \

i need to run psql command with importing lines from file sql.sql on the line ${sql} in script import.sh but including text /opt/files instead of the variable itself ${path}, eg:

${psql_path}/psql \
  -X \
  --set ON_ERROR_STOP=on \
  --set AUTOCOMMIT=on \
  --echo-all \
  -c "truncate table SCHEMA.TABLE1;" \
  -c "\\copy SCHEMA.TABLE1 FROM '/opt/files/TABLE1.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \
  -c "truncate table SCHEMA.TABLE2;" \
  -c "\\copy SCHEMA.TABLE2 FROM '/opt/files/TABLE2.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \
  ${dbname} > ${log_file} 2>> ${log_file}

edit: all im getting right now is example below. How do i insert the text of variable ${path} ?

${psql_path}/psql \
  -X \
  --set ON_ERROR_STOP=on \
  --set AUTOCOMMIT=on \
  --echo-all \
  -c "truncate table SCHEMA.TABLE1;" \
  -c "\\copy SCHEMA.TABLE1 FROM '${path}/TABLE1.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \
  -c "truncate table SCHEMA.TABLE2;" \
  -c "\\copy SCHEMA.TABLE2 FROM '${path}/TABLE2.tsv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"'" \
  ${dbname} > ${log_file} 2>> ${log_file}

Upvotes: 0

Views: 236

Answers (2)

grail
grail

Reputation: 930

As advised, the HEREDOC is the best option and then simple parameter substitution can be done:

. properties

sql=$(cat sql.sql)

${psql_path}/psql <<-EOF
  -X
  --set ON_ERROR_STOP=on
  --set AUTOCOMMIT=on
  --echo-all
  ${sql//\$\{path\}/$path}
  ${dbname} > ${log_file} 2>> ${log_file}
EOF

Notice too that the line extensions are no longer required either. You should be able to remove them from sql.sql file as well

Upvotes: 1

Marco Silva
Marco Silva

Reputation: 323

This line:

sql=$(cat sql.sql)

Will put contents of "sql.sql" into this variable $sql but won't evaluate any variables inside it. The right way to do this is to use a heredoc, instead. But since I figure out you want to keep your script, a simple solution would be:

sql=$(cat sql.sql | sed -e 's@${path}@'"${path}"'@')

This is simple running sed to substitute the contents of "${path}" into the contents of shell variable of the same name. But anyway consider using heredocs, because your "sql" file is obviously not SQL at all.

Upvotes: 1

Related Questions