— 输出结果
ERROR: syntax error at or near “FORM”
LINE 1: SELECT * FORM fgedu_users;
^
SQL state: 42601

— 正确写法
SELECT * FROM fgedu_users;

— 输出结果
id | name | email
—-+——–+——————
1 | fgedu | fgedu@test.com
(1 row)

— 错误示例：字符串引号错误
SELECT * FROM fgedu_users WHERE name = “fgedu”;

— 输出结果
ERROR: column “fgedu” does not exist
LINE 1: SELECT * FROM fgedu_users WHERE name = “fgedu”;
^
SQL state: 42703

— 正确写法：PostgreSQL中字符串使用单引号
SELECT * FROM fgedu_users WHERE name = ‘fgedu’;

— 输出结果
id | name | email
—-+——–+——————
1 | fgedu | fgedu@test.com
(1 row)

— 错误示例：关键字冲突
CREATE TABLE order (id INTEGER, name VARCHAR(100));

— 输出结果
ERROR: syntax error at or near “order”
LINE 1: CREATE TABLE order (id INTEGER, name VARCHAR(100));
^
SQL state: 42601

— 正确写法：使用引号或避免关键字
CREATE TABLE “order” (id INTEGER, name VARCHAR(100));
— 或者使用更好的命名
CREATE TABLE fgedu_orders (id INTEGER, name VARCHAR(100));

— 输出结果
CREATE TABLE

— 输出结果
ERROR: “v_count” is not a known variable
LINE 3: v_count := 10;
^
SQL state: 42601

— 正确写法：先声明变量
CREATE OR REPLACE FUNCTION fgedu_test()
RETURNS INTEGER AS $$
DECLARE
v_count INTEGER;
BEGIN
v_count := 10;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 错误示例：IF语句缺少THEN
CREATE OR REPLACE FUNCTION fgedu_check_value(p_value INTEGER)
RETURNS TEXT AS $$
BEGIN
IF p_value > 0
RETURN ‘positive’;
END IF;
END;
$$ LANGUAGE plpgsql;

— 输出结果
ERROR: syntax error at or near “RETURN”
LINE 4: RETURN ‘positive’;
^
SQL state: 42601

— 正确写法：IF语句需要THEN
CREATE OR REPLACE FUNCTION fgedu_check_value(p_value INTEGER)
RETURNS TEXT AS $$
BEGIN
IF p_value > 0 THEN
RETURN ‘positive’;
END IF;
RETURN ‘non-positive’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 错误示例：分号缺失
CREATE OR REPLACE FUNCTION fgedu_get_count()
RETURNS INTEGER AS $$
DECLARE
v_count INTEGER
BEGIN
SELECT COUNT(*) INTO v_count FROM fgedu_users
RETURN v_count
END
$$ LANGUAGE plpgsql;

— 输出结果
ERROR: syntax error at or near “BEGIN”
LINE 3: BEGIN
^
SQL state: 42601

— 正确写法：语句结束需要分号
CREATE OR REPLACE FUNCTION fgedu_get_count()
RETURNS INTEGER AS $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM fgedu_users;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 输出结果
?column?
———-

(1 row)

— 错误示例：空值比较
SELECT * FROM fgedu_users WHERE name != NULL;

— 输出结果
id | name | email
—-+——+——-
(0 rows)

— 正确写法：使用IS NULL或IS NOT NULL
SELECT * FROM fgedu_users WHERE name IS NOT NULL;

— 输出结果
id | name | email
—-+——–+——————
1 | fgedu | fgedu@test.com
(1 row)

— 使用COALESCE处理空值
SELECT COALESCE(NULL, 0) + 10;

— 输出结果
?column?
———-
10
(1 row)

— PL/pgSQL中处理空值
CREATE OR REPLACE FUNCTION fgedu_safe_concat(
p_str1 TEXT,
p_str2 TEXT
)
RETURNS TEXT
AS $$
BEGIN
RETURN COALESCE(p_str1, ”) || COALESCE(p_str2, ”);
END;
$$ LANGUAGE plpgsql;

— 测试
SELECT fgedu_safe_concat(NULL, ‘world’);

— 输出结果
fgedu_safe_concat
——————-
world
(1 row)

— 输出结果
ERROR: invalid input syntax for type integer: “abc”
SQL state: 22P02

— 正确写法：使用TRY-CATCH或先验证
CREATE OR REPLACE FUNCTION fgedu_safe_to_integer(p_text TEXT)
RETURNS INTEGER
AS $$
BEGIN
RETURN p_text::INTEGER;
EXCEPTION
WHEN INVALID_TEXT_REPRESENTATION THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

— 测试
SELECT fgedu_safe_to_integer(‘123’);
SELECT fgedu_safe_to_integer(‘abc’);

— 输出结果
fgedu_safe_to_integer
———————–
123
(1 row)

fgedu_safe_to_integer
———————–
NULL
(1 row)

— 错误示例：日期格式转换
SELECT ‘2026-13-01’::DATE;

— 输出结果
ERROR: date/time field value out of range: “2026-13-01”
SQL state: 22008

— 正确写法：使用TO_DATE指定格式
SELECT TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’);

— 输出结果
to_date
————
2026-01-01
(1 row)

— 输出结果
ERROR: permission denied for table fgedu_sensitive_data
SQL state: 42501

— 解决方案：授予权限
GRANT SELECT ON fgedu_sensitive_data TO fgedu01;

— 输出结果
GRANT

— 错误示例：函数执行权限
— 以普通用户执行超级用户函数
SELECT fgedu_admin_function();

— 输出结果
ERROR: permission denied for function fgedu_admin_function
SQL state: 42501

— 解决方案：授予函数执行权限
GRANT EXECUTE ON FUNCTION fgedu_admin_function() TO fgedu01;

— 输出结果
GRANT

— 错误示例：序列权限
INSERT INTO fgedu_orders(name) VALUES(‘test’);

— 输出结果
ERROR: permission denied for sequence fgedu_orders_id_seq
SQL state: 42501

— 解决方案：授予序列使用权限
GRANT USAGE, SELECT ON SEQUENCE fgedu_orders_id_seq TO fgedu01;

— 输出结果
GRANT

— 查看用户权限
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.table_privileges
WHERE grantee = ‘fgedu01’;

— 输出结果
grantee | table_schema | table_name | privilege_type
———+————–+———————+—————-
fgedu01 | public | fgedu_sensitive_data| SELECT
fgedu01 | public | fgedu_orders | INSERT
(2 rows)

RETURN v_name;
END;
$$ LANGUAGE plpgsql;

— 测试（假设有多条匹配）
SELECT fgedu_get_user_name(‘%@test.com’);

— 输出结果
ERROR: query returned more than one row
SQL state: 21000

— 正确写法：使用LIMIT或循环
CREATE OR REPLACE FUNCTION fgedu_get_user_name(p_email_pattern TEXT)
RETURNS TEXT
AS $$
DECLARE
v_name TEXT;
BEGIN
SELECT name INTO v_name
FROM fgedu_users
WHERE email LIKE p_email_pattern
LIMIT 1;

RETURN v_name;
END;
$$ LANGUAGE plpgsql;

— 案例2：动态SQL错误
CREATE OR REPLACE FUNCTION fgedu_dynamic_query(p_table_name TEXT)
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
— 错误：不能直接使用变量作为表名
SELECT COUNT(*) INTO v_count FROM p_table_name;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 输出结果
ERROR: relation “p_table_name” does not exist
SQL state: 42P01

— 正确写法：使用EXECUTE执行动态SQL
CREATE OR REPLACE FUNCTION fgedu_dynamic_query(p_table_name TEXT)
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
EXECUTE ‘SELECT COUNT(*) FROM ‘ || p_table_name
INTO v_count;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;

— 测试
SELECT fgedu_dynamic_query(‘fgedu_users’);

— 输出结果
fgedu_dynamic_query
———————
10
(1 row)

— 案例3：异常处理获取详细信息
CREATE OR REPLACE FUNCTION fgedu_insert_with_error_handling(
p_name TEXT,
p_email TEXT
)
RETURNS JSONB
AS $$
DECLARE
v_sqlstate TEXT;
v_sqlerrm TEXT;
v_detail TEXT;
v_hint TEXT;
BEGIN
BEGIN
INSERT INTO fgedu_users(name, email) VALUES(p_name, p_email);
RETURN jsonb_build_object(‘success’, true, ‘message’, ‘插入成功’);

EXCEPTION
WHEN unique_violation THEN
GET STACKED DIAGNOSTICS
v_sqlstate = RETURNED_SQLSTATE,
v_sqlerrm = MESSAGE_TEXT,
v_detail = PG_EXCEPTION_DETAIL,
v_hint = PG_EXCEPTION_HINT;

RETURN jsonb_build_object(
‘success’, false,
‘error_code’, v_sqlstate,
‘error_message’, v_sqlerrm,
‘detail’, v_detail,
‘hint’, v_hint
);

WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
v_sqlstate = RETURNED_SQLSTATE,
v_sqlerrm = MESSAGE_TEXT;

RETURN jsonb_build_object(
‘success’, false,
‘error_code’, v_sqlstate,
‘error_message’, v_sqlerrm
);
END;
END;
$$ LANGUAGE plpgsql;

— 测试唯一约束违反
SELECT fgedu_insert_with_error_handling(‘fgedu’, ‘fgedu@test.com’);

— 输出结果
fgedu_insert_with_error_handling
———————————————————————————————————
{“success”: false, “error_code”: “23505”, “error_message”: “duplicate key value violates unique constraint…”, “detail”: “Key (email)=(fgedu@test.com) already exists.”, “hint”: null}
(1 row)

— 测试
SELECT fgedu_python_divide(10, 0);

— 输出结果
WARNING: 除数为零，返回NULL
fgedu_python_divide
——————–
NULL
(1 row)

— 案例2：数据库查询错误处理
CREATE OR REPLACE FUNCTION fgedu_python_query(p_table_name TEXT)
RETURNS INTEGER
AS $$
try:
query = f”SELECT COUNT(*) FROM {p_table_name}”
result = plpy.execute(query)
return result[0][‘count’]
except Exception as e:
plpy.warning(f”查询失败: {str(e)}”)
return -1
$$ LANGUAGE plpython3u;

— 测试不存在的表
SELECT fgedu_python_query(‘nonexistent_table’);

— 输出结果
WARNING: 查询失败: relation “nonexistent_table” does not exist
fgedu_python_query
——————–
-1
(1 row)

— 案例3：类型转换错误
CREATE OR REPLACE FUNCTION fgedu_python_safe_int(p_value TEXT)
RETURNS INTEGER
AS $$
try:
return int(p_value)
except ValueError:
plpy.notice(f”无法将 ‘{p_value}’ 转换为整数”)
return None
$$ LANGUAGE plpython3u;

— 测试
SELECT fgedu_python_safe_int(‘123’);
SELECT fgedu_python_safe_int(‘abc’);

— 输出结果
NOTICE: 无法将 ‘abc’ 转换为整数
fgedu_python_safe_int
———————–
NULL
(1 row)

— 会话2
BEGIN;
UPDATE fgedu_accounts SET balance = balance – 100 WHERE id = 2;
— 尝试更新会话1锁定的行
UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 1;

— 输出结果
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 12346.
SQL state: 40P01

— 解决方案：设置锁等待超时
SET lock_timeout = ‘5s’;
BEGIN;
UPDATE fgedu_accounts SET balance = balance – 100 WHERE id = 1;
— 如果等待超过5秒，自动取消

— 案例2：序列化失败
— 设置隔离级别为SERIALIZABLE
SET default_transaction_isolation = ‘serializable’;

— 会话1
BEGIN;
SELECT * FROM fgedu_accounts WHERE id = 1;
— 不提交

— 会话2
BEGIN;
UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

— 会话1继续
UPDATE fgedu_accounts SET balance = balance – 100 WHERE id = 1;
COMMIT;

— 输出结果
ERROR: could not serialize access due to concurrent update
SQL state: 40001

— 解决方案：使用重试机制
CREATE OR REPLACE PROCEDURE fgedu_retry_transaction(
p_max_retries INTEGER DEFAULT 3
)
AS $$
DECLARE
v_attempt INTEGER := 0;
BEGIN
WHILE v_attempt < p_max_retries LOOP v_attempt := v_attempt + 1; BEGIN -- 设置隔离级别 SET LOCAL default_transaction_isolation = 'serializable'; -- 执行业务逻辑 UPDATE fgedu_accounts SET balance = balance - 100 WHERE id = 1; UPDATE fgedu_accounts SET balance = balance + 100 WHERE id = 2; COMMIT; RAISE NOTICE '事务成功，尝试次数: %', v_attempt; RETURN; EXCEPTION WHEN serialization_failure THEN ROLLBACK; IF v_attempt >= p_max_retries THEN
RAISE EXCEPTION ‘事务重试%次后仍失败’, p_max_retries;
END IF;
RAISE NOTICE ‘序列化失败，第%次重试…’, v_attempt;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 案例3：约束违反错误
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10,2) CHECK (price > 0),
stock INTEGER DEFAULT 0 CHECK (stock >= 0)
);

— 插入违反约束的数据
INSERT INTO fgedu_products(name, price, stock) VALUES(‘test’, -10, 5);

— 输出结果
ERROR: new row for relation “fgedu_products” violates check constraint “fgedu_products_price_check”
DETAIL: Failing row contains (1, test, -10.00, 5).
SQL state: 23514

— 解决方案：使用存储过程验证
CREATE OR REPLACE PROCEDURE fgedu_insert_product(
p_name VARCHAR(100),
p_price NUMERIC(10,2),
p_stock INTEGER DEFAULT 0
)
AS $$
BEGIN
— 参数验证
IF p_price IS NULL OR p_price <= 0 THEN RAISE EXCEPTION '价格必须大于0'; END IF; IF p_stock IS NULL OR p_stock < 0 THEN RAISE EXCEPTION '库存不能为负数'; END IF; INSERT INTO fgedu_products(name, price, stock) VALUES(p_name, p_price, p_stock); RAISE NOTICE '产品插入成功'; END; $$ LANGUAGE plpgsql; -- 测试 CALL fgedu_insert_product('test', -10, 5); -- 输出结果 ERROR: 价格必须大于0 SQL state: P0001

— 参数验证
IF p_param1 IS NULL THEN
RAISE EXCEPTION ‘参数p_param1不能为空’;
END IF;

— 业务逻辑
BEGIN
— 执行主要逻辑
v_result := jsonb_build_object(
‘param1’, p_param1,
‘param2’, p_param2,
‘status’, ‘success’
);

— 记录成功日志
INSERT INTO fgedu_function_logs(
function_name, params, status, exec_time, create_time
) VALUES (
‘fgedu_template_function’,
jsonb_build_object(‘p_param1’, p_param1, ‘p_param2’, p_param2),
‘success’,
clock_timestamp() – v_start_time,
NOW()
);

RETURN v_result;

EXCEPTION
WHEN unique_violation THEN
v_error_detail := SQLERRM;
RAISE NOTICE ‘唯一约束违反: %’, v_error_detail;
RETURN jsonb_build_object(
‘status’, ‘error’,
‘error_code’, ‘23505’,
‘error_message’, ‘数据已存在’
);

WHEN foreign_key_violation THEN
v_error_detail := SQLERRM;
RAISE NOTICE ‘外键约束违反: %’, v_error_detail;
RETURN jsonb_build_object(
‘status’, ‘error’,
‘error_code’, ‘23503’,
‘error_message’, ‘关联数据不存在’
);

WHEN OTHERS THEN
v_error_detail := SQLERRM;
RAISE WARNING ‘未知错误: %’, v_error_detail;

— 记录错误日志
INSERT INTO fgedu_function_logs(
function_name, params, status, error_message, create_time
) VALUES (
‘fgedu_template_function’,
jsonb_build_object(‘p_param1’, p_param1, ‘p_param2’, p_param2),
‘error’,
v_error_detail,
NOW()
);

RETURN jsonb_build_object(
‘status’, ‘error’,
‘error_code’, SQLSTATE,
‘error_message’, v_error_detail
);
END;
END;
$$ LANGUAGE plpgsql;

— 检查数据
SELECT COUNT(*) INTO v_count FROM fgedu_orders WHERE customer_id = p_id;
RAISE NOTICE ‘找到 % 条订单记录’, v_count;

— 处理数据
FOR v_data IN SELECT * FROM fgedu_orders WHERE customer_id = p_id
LOOP
RAISE NOTICE ‘处理订单: %, 金额: %’, v_data.id, v_data.total_amount;
END LOOP;

— 返回结果
v_result := jsonb_build_object(
‘customer_id’, p_id,
‘order_count’, v_count,
‘status’, ‘completed’
);

RAISE NOTICE ‘处理完成’;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;

— 执行调试
SELECT fgedu_debug_function(1001);

— 输出结果
NOTICE: 开始处理，参数: 1001
NOTICE: 找到 5 条订单记录
NOTICE: 处理订单: 1, 金额: 1250.00
NOTICE: 处理订单: 2, 金额: 3580.00
NOTICE: 处理完成
fgedu_debug_function
———————————————————
{“customer_id”: 1001, “order_count”: 5, “status”: “completed”}
(1 row)