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存储过程，实现业务逻辑的封装与复用。