PostgreSQL 中文社区
- PL/pgSQL 是 PostgreSQL 中的一种存储过程语言，它支持许多常用的函数和操作符。下面列举了一些常用的 PL/pgSQL 函数和操作符：
1. 常用函数：
RAISE：用于在存储过程中抛出异常。
RAISE EXCEPTION 'Error occurred: %', error_message;
IF-ELSE：用于条件判断。
IF condition THEN
-- do something
ELSE
-- do something else
END IF;
FOR LOOP：用于循环操作。
FOR variable_name IN [ REVERSE ] lower_bound..upper_bound LOOP
-- loop body
END LOOP;
- 案例：
CREATE OR REPLACE FUNCTION print_numbers(start_num INT, end_num INT)
RETURNS VOID AS $$
DECLARE
i INT;
BEGIN
FOR i IN start_num..end_num LOOP
RAISE NOTICE 'Number: %', i;
END LOOP;
END;
$$ LANGUAGE plpgsql;
解析：
- 这个函数名为
print_numbers，接受两个参数start_num和end_num，然后使用 FOR 循环打印从start_num到end_num的所有数字。在循环体中，使用RAISE NOTICE打印每个数字的值。
WHILE LOOP：用于执行条件循环。
WHILE condition LOOP
-- loop body
END LOOP;
- 案例
CREATE OR REPLACE FUNCTION countdown(start_num INT)
RETURNS VOID AS $$
DECLARE
num INT := start_num;
BEGIN
WHILE num > 0 LOOP
RAISE NOTICE 'Countdown: %', num;
num := num - 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;
解析：
- 这个函数名为
countdown，接受一个参数start_num，然后使用 WHILE 循环实现倒计时功能。在循环体中，首先打印当前数字的值，然后将数字减一。当数字大于零时，循环继续执行，直到数字减至零为止。
RETURN：用于从函数中返回值。
RETURN value;
SELECT INTO：用于将查询结果赋值给变量。
SELECT column_name INTO variable_name FROM table_name WHERE condition;
EXECUTE：用于执行动态 SQL 语句。
EXECUTE 'SELECT * FROM ' || table_name;
- 案例：
假设有一个存储过程，接收一个表名作为参数，并执行该表的 SELECT 查询，然后返回查询结果。
CREATE OR REPLACE FUNCTION select_from_table(table_name TEXT)
RETURNS TABLE (...) AS $$
DECLARE
query TEXT;
BEGIN
-- 构造动态 SQL 查询语句
query := 'SELECT * FROM ' || table_name;
-- 执行动态 SQL 查询语句
RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;
解析：
- 在这个函数中，首先构造了一个动态 SQL 查询语句，然后使用 EXECUTE 执行该查询语句。最后，使用 RETURN QUERY 返回查询结果。这个函数可以接受任意表名作为参数，并执行相应的查询操作。
||是字符串连接操作符，用于将两个字符串连接起来形成一个新的字符串。RETURNS TABLE (...)表示函数返回一个表类型的结果集。括号中的内容是结果集的列定义，可以包含列名和数据类型。
GET DIAGNOSTICS：用于获取异常信息。
GET DIAGNOSTICS variable_name = condition;
- 案例：
假设有一个存储过程，执行一条 SQL 查询，并获取查询结果的行数，并将行数保存到一个变量中。
CREATE OR REPLACE FUNCTION get_row_count()
RETURNS VOID AS $$
DECLARE
row_count INT;
BEGIN
-- 执行查询语句
EXECUTE 'SELECT COUNT(*) FROM your_table' INTO row_count;
-- 获取查询结果的行数
GET DIAGNOSTICS row_count = ROW_COUNT;
-- 打印行数
RAISE NOTICE 'Row count: %', row_count;
END;
$$ LANGUAGE plpgsql;
解释：
- 在这个函数中，首先执行了一条 SQL 查询语句，然后通过 GET DIAGNOSTICS 获取查询结果的行数，并将行数保存到变量
row_count中。最后，使用RAISE NOTICE打印行数。
2. 常用操作符：
:=：赋值操作符，用于将值赋给变量。
variable_name := value;
IF-THEN-ELSE：条件判断操作符。
IF condition THEN
-- do something
ELSE
-- do something else
END IF;
=：相等比较操作符。
IF variable_name = value THEN
-- do something
END IF;
!= 或 <>：不等于比较操作符。
IF variable_name != value THEN
-- do something
END IF;
<, >, <=, >=：大小比较操作符，用于进行大小比较。
IF variable_name < value THEN
-- do something
END IF;
AND, OR, NOT：逻辑操作符，用于组合条件。
IF condition1 AND condition2 THEN
-- do something
END IF;
IS NULL, IS NOT NULL：判断是否为空值。
IF variable_name IS NULL THEN
-- do something
END IF;
LIKE, ILIKE：模糊匹配操作符，用于模式匹配。
IF variable_name LIKE 'pattern' THEN
-- do something
END IF;
- 案例：
假设有一个存储过程，接收一个参数，并根据参数值判断执行不同的逻辑操作。
CREATE OR REPLACE FUNCTION check_pattern(input_value TEXT)
RETURNS VOID AS $$
BEGIN
IF input_value LIKE 'abc%' THEN
RAISE NOTICE 'Input value starts with "abc"';
ELSE
RAISE NOTICE 'Input value does not start with "abc"';
END IF;
END;
$$ LANGUAGE plpgsql;
解析：
- 在这个函数中，使用了
LIKE操作符来判断输入参数input_value是否以 “abc” 开头。如果是，则打印消息 “Input value starts with “abc””；如果不是，则打印消息 “Input value does not start with “abc””。
