14.postgresql在sql中使用变量的方法和注意事项

postgresql在sql中使用变量的方法和注意事项

目录

• postgresql在sql中使用变量的方法和注意事项
  • 一、PostgreSQL 中使用变量的核心方法
    • 场景 1：psql 客户端（交互式 / 脚本执行）
      • 1.1 基础用法
      • 1.2 进阶：psql 脚本传参（批量执行）
    • 场景 2：函数 / 存储过程（PL/pgSQL）
      • 2.1 基础用法（函数内定义变量）
      • 2.2 存储过程（支持事务）
    • 场景 3：应用程序调用（如 Python/Java）
      • 3.1 Python 示例（psycopg2）
  • 二、核心注意事项（避坑指南）
    • 1. 变量替换的引号规则（psql 场景）
    • 2. 变量作用域
    • 3. 类型匹配
    • 4. 防 SQL 注入（关键）
    • 5. 动态 SQL 中的变量使用
    • 6. psql 命令的特殊规则
  • 总结
    • 1.核心用法：
    • 2.关键注意事项：
    • 3.避坑重点：

一、PostgreSQL 中使用变量的核心方法

PostgreSQL 本身没有 “全局 SQL 变量” 的原生支持，变量使用依赖使用场景，以下是最常用的 3 种方式：

场景 1：psql 客户端（交互式 / 脚本执行）

这是你之前用到的场景，psql 提供\set/\unset命令定义会话级变量，适合手动执行 SQL 或编写 psql 脚本。

1.1 基础用法

-- 1. 定义变量（数值型，无引号；psql命令结尾不加;）
\set var_salary 10000
-- 2. 定义字符串/日期型变量（单引号包裹值）
\set var_dept '研发部'
\set var_hiredate '2026-01-01'

-- 3. 引用变量（关键：数值型无引号，字符串型用双引号包裹）
-- 数值型变量引用
SELECT name, salary FROM employees WHERE salary > :var_salary;
-- 字符串型变量引用（双引号让psql先替换变量，再解析为字符串）
SELECT name, department FROM employees WHERE department = :"var_dept";
-- 日期型变量引用（同字符串）
SELECT name FROM employees WHERE sys_period @> :"var_hiredate"::timestamptz;

-- 4. 查看变量值
\echo :var_salary  -- 输出10000
\echo :"var_dept"  -- 输出研发部

-- 5. 清空变量
\unset var_salary

1.2 进阶：psql 脚本传参（批量执行）

编写.sql脚本（如query_emp.sql），通过\set接收外部传参：

-- query_emp.sql
\set var_salary :1  -- 接收第1个外部参数
\set var_dept :2    -- 接收第2个外部参数
SELECT name, salary, department 
FROM employees 
WHERE salary > :var_salary AND department = :"var_dept";

执行脚本并传参：

psql -U username -d test -f query_emp.sql -v 1=10000 -v 2='研发部'
# 或简化写法（按位置传参）
psql -U username -d test -f query_emp.sql --args 10000 '研发部'

场景 2：函数 / 存储过程（PL/pgSQL）

在自定义函数 / 存储过程中使用变量，这是 PostgreSQL 中最灵活的变量使用方式，支持逻辑控制（if/loop）。

2.1 基础用法（函数内定义变量）

-- 创建带变量的函数
CREATE OR REPLACE FUNCTION get_emp_by_salary(min_salary numeric)
RETURNS TABLE(name text, salary numeric) AS $$
DECLARE
    -- 定义局部变量（可选：指定类型+默认值）
    max_salary numeric := 50000;
    dept_name text := '研发部';
BEGIN
    -- 变量赋值（两种方式）
    max_salary := min_salary * 2;
    SELECT department INTO dept_name FROM employees LIMIT 1;  -- 从查询结果赋值

    -- 使用变量查询
    RETURN QUERY
    SELECT e.name, e.salary 
    FROM employees e 
    WHERE e.salary BETWEEN min_salary AND max_salary 
      AND e.department = dept_name;
END;
$$ LANGUAGE plpgsql;

-- 调用函数（传入参数）
SELECT * FROM get_emp_by_salary(10000);

2.2 存储过程（支持事务）

CREATE OR REPLACE PROCEDURE update_emp_salary(emp_name text, new_salary numeric)
LANGUAGE plpgsql
AS $$
DECLARE
    old_salary numeric;
BEGIN
    -- 获取旧薪资
    SELECT salary INTO old_salary FROM employees WHERE name = emp_name;
    -- 更新薪资
    UPDATE employees SET salary = new_salary WHERE name = emp_name;
    -- 输出日志（变量拼接）
    RAISE NOTICE '员工%：旧薪资%，新薪资%', emp_name, old_salary, new_salary;
END;
$$;

-- 调用存储过程
CALL update_emp_salary('张三', 22000);

场景 3：应用程序调用（如 Python/Java）

通过应用程序连接 PostgreSQL 时，禁止直接拼接变量（防 SQL 注入），需使用「参数化查询」。

3.1 Python 示例（psycopg2）

import psycopg2

# 连接数据库
conn = psycopg2.connect(database="test", user="username", password="xxx", host="127.0.0.1")
cur = conn.cursor()

# 定义变量
min_salary = 10000
dept_name = "研发部"

# 参数化查询（%s是占位符，自动处理类型和引号）
cur.execute("""
    SELECT name, salary FROM employees 
    WHERE salary > %s AND department = %s
""", (min_salary, dept_name))  # 变量以元组传入

# 获取结果
for row in cur.fetchall():
    print(f"姓名：{row[0]}，薪资：{row[1]}")

conn.close()

二、核心注意事项（避坑指南）

1. 变量替换的引号规则（psql 场景）

这是最容易踩坑的点，核心原则：

• 数值型变量：引用时无引号（WHERE salary > :var_salary）；
• 字符串 / 日期型变量：引用时双引号包裹（WHERE department = :"var_dept"）；
• 错误示例：WHERE salary > ':var_salary' → 单引号导致变量被识别为字符串:var_salary，触发类型错误。

2. 变量作用域

• psql 的\set变量：会话级，仅当前 psql 连接有效，断开连接后失效；
• 函数 / 存储过程的变量：局部作用域，仅函数内部有效，外部无法访问；
• 若需跨会话共享 “变量”，可创建专门的配置表（如sys_config）存储键值对。

3. 类型匹配

• 变量类型必须与字段 / 表达式类型一致，否则触发类型转换错误：
  示例：\set var_salary '10000a' → 引用时WHERE salary > :var_salary会报错（字符串转数值失败）；
• 建议：定义变量时显式指定类型（如函数内DECLARE var numeric;）。

4. 防 SQL 注入（关键）

• 绝对禁止：直接拼接变量到 SQL 语句（如 Python 中sql = f"SELECT * FROM emp WHERE name = '{emp_name}'"）；
• 正确做法：
  • psql 脚本：用:变量或%s占位符；
  • 应用程序：使用参数化查询（如 psycopg2 的%s、JDBC 的?）；
  • 函数 / 存储过程：通过参数传入变量，而非动态拼接 SQL。

5. 动态 SQL 中的变量使用

若需动态拼接 SQL（如动态表名 / 字段名），必须用EXECUTE ... USING传递变量，而非直接拼接：

-- 错误：直接拼接变量（有注入风险）
EXECUTE 'SELECT * FROM ' || table_name || ' WHERE salary > ' || min_salary;

-- 正确：USING传递变量
EXECUTE 'SELECT * FROM ' || quote_ident(table_name) || ' WHERE salary > $1'
USING min_salary;  -- $1对应USING后的第一个变量

6. psql 命令的特殊规则

• \set命令结尾不要加分号（\set var 1000; → 变量值会变成1000;）；
• 变量名区分大小写（\set Var 100 和 \set var 200 是两个不同变量）；
• 可通过\set AUTOCOMMIT off关闭自动提交，配合变量实现事务控制。

总结

1.核心用法：

• psql 客户端：\set定义变量，数值无引号、字符串用双引号引用；
• 函数 / 存储过程：PL/pgSQL 的DECLARE定义变量，支持逻辑控制；
• 应用程序：参数化查询（防注入），避免直接拼接变量。

2.关键注意事项：

• 引号规则：数值无引号、字符串双引号；
• 类型匹配：变量类型与字段一致；
• 安全：动态 SQL 用EXECUTE ... USING，禁止拼接变量防注入。

3.避坑重点：

• psql 中变量被单引号包裹会导致替换失败，这是新手最常见的错误。