如何向PostgreSQL的COPY命令传递动态变量？

嘿，这个需求我太熟悉了！PostgreSQL的COPY命令本身确实没提供直接传变量的语法，但咱们有好几种实用的方案能实现动态传值，根据你的使用场景选就行：

1. 在psql命令行里用客户端变量替换 #

如果是在psql交互环境或者脚本里使用，直接用psql自带的变量功能就行，记得用\copy（客户端版的COPY）来解析客户端侧的变量：

-- 先定义变量
\set my_variable '202405'
-- 用:变量名引用，字符串变量要加单引号包裹，写成:'变量名'
\copy (SELECT * FROM sales WHERE month = :'my_variable') TO '/tmp/sales_:my_variable.csv' WITH (FORMAT csv, HEADER);

这里的关键点是用:'my_variable'来引用字符串变量，psql会自动帮你替换并处理引号转义，避免语法错误。

2. 在PL/pgSQL函数里动态生成COPY语句 #

如果是要在存储过程或者函数里动态传值，就得用EXECUTE动态拼接SQL语句，一定要用format()函数来处理变量，防止SQL注入：

CREATE OR REPLACE FUNCTION export_sales_by_month(p_month text)
RETURNS void AS $$
DECLARE
    copy_query text;
BEGIN
    -- 用format的%L转义字符串变量，%s处理文件名里的变量
    copy_query := format(
        'COPY (SELECT * FROM sales WHERE month = %L) TO ''/var/postgres/exports/sales_%s.csv'' WITH (FORMAT csv, HEADER)',
        p_month, p_month
    );
    EXECUTE copy_query;
END;
$$ LANGUAGE plpgsql;

-- 调用函数时传入变量
SELECT export_sales_by_month('202405');

%L会自动给变量加上单引号并转义特殊字符，这是保障安全的关键，绝对不要直接拼接变量字符串！

3. 在应用程序中动态构造COPY语句 #

如果是用Python、Java等编程语言调用PostgreSQL，建议用驱动自带的SQL拼接工具来安全处理变量，比如Python的psycopg2：

import psycopg2
from psycopg2 import sql

# 建立连接
conn = psycopg2.connect("dbname=your_db user=your_user")
cur = conn.cursor()

my_variable = "202405"
# 用sql模块安全拼接变量，避免注入风险
copy_stmt = sql.SQL(
    "COPY (SELECT * FROM sales WHERE month = {}) TO STDOUT WITH (FORMAT csv, HEADER)"
).format(sql.Literal(my_variable))

# 将结果写入动态命名的文件
with open(f"sales_{my_variable}.csv", "w") as output_file:
    cur.copy_expert(copy_stmt, output_file)

conn.commit()
cur.close()
conn.close()

这里用sql.Literal()来处理变量，驱动会自动帮你做安全转义，比手动拼接字符串靠谱多了。

额外注意点 #

• 服务器端的COPY命令操作的是PostgreSQL进程有权限访问的服务器文件，而\copy操作的是客户端本地文件，别搞混了！
• 所有动态拼接SQL的场景，都要优先用参数化/转义工具，绝对不能直接把变量拼进SQL字符串里，否则会有严重的SQL注入风险。

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