PostgreSQL如何不修改原SQL脚本动态替换参数执行脚本

PostgreSQL本身和官方客户端psql提供了3种完全不用改动原脚本核心逻辑的实现方式，按需选用即可：

方法1：用psql客户端变量调用外部SQL脚本（最常用，适合脚本存为本地文件的场景） #

你可以把原SQL脚本里的固定姓名值替换成psql变量，不需要改其他逻辑，调用的时候传参就行：

• 首先修改原脚本里的where条件为：where name = :'query_name' （这是psql的变量占位符语法，不是SQL语法，不影响SQL本身的结构兼容性）
  调整后的完整脚本存为user_query.sql：

  select id,
         name,
         address,
         age,
         sex
  from table1
  left join table2 <condition>
  left join table3 <condition>
  where name = :'query_name';
  

• 调用的时候直接通过psql的-v参数传值就行，完全不用改脚本内容：

  # 查询Harry Potter
  psql -d your_db_name -v query_name="Harry Potter" -f user_query.sql
  # 查询Lebron James
  psql -d your_db_name -v query_name="Lebron James" -f user_query.sql
  

注意：:'变量名'的写法会自动给字符串加单引号，避免SQL注入问题，不要直接写:query_name不加引号

方法2：用PostgreSQL预处理语句（适合在数据库会话内动态执行，不用改原脚本结构） #

如果你的SQL是在数据库会话里运行，不想存成文件，直接用预处理语句封装就行：

-- 预处理定义，只需要执行一次，会话内有效
PREPARE user_query(text) AS
select id,
       name,
       address,
       age,
       sex
from table1
left join table2 <condition>
left join table3 <condition>
where name = $1;

-- 执行的时候直接传参数就行，不用改原查询逻辑
-- 查询Harry Potter
EXECUTE user_query('Harry Potter');
-- 查询Lebron James
EXECUTE user_query('Lebron James');

用完可以用DEALLOCATE user_query;清理预处理语句。

方法3：封装成SQL函数（适合长期反复调用，原查询逻辑完全固定不用改） #

如果这个查询是高频使用的，可以直接封装成函数，后续调用只传参数：

CREATE OR REPLACE FUNCTION get_user_by_name(query_name text)
RETURNS TABLE (
    id int,
    name text,
    address text,
    age int,
    sex text
) AS $$
select id,
       name,
       address,
       age,
       sex
from table1
left join table2 <condition>
left join table3 <condition>
where name = query_name;
$$ LANGUAGE sql STABLE;

后续调用直接传参：

-- 查询Harry Potter
SELECT * FROM get_user_by_name('Harry Potter');
-- 查询Lebron James
SELECT * FROM get_user_by_name('Lebron James');

这个方法的好处是函数存在数据库里，所有连接都能调用，不需要每次都写原查询逻辑。

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