PostgreSQL SUBSTRING查询出现语法错误，请求排查解决

尝试从vm.location_path中提取方括号[]之间的文本，使用SUBSTRING()函数指定起始位置为第2个字符，结束位置为]字符的位置减2，但执行SQL时触发语法错误，错误出现在POSITION(']', vm.location_path)的逗号处，错误信息为SQL Error [42601]: ERROR: syntax error at or near ","。

原SQL语句：

SELECT
    'UPDATE vm SET dstore_moref = ''' || datastore_inv.moref || ''' WHERE id = ''' || vm.id || ''';'
FROM
    vm
INNER JOIN vapp_vm ON vapp_vm.svm_id = vm.id
INNER JOIN vm_inv ON vm_inv.moref = vm.moref
INNER JOIN datastore_inv ON datastore_inv.vc_display_name =(
        SUBSTRING(
            vm.location_path,
            2,
            POSITION(']',
            vm.location_path) - 2
        )
    )
WHERE
    vm.dstore_moref IS NULL AND vm_inv.is_deleted IS FALSE
GROUP BY datastore_inv.moref, vm.id;

错误详情：

SQL Error [42601]: ERROR: syntax error at or near ","
  Position: 370


Error position: line: 11 pos: 369

该SQL用于vCloud Director，目的是生成dstore_moref为NULL的VM的更新语句。

错误原因 #

vCloud Director底层基于PostgreSQL数据库，PostgreSQL的POSITION()函数语法为POSITION(substring IN string)，不支持用逗号分隔参数。原代码中错误使用逗号分隔']'和vm.location_path，违反语法规范导致报错。

修正后的SQL #

将POSITION(']', vm.location_path)改为POSITION(']' IN vm.location_path)即可解决问题，修正后的完整SQL如下：

SELECT
    'UPDATE vm SET dstore_moref = ''' || datastore_inv.moref || ''' WHERE id = ''' || vm.id || ''';'
FROM
    vm
INNER JOIN vapp_vm ON vapp_vm.svm_id = vm.id
INNER JOIN vm_inv ON vm_inv.moref = vm.moref
INNER JOIN datastore_inv ON datastore_inv.vc_display_name =(
        SUBSTRING(
            vm.location_path,
            2,
            POSITION(']' IN vm.location_path) - 2
        )
    )
WHERE
    vm.dstore_moref IS NULL AND vm_inv.is_deleted IS FALSE
GROUP BY datastore_inv.moref, vm.id;

逻辑验证 #

假设vm.location_path的值为[datastore-01]：

• POSITION(']' IN vm.location_path)返回13（']'处于第13位）
• 减2后得到11，SUBSTRING从第2位开始取11个字符，正好提取出datastore-01，符合预期需求。

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