Is it possible to combine `-c` and `-v` in psql?

Question

Abstract

Why this does not work:

$ psql -X -h localhost -d mydatabase -U postgres -v myschema=foo -c "SELECT :'myschema';"
ERROR:  syntax error at or near ":"
LINE 1: SELECT :'myschema';
               ^

But this works as expected:

$ psql -X -h localhost -d mydatabase -U postgres -v myschema=foo

mydatabase=# SELECT :'myschema';
 ?column? 
----------
 foo
(1 row)

Is it possible to combine -c and -v in a psql call.

Context

I'm trying to execute a DO (this one) PostgreSQL statement with psql -c from a bash script, where I want to pass one of the WHERE conditions as a psql variable

Something like this:

SCHEMA='foo'

! read -d '' sql_query << "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}"

But it fails with a:

ERROR:  syntax error at or near ":"
LINE 9:     AND    relnamespace = :'myschema'::regnamespace

Trying to debug the problem I write just this simple query:

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

or variations of it like:

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';"

and neither works.

So the question is. It's is possible to combine -c and -v in a psql call.

DO statements and psql variables

Thanks to the comments I discover that a workaround is needed to mix DO statements and psql variables.

Or fix it at bash level. This is how I'm doing it:

SCHEMA='foo'

! read -d '' sql_query << 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}"

But the question still is how (if possible) psql variables can be used with -c

Answer 1

So the question is. It's is possible to combine -c and -v in a psql call.

No, psql does not expand variables in queries passed on the command line. It's implied in the documentation by the bit outlined in bold below:

-c command
--command=command

Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command.