psql SQL Interpolation in a code block

Question

In some of my scripts I use SQL Interpolation feature of psql utility:

basic.sql:

update :schema.mytable set ok = true;

> psql -h 10.0.0.1 -U postgres -f basic.sql -v schema=myschema

Now I need bit more complicated scenario. I need to specify schema name (and desirebly some other things) inside PL/pgSQL code block:

pg.sql

do
$$
begin
  update :schema.mytable set ok = true;
end;
$$

But unfortunately this does not work, since psql does not replace :variables inside $$.

Is there a way to workaround it in general? Or more specifically, how to substitute schema names into pgSQL code block or function definition?

See [PSQL Command Line Arguments in DO script](https://stackoverflow.com/q/38174506) and links within for some workarounds. But nothing as simple or straightforward as interpolation inside string done by psql, if it existed. — Daniel Vérité, Oct 17 '17 at 20:47
@DanielVérité, found something useful, but still no solution if two schemas need to be referenced (e.g. `source` and `dest`) apart form dynamic SQL, which I would like to avoid. But thanks anyway. — greatvovan, Oct 18 '17 at 16:20

Vao Tsun · Answer 1 · 2017-10-17T17:11:21.213

in your referenced docs:

Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn't work to produce a quoted literal from a variable's value (and it would be unsafe if it did work, since it wouldn't correctly handle quotes embedded in the value).

it does not matter if quotes are double dollar sign or single quote - it wont work, eg:

do
'
begin
  update :schema.mytable set ok = true;
end;
'
ERROR:  syntax error at or near ":"

to pass variable into quoted statement other way you can try using shell variables, eg:

MacBook-Air:~ vao$ cat do.sh; export schema_name='smth' && bash do.sh
psql -X so <<EOF
\dn+
do
\$\$
begin
  execute format ('create schema %I','$schema_name');
end;
\$\$
;
\dn+
EOF

                         List of schemas
   Name   |  Owner   | Access privileges |      Description
----------+----------+-------------------+------------------------
 public   | vao      | vao=UC/vao       +| standard public schema
          |          | =UC/vao           |
 schema_a | user_old |                   |
(2 rows)

DO
                         List of schemas
   Name   |  Owner   | Access privileges |      Description
----------+----------+-------------------+------------------------
 public   | vao      | vao=UC/vao       +| standard public schema
          |          | =UC/vao           |
 schema_a | user_old |                   |
 smth     | vao      |                   |
(3 rows)

I see it clearly, but stil there is no answer how to deal with it. — greatvovan, Oct 17 '17 at 16:51
Then it's better to use dynamic SQL, much more straightforward way for DB scripting. — greatvovan, Oct 17 '17 at 18:42

psql SQL Interpolation in a code block

1 Answers1