CODEWITHSUNDEEP
postgresqlpsqlpostgresql-9.6
greatvovan
greatvovan

Reputation: 3177

psql SQL Interpolation in a code block

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?

Upvotes: 3

Views: 2511

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

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)

Upvotes: 1

Related Questions