title: Is it possible to combine `-c` and `-v` in psql?
body: <p><strong>Abstract</strong></p>
  
  <p>Why this does not work:</p>
  
  <pre><code>$ psql -X -h localhost -d mydatabase -U postgres -v myschema=foo -c "SELECT :'myschema';"
  ERROR:  syntax error at or near ":"
  LINE 1: SELECT :'myschema';
                 ^
  </code></pre>
  
  <p>But this works as expected:</p>
  
  <pre><code>$ psql -X -h localhost -d mydatabase -U postgres -v myschema=foo
  
  mydatabase=# SELECT :'myschema';
   ?column? 
  ----------
   foo
  (1 row)
  </code></pre>
  
  <p>Is it possible to combine <code>-c</code> and <code>-v</code> in a <code>psql</code> call.</p>
  
  <p><strong>Context</strong></p>
  
  <p>I'm trying to execute a <code>DO</code> (<a href="https://stackoverflow.com/a/12082038/930271">this one</a>) PostgreSQL statement with <code>psql -c</code> from a bash script, where I want to pass one of the <code>WHERE</code> conditions as a <a href="https://www.postgresql.org/docs/10/app-psql.html#APP-PSQL-VARIABLES" rel="nofollow noreferrer">psql variable</a></p>
  
  <p>Something like this:</p>
  
  <pre><code>SCHEMA='foo'
  
  ! read -d '' sql_query &lt;&lt; "EOF"
  DO
  $func$
  BEGIN
     -- RAISE NOTICE '%', 
     EXECUTE
     (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
      FROM   pg_class
      WHERE  relkind = 'r'  -- only tables
      AND    relnamespace = :'myschema'::regnamespace
     );
  END
  $func$;    
  EOF
  
  psql -h localhost -U postgres -d mydatabase -v myschema="${SCHEMA}" -c "${sql_query}"
  </code></pre>
  
  <p>But it fails with a:</p>
  
  <pre><code>ERROR:  syntax error at or near ":"
  LINE 9:     AND    relnamespace = :'myschema'::regnamespace
  </code></pre>
  
  <p>Trying to debug the problem I write just this simple query:</p>
  
  <pre><code>psql -v myschema=foo -h localhost -d mydatabase -U postgres -c "SELECT :myschema;"
  ERROR:  42601: syntax error at or near ":"
  LINE 1: SELECT :myschema
                 ^
  LOCATION:  scanner_yyerror, scan.l:1087
  </code></pre>
  
  <p>or variations of it like:</p>
  
  <pre><code>psql -v myschema="'foo'" -h localhost -d mydatabase -U postgres -c "SELECT :myschema;"
  psql -v myschema 'foo' -h localhost -d mydatabase -U postgres -c "SELECT :myschema;"
  psql -v myschema=foo -h localhost -d mydatabase -U postgres -c "SELECT :'myschema';"
  </code></pre>
  
  <p>and neither works.</p>
  
  <p>So the question is. It's is possible to combine <code>-c</code> and <code>-v</code> in a <code>psql</code> call.</p>
  
  <p><strong>DO statements and psql variables</strong></p>
  
  <p>Thanks to the comments I discover that <a href="https://stackoverflow.com/a/28999217/930271">a workaround is needed</a> to mix <code>DO</code> statements and psql variables.</p>
  
  <p>Or fix it at <code>bash</code> level. This is how I'm doing it:</p>
  
  <pre><code>SCHEMA='foo'
  
  ! read -d '' sql_query &lt;&lt; EOF
  DO
  $func$
  BEGIN
     -- RAISE NOTICE '%', 
     EXECUTE
     (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
      FROM   pg_class
      WHERE  relkind = 'r'  -- only tables
      AND    relnamespace = '${SCHEMA}'::regnamespace
     );
  END
  $func$;    
  EOF
  
  psql -h localhost -U postgres -d mydatabase -c "${sql_query}"
  </code></pre>
  
  <p>But the question still is how (if possible) psql variables can be used with <code>-c</code></p>