title: Accessing external XML files as variables in a PSQL script (sourced from a bash script)
body: <p>Following <a href="https://stackoverflow.com/questions/19007884/import-xml-files-to-postgresql/49950384#49950384">this example</a>, I am having trouble using a PostgreSQL variables in a *.sql script:</p>
  
  <ul>
  <li><p>I want to iterate over a number of XML data files, using a BASH script</p></li>
  <li><p>the BASH script assigns XML file names to a variable, that is passed to SQL script</p></li>
  <li><p>the SQL script, called by that BASH script, loads those data into PostgreSQL</p></li>
  </ul>
  
  <p>If I source the XML files directly, there is no problem; however, I cannot access that variable, in my SQL script:</p>
  
  <p>In my SQL script (<code>hmdb.sql</code>) I can access the PSQL variable <code>:bash_var</code> (passed from the BASH script):</p>
  
  <pre><code>\echo '\nEXTERNAL VARIABLE (= "datafile", HERE):' :bash_var '\n'
  </code></pre>
  
  <p>and/or directly reference the XML file,</p>
  
  <pre><code>datafile text := 'hmdb/hmdb.xml';
  </code></pre>
  
  <p>but not as a variable:</p>
  
  <pre><code>datafile text := 'bash_var';
  </code></pre>
  
  <hr>
  
  <p><strong>hmdb.sh</strong></p>
  
  <pre><code>#!/bin/bash
  
  DATA_DIR=data/
  
  for file in $DATA_DIR/*.xml
    do
      bash_var=$(echo ${file##*/})
      echo $bash_var
      psql -d hmdb -v bash_var=$bash_var -f hmdb.sql
  done
  </code></pre>