PostgreSQL psql如何用\set变量构建含下划线的SQL标识符

问题场景 #

环境：PostgreSQL 14.11

常规的psql变量替换可以正常执行：

CDSLBXW=# \set sch public
CDSLBXW=# \set tbl job_history
CDSLBXW=# \set fld job_id
CDSLBXW=# CREATE INDEX blarge ON :sch.:tbl (:fld);
CREATE INDEX

但当尝试生成带下划线的有意义索引名（比如tmpidx_表名_字段名）时，直接写会触发语法错误：

CDSLBXW=# CREATE INDEX CONCURRENTLY IF NOT EXISTS tmpidx_:tbl_:fld ON :sch.:tbl (:fld);
ERROR:  syntax error at or near ":"
LINE 1: CREATE INDEX CONCURRENTLY IF NOT EXISTS tmpidx_:tbl_job_id O...

                                                   ^

原因是psql不支持bash那样用大括号分隔变量名，它会把:tbl_job_id当成一个未定义的变量，导致解析失败。

直接解决方法 #

方法1：提前用\set拼接索引名变量 #

先把要生成的索引名单独定义成一个变量，再在创建语句中引用，这是最直接的方式：

CDSLBXW=# \set sch public
CDSLBXW=# \set tbl job_history
CDSLBXW=# \set fld job_id
CDSLBXW=# \set idx_name tmpidx_:tbl_:fld
CDSLBXW=# CREATE INDEX CONCURRENTLY IF NOT EXISTS :idx_name ON :sch.:tbl (:fld);

psql会正确解析:tbl和:fld变量，拼接出tmpidx_job_history_job_id这样的索引名，再执行创建语句。

方法2：利用psql的变量值引用语法拼接 #

如果不想单独定义变量，也可以用:'var'获取变量的字符串值，通过\set直接拼接：

CDSLBXW=# \set sch public
CDSLBXW=# \set tbl job_history
CDSLBXW=# \set fld job_id
CDSLBXW=# \set idx_name 'tmpidx_' || :'tbl' || '_' || :'fld'
CDSLBXW=# CREATE INDEX CONCURRENTLY IF NOT EXISTS :idx_name ON :sch.:tbl (:fld);

补充：已有的可行方案 #

你之前找到的format()结合\gexec的方案也能正常工作，但上面的方法步骤更少，更直接：

SELECT format('CREATE INDEX tmpidx_%2$I_%3$I ON %1$I.%2$I (%3$I);', 
              :'sch', :'tbl', :'fld') \gexec

内容的提问来源于stack exchange，提问作者RonJohn