在PSQL脚本中无法访问Bash传入的XML文件名变量问题

我经常遇到用户在Bash和PostgreSQL脚本之间传递变量时踩坑，你的场景（遍历XML文件并传递文件名给SQL加载数据）非常典型，下面给你几个最实用的解决方案，按推荐程度排序：

方法1：使用psql的-v参数传递变量（最推荐） #

这是PostgreSQL官方推荐的安全方式，能完美处理带空格、特殊字符的文件名，而且不需要修改数据库配置。

步骤1：修改你的Bash脚本 #

在遍历XML文件时，用psql -v把文件名作为变量传递给SQL脚本：

#!/bin/bash
# 替换成你的数据库名称
DB_NAME="your_database"

# 遍历当前目录下所有.xml文件
for xml_file in *.xml; do
  echo "正在处理文件: $xml_file"
  # 使用-v参数传递变量xml_file_path给SQL脚本
  psql -d "$DB_NAME" -v xml_file_path="$xml_file" -f hmdb.sql
done

步骤2：在hmdb.sql中引用变量 #

在SQL脚本里，用:变量名的格式引用psql传递过来的变量：

-- 假设你有一个存储XML数据的表，比如：
-- CREATE TABLE xml_data (id SERIAL PRIMARY KEY, filename TEXT, content XML);

-- 插入XML数据到表中
INSERT INTO xml_data (filename, content)
VALUES (
  :xml_file_path,  -- 直接引用变量作为文件名
  xmlparse(document pg_read_file(:xml_file_path))  -- 读取并解析XML文件
);

如果是用\copy元命令加载数据（注意\copy是psql命令，不是SQL命令），需要用:'变量名'的格式（加单引号）：

-- 用\copy加载XML到表（假设表结构匹配XML格式）
\copy your_target_table FROM :'xml_file_path' WITH (FORMAT XML);

方法2：使用psql的位置参数传递 #

如果你习惯用位置参数，也可以这样做：

Bash脚本调用 #

psql -d "$DB_NAME" -f hmdb.sql -- "$xml_file"

这里的--是用来分隔psql参数和你的自定义参数的。

SQL脚本引用 #

在SQL里用:1表示第一个位置参数：

INSERT INTO xml_data (filename, content)
VALUES (
  :1,
  xmlparse(document pg_read_file(:1))
);

同样，\copy里要用:'1'。

方法3：通过环境变量传递（不推荐，需配置） #

这种方法需要修改PostgreSQL配置，不如前两种方便，但如果有特殊需求可以用：

Bash脚本中导出环境变量 #

export XML_FILE="$xml_file"
psql -d "$DB_NAME" -f hmdb.sql

修改PostgreSQL配置 #

在postgresql.conf中添加：

allowed_system_settings = 'XML_FILE'

然后重启PostgreSQL服务。

SQL脚本中读取环境变量 #

INSERT INTO xml_data (filename, content)
VALUES (
  current_setting('env.XML_FILE'),
  xmlparse(document pg_read_file(current_setting('env.XML_FILE')))
);

为什么你之前的方法可能失败？ #

• 你可能尝试直接在Bash里拼接SQL字符串（比如psql -c "INSERT ... $xml_file"），这种方式会遇到文件名含空格/特殊字符的问题，还存在SQL注入风险。
• SQL脚本运行在psql进程中，无法直接访问Bash的本地变量，必须通过psql提供的参数传递机制或者环境变量（需配置）来传递。

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