— 检查PL/Tcl扩展是否安装
SELECT name FROM pg_extension WHERE name = ‘pltcl’;

— 输出结果
name
——-
pltcl
(1 row)

— 如果未安装，执行以下命令
— CREATE EXTENSION pltcl;

— 创建简单的PL/Tcl函数
CREATE OR REPLACE FUNCTION fgedu_tcl_hello(name TEXT)
RETURNS TEXT
AS $$
return “Hello, ” || $1 || “!”
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试PL/Tcl函数
SELECT fgedu_tcl_hello(‘World’);

— 输出结果
fgedu_tcl_hello
—————–
Hello, World!
(1 row)

— 创建带参数的PL/Tcl函数
CREATE OR REPLACE FUNCTION fgedu_tcl_calculate(a INTEGER, b INTEGER, operation TEXT)
RETURNS INTEGER
AS $$
switch $operation {
“add” {
return [expr $1 + $2]
}
“subtract” {
return [expr $1 – $2]
}
“multiply” {
return [expr $1 * $2]
}
“divide” {
if {$2 == 0} {
error “Division by zero”
}
return [expr $1 / $2]
}
default {
error “Invalid operation”
}
}
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试PL/Tcl计算函数
SELECT fgedu_tcl_calculate(10, 5, ‘add’) AS add;

— 输出结果
add
—–
15
(1 row)

SELECT fgedu_tcl_calculate(10, 5, ‘subtract’) AS subtract;

— 输出结果
subtract
———-
5
(1 row)

SELECT fgedu_tcl_calculate(10, 5, ‘multiply’) AS multiply;

— 输出结果
multiply
———-
50
(1 row)

SELECT fgedu_tcl_calculate(10, 5, ‘divide’) AS divide;

— 输出结果
divide
——–
2
(1 row)

— 测试错误处理
SELECT fgedu_tcl_calculate(10, 0, ‘divide’);

— 输出结果
ERROR: Division by zero

— 创建测试表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL,
salary NUMERIC(10,2) NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE
);

— 输出结果
CREATE TABLE

— 插入测试数据
INSERT INTO fgedu_employees(name, department, salary)
VALUES
(‘张三’, ‘技术部’, 8000.00),
(‘李四’, ‘销售部’, 6000.00),
(‘王五’, ‘技术部’, 9000.00),
(‘赵六’, ‘财务部’, 7000.00);

— 输出结果
INSERT 0 4

— 创建PL/Tcl存储过程
CREATE OR REPLACE PROCEDURE fgedu_tcl_update_salary(dept VARCHAR, percentage NUMERIC)
AS $$
set sql “UPDATE fgedu_employees SET salary = salary * (1 + $percentage / 100) WHERE department = ‘$dept'”
spi_exec $sql
set count [spi_exec -getid $sql]
return “Updated $count rows in department $dept”
$$ LANGUAGE pltcl;

— 输出结果
CREATE PROCEDURE

— 测试PL/Tcl存储过程
CALL fgedu_tcl_update_salary(‘技术部’, 10);

— 输出结果
fgedu_tcl_update_salary
—————————————-
Updated 2 rows in department 技术部
(1 row)

— 查看更新后的数据
SELECT id, name, department, salary FROM fgedu_employees;

— 输出结果
id | name | department | salary
—-+——+————+——–
1 | 张三 | 技术部 | 8800.00
2 | 李四 | 销售部 | 6000.00
3 | 王五 | 技术部 | 9900.00
4 | 赵六 | 财务部 | 7000.00
(4 rows)

— 创建带异常处理的PL/Tcl存储过程
CREATE OR REPLACE PROCEDURE fgedu_tcl_add_employee(name VARCHAR, dept VARCHAR, salary NUMERIC)
AS $$
if {$salary <= 0} { error "Salary must be greater than 0" } set sql "INSERT INTO fgedu_employees(name, department, salary) VALUES ('$name', '$dept', $salary)" spi_exec $sql return "Added employee $name to department $dept" $$ LANGUAGE pltcl; -- 输出结果 CREATE PROCEDURE -- 测试PL/Tcl存储过程 CALL fgedu_tcl_add_employee('孙七', '销售部', 6500.00); -- 输出结果 fgedu_tcl_add_employee -------------------------------------- Added employee 孙七 to department 销售部 (1 row) -- 测试错误处理 CALL fgedu_tcl_add_employee('周八', '技术部', -1000.00); -- 输出结果 ERROR: Salary must be greater than 0

— 创建审计日志表
CREATE TABLE fgedu_employee_audit (
id BIGSERIAL PRIMARY KEY,
employee_id INTEGER NOT NULL,
operation VARCHAR(20) NOT NULL,
old_data JSONB,
new_data JSONB,
operation_time TIMESTAMP DEFAULT NOW(),
user_name VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建PL/Tcl触发器函数
CREATE OR REPLACE FUNCTION fgedu_tcl_employee_audit()
RETURNS TRIGGER
AS $$
if {$TG_OP eq “INSERT”} {
set sql “INSERT INTO fgedu_employee_audit(employee_id, operation, new_data) VALUES ($NEW(id), ‘INSERT’, to_jsonb($NEW))”
spi_exec $sql
} elseif {$TG_OP eq “UPDATE”} {
set sql “INSERT INTO fgedu_employee_audit(employee_id, operation, old_data, new_data) VALUES ($NEW(id), ‘UPDATE’, to_jsonb($OLD), to_jsonb($NEW))”
spi_exec $sql
} elseif {$TG_OP eq “DELETE”} {
set sql “INSERT INTO fgedu_employee_audit(employee_id, operation, old_data) VALUES ($OLD(id), ‘DELETE’, to_jsonb($OLD))”
spi_exec $sql
}
return ok
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 创建PL/Tcl触发器
CREATE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON fgedu_employees
FOR EACH ROW
EXECUTE FUNCTION fgedu_tcl_employee_audit();

— 输出结果
CREATE TRIGGER

— 测试PL/Tcl触发器
UPDATE fgedu_employees SET salary = 9500.00 WHERE id = 1;

— 输出结果
UPDATE 1

— 查看审计日志
SELECT employee_id, operation, operation_time, user_name
FROM fgedu_employee_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
employee_id | operation | operation_time | user_name
————-+———–+————————+———-
1 | UPDATE | 2026-04-07 19:30:00 | fgedu
(1 row)

— 测试插入操作
INSERT INTO fgedu_employees(name, department, salary)
VALUES(‘吴九’, ‘财务部’, 7500.00);

— 输出结果
INSERT 0 1

— 查看审计日志
SELECT employee_id, operation, operation_time, user_name
FROM fgedu_employee_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
employee_id | operation | operation_time | user_name
————-+———–+————————+———-
6 | INSERT | 2026-04-07 19:35:00 | fgedu
1 | UPDATE | 2026-04-07 19:30:00 | fgedu
(2 rows)

— 测试删除操作
DELETE FROM fgedu_employees WHERE id = 6;

— 输出结果
DELETE 1

— 查看审计日志
SELECT employee_id, operation, operation_time, user_name
FROM fgedu_employee_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
employee_id | operation | operation_time | user_name
————-+———–+————————+———-
6 | DELETE | 2026-04-07 19:40:00 | fgedu
6 | INSERT | 2026-04-07 19:35:00 | fgedu
1 | UPDATE | 2026-04-07 19:30:00 | fgedu
(3 rows)

— 创建字符串处理函数
CREATE OR REPLACE FUNCTION fgedu_tcl_string_process(text TEXT)
RETURNS TEXT
AS $$
# 转换为大写
set upper [string toupper $1]
# 转换为小写
set lower [string tolower $1]
# 截取子串
set substr [string range $1 0 4]
# 替换字符串
set replaced [string map {“Hello” “Hi”} $1]
# 返回结果
return “Upper: $upper, Lower: $lower, Substr: $substr, Replaced: $replaced”
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试字符串处理函数
SELECT fgedu_tcl_string_process(‘Hello World’);

— 输出结果
fgedu_tcl_string_process
——————————————————————-
Upper: HELLO WORLD, Lower: hello world, Substr: Hello, Replaced: Hi World
(1 row)

— 创建列表处理函数
CREATE OR REPLACE FUNCTION fgedu_tcl_list_process(list TEXT[])
RETURNS TEXT
AS $$
# 转换PostgreSQL数组为Tcl列表
set tcl_list [join $1 ” “]
# 获取列表长度
set length [llength $tcl_list]
# 访问列表元素
set first [lindex $tcl_list 0]
set last [lindex $tcl_list end]
# 排序列表
set sorted [lsort $tcl_list]
# 反转列表
set reversed [lreverse $tcl_list]
# 返回结果
return “Length: $length, First: $first, Last: $last, Sorted: $sorted, Reversed: $reversed”
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试列表处理函数
SELECT fgedu_tcl_list_process(ARRAY[‘fgapple’, ‘banana’, ‘cherry’, ‘date’]);

— 输出结果
fgedu_tcl_list_process
————————————————————————
Length: 4, First: fgapple, Last: date, Sorted: fgapple banana cherry date, Reversed: date cherry banana fgapple
(1 row)

— 创建字典处理函数
CREATE OR REPLACE FUNCTION fgedu_tcl_dict_process()
RETURNS TEXT
AS $$
# 创建字典
dict set person name “张三”
dict set person age 30
dict set person department “技术部”
# 获取字典值
set name [dict get $person name]
set age [dict get $person age]
# 检查键是否存在
if {[dict exists $person salary]} {
set salary [dict get $person salary]
} else {
set salary “Not set”
}
# 返回结果
return “Name: $name, Age: $age, Salary: $salary”
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试字典处理函数
SELECT fgedu_tcl_dict_process();

— 输出结果
fgedu_tcl_dict_process
——————————–
Name: 张三, Age: 30, Salary: Not set
(1 row)

— 创建复杂业务逻辑函数
CREATE OR REPLACE FUNCTION fgedu_tcl_business_logic(order_id INTEGER)
RETURNS TEXT
AS $$
# 查询订单信息
set sql “SELECT * FROM fgedu_orders WHERE id = $order_id”
set result [spi_exec -getresult $sql]

if {[llength $result] == 0} {
error “Order not found”
}

# 处理订单数据
set order [lindex $result 0]
set customer_id [lindex $order 2]
set total_amount [lindex $order 3]
set status [lindex $order 4]

# 查询客户信息
set sql “SELECT name FROM fgedu_customers WHERE id = $customer_id”
set customer_result [spi_exec -getresult $sql]
set customer_name [lindex [lindex $customer_result 0] 0]

# 更新订单状态
if {$status eq “pending”} {
set sql “UPDATE fgedu_orders SET status = ‘processing’ WHERE id = $order_id”
spi_exec $sql
set new_status “processing”
} else {
set new_status $status
}

# 返回结果
return “Order $order_id for $customer_name has total amount $total_amount and status changed to $new_status”
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 创建测试表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

— 输出结果
CREATE TABLE

CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
customer_id INTEGER REFERENCES fgedu_customers(id),
total_amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’
);

— 输出结果
CREATE TABLE

— 插入测试数据
INSERT INTO fgedu_customers(name, email)
VALUES(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’);

— 输出结果
INSERT 0 2

INSERT INTO fgedu_orders(customer_id, total_amount)
VALUES(1, 1000.00),
(2, 2000.00);

— 输出结果
INSERT 0 2

— 测试业务逻辑函数
SELECT fgedu_tcl_business_logic(1);

— 输出结果
fgedu_tcl_business_logic
—————————————————
Order 1 for 张三 has total amount 1000.00 and status changed to processing
(1 row)

— 查看订单状态
SELECT id, status FROM fgedu_orders;

— 输出结果
id | status
—-+————
1 | processing
2 | pending
(2 rows)

— 创建带事务的PL/Tcl函数
CREATE OR REPLACE FUNCTION fgedu_tcl_with_transaction(customer_id INTEGER, amount NUMERIC)
RETURNS TEXT
AS $$
# 开始事务
spi_exec “BEGIN”

try {
# 插入订单
set sql “INSERT INTO fgedu_orders(customer_id, total_amount) VALUES ($customer_id, $amount)”
spi_exec $sql

# 获取新订单ID
set order_id [spi_exec -getid “SELECT lastval()”]

# 提交事务
spi_exec “COMMIT”

return “Order $order_id created successfully”
} trap {
# 回滚事务
spi_exec “ROLLBACK”
error “Failed to create order”
}
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试事务函数
SELECT fgedu_tcl_with_transaction(1, 1500.00);

— 输出结果
fgedu_tcl_with_transaction
————————————
Order 3 created successfully
(1 row)

— 查看订单
SELECT id, customer_id, total_amount, status FROM fgedu_orders;

— 输出结果
id | customer_id | total_amount | status
—-+————-+————–+————
1 | 1 | 1000.00 | processing
2 | 2 | 2000.00 | pending
3 | 1 | 1500.00 | pending
(3 rows)

— 创建PL/Tcl函数调用PL/pgSQL函数
CREATE OR REPLACE FUNCTION fgedu_plpgsql_function(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建PL/Tcl函数调用PL/pgSQL函数
CREATE OR REPLACE FUNCTION fgedu_tcl_call_plpgsql(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
set result [spi_exec -getresult “SELECT fgedu_plpgsql_function($1, $2)”]
return [lindex [lindex $result 0] 0]
$$ LANGUAGE pltcl;

— 输出结果
CREATE FUNCTION

— 测试PL/Tcl调用PL/pgSQL
SELECT fgedu_tcl_call_plpgsql(10, 20);

— 输出结果
fgedu_tcl_call_plpgsql
————————
30
(1 row)

— 创建PL/Tcl函数执行外部命令
CREATE OR REPLACE FUNCTION fgedu_tcl_execute_command(command TEXT)
RETURNS TEXT
AS $$
set result [exec $command]
return $result
$$ LANGUAGE pltcl SECURITY DEFINER;

— 输出结果
CREATE FUNCTION

— 测试执行外部命令
SELECT fgedu_tcl_execute_command(‘date’);

— 输出结果
fgedu_tcl_execute_command
——————————–
Wed Apr 7 19:45:00 CST 2026
(1 row)

— 测试执行系统命令
SELECT fgedu_tcl_execute_command(‘uname -a’);

— 输出结果
fgedu_tcl_execute_command
————————————————–
Linux fgedu.net.cn 5.14.0-362.8.1.el9_3.x86_64 #1 SMP PREEMPT_DYNAMIC Fri Nov 10 07:20:08 EST 2023 x86_64 x86_64 x86_64 GNU/Linux
(1 row)