Debian中PostgreSQL存储过程编写指南

Debian中PostgreSQL存储过程编写指南

一、环境准备

1. 安装PostgreSQL

   sudo apt update  
   sudo apt install postgresql postgresql-contrib  
   

2. 启动服务并设置开机自启

   sudo systemctl start postgresql  
   sudo systemctl enable postgresql  
   

3. 创建数据库和用户（可选）

   sudo -u postgres createdb mydb  
   sudo -u postgres createuser -P myuser  # 按提示设置密码  
   

二、存储过程基础编写

1. 使用PL/pgSQL创建存储过程

   • 无参数存储过程（示例：插入数据）

     CREATE OR REPLACE PROCEDURE insert_sample_data()  
     LANGUAGE plpgsql  
     AS $$  
     BEGIN  
       INSERT INTO sample_table (name, value) VALUES ('Test', 100);  
     END;  
     $$;  
     

     调用：CALL insert_sample_data();

   • 带输入参数的存储过程（示例：计算两数之和）

     CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)  
     RETURNS INTEGER  
     LANGUAGE plpgsql  
     AS $$  
     BEGIN  
       RETURN a + b;  
     END;  
     $$;  
     

     调用：SELECT add_numbers(5, 3);

   • 带输出参数的存储过程（示例：返回最大值）

     CREATE OR REPLACE FUNCTION get_max_value()  
     RETURNS INTEGER  
     LANGUAGE plpgsql  
     AS $$  
     DECLARE  
       max_val INTEGER;  
     BEGIN  
       SELECT MAX(value) INTO max_val FROM sample_table;  
       RETURN max_val;  
     END;  
     $$;  
     

     调用：SELECT get_max_value();

2. 控制流与异常处理

   CREATE OR REPLACE PROCEDURE update_with_check(id INTEGER, new_name TEXT)  
   LANGUAGE plpgsql  
   AS $$  
   BEGIN  
     IF NOT EXISTS (SELECT 1 FROM sample_table WHERE id = id) THEN  
       RAISE EXCEPTION 'ID % not found', id;  
     END IF;  
     UPDATE sample_table SET name = new_name WHERE id = id;  
   EXCEPTION  
     WHEN OTHERS THEN  
       RAISE NOTICE 'Error: %', SQLERRM;  
   END;  
   $$;  
   

   调用：CALL update_with_check(1, 'NewName');

三、高级特性

1. 事务管理

   CREATE OR REPLACE PROCEDURE transfer_funds(from_id INTEGER, to_id INTEGER, amount NUMERIC)  
   LANGUAGE plpgsql  
   AS $$  
   BEGIN  
     BEGIN  
       UPDATE accounts SET balance = balance - amount WHERE id = from_id;  
       UPDATE accounts SET balance = balance + amount WHERE id = to_id;  
       COMMIT;  
     EXCEPTION  
       WHEN OTHERS THEN  
         ROLLBACK;  
         RAISE NOTICE 'Transfer failed';  
     END;  
   $$;  
   

2. 动态SQL

   CREATE OR REPLACE FUNCTION dynamic_query(table_name TEXT)  
   RETURNS SETOF RECORD  
   LANGUAGE plpgsql  
   AS $$  
   DECLARE  
     query TEXT;  
   BEGIN  
     query := 'SELECT * FROM ' || table_name;  
     RETURN QUERY EXECUTE query;  
   END;  
   $$;  
   

   调用：SELECT * FROM dynamic_query('users') AS (id INT, name TEXT);

四、注意事项

• 权限：确保用户有CREATE权限，生产环境需谨慎操作。
• 调试：使用RAISE NOTICE输出调试信息。
• 性能：避免在存储过程中执行复杂计算，优先在应用层处理。
• 版本兼容：PostgreSQL 11+支持CREATE PROCEDURE语法，旧版本需用CREATE FUNCTION模拟。

五、参考资源

• 官方文档：PostgreSQL存储过程指南
• Debian PostgreSQL安装指南：Debian Wiki

通过以上步骤，可在Debian环境中高效编写和管理PostgreSQL存储过程，实现业务逻辑的封装与复用。