5

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

Francisco Puga
  • 23,869
  • 5
  • 48
  • 64
  • No. `do` body is just a string (dollar quoted) and it used as is, without psql variables substitution. Lets say it is same to `select 'foo :myschema bar';` or `select $str$foo :myschema bar$str$;` Just use bash variable substitution instead. – Abelisto Nov 19 '19 at 18:03
  • What about the non-`do` cases like `psql -v myschema=foo -h localhost -d mydatabase -U postgres -c "SELECT :'myschema';"`? – that other guy Nov 19 '19 at 18:11
  • 1
    Does this answer your question? [Use variable set by psql meta-command inside of DO block](https://stackoverflow.com/questions/28997527/use-variable-set-by-psql-meta-command-inside-of-do-block) – Abelisto Nov 19 '19 at 18:20
  • @Abelisto thanks for the research. It answers the concrete use case of the `DO` that anyway can be resolved escaping dollar quotes or other `bash` strategies, but still it does not answer why the simple `SELECT :myschema;` query with `-c` does not work. – Francisco Puga Nov 19 '19 at 18:37
  • Because after substitution it will be `SELECT foo;` (single quotes removed by psql while variable assigned). @thatotherguy already provide the possible solution. If you want to use variable inside string then it should be something like `select 'foo ' || :'myvar' || ' bar';` – Abelisto Nov 19 '19 at 18:45
  • Thanks @Abelisto I updated my question to clarify the points you mention. As you said theres is a bug it should be `SELECT :'myschema'` and not `SELECT :myschema;` but the broad question still applies. – Francisco Puga Nov 19 '19 at 19:40

1 Answers1

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.

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156