PostgreSQL-常用函数和操作符

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 打印行数。

CURSOR FOR 游标用法(了解)

注意，游标的命名必须唯一，且与其他变量和对象的名称不冲突。

• 游标声明：

DECLARE
    cursor_name CURSOR FOR
        SELECT column1, column2, ... FROM table_name WHERE condition;

在声明游标时，您指定要使用的查询，并将其分配给一个游标变量。

• 游标打开：

OPEN cursor_name;

打开游标后，它会开始执行与其关联的查询，并准备好从结果集中提取数据。

• 循环提取数据：

LOOP
    FETCH cursor_name INTO variable1, variable2, ...;
    -- 在这里对提取出的数据进行处理
    EXIT WHEN NOT FOUND;
END LOOP;

在循环中，您可以使用FETCH语句从游标中提取数据，并将其存储在指定的变量中。FETCH命令会将游标定位到结果集中的下一行数据。如果没有更多的数据可提取，则FETCH将引发一个NOT FOUND异常。您可以使用EXIT WHEN NOT FOUND语句来退出循环。

• 关闭游标：

CLOSE cursor_name;

在完成对游标的操作后，应该关闭游标以释放资源。

• 案例1：

  • 打印当前月数据

CREATE OR REPLACE FUNCTION "myschema"."pr_date"()
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
    v_current_month DATE := DATE_TRUNC('month', CURRENT_DATE);
    v_record users%ROWTYPE;
BEGIN
    FOR v_record IN
        SELECT * FROM users WHERE DATE_TRUNC('month', created_at) = v_current_month
    LOOP
        RAISE NOTICE 'Data: %', v_record;
    END LOOP;
    RETURN;
EXCEPTION
    WHEN others THEN
        RAISE EXCEPTION 'Error occurred: %', SQLERRM;
END;
$BODY$ LANGUAGE plpgsql;

可以优化为for查询：(结构更加清晰…)

CREATE OR REPLACE FUNCTION "myschema"."pr_dat"()
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
    v_current_month DATE := DATE_TRUNC('month', CURRENT_DATE);
    v_record users%ROWTYPE;
BEGIN
    FOR v_record IN
        SELECT * FROM users WHERE DATE_TRUNC('month', created_at) = v_current_month
    LOOP
        RAISE NOTICE 'Data: %', v_record;
    END LOOP;
    RETURN;
EXCEPTION
    WHEN others THEN
        RAISE EXCEPTION 'Error occurred: %', SQLERRM;
END;
$BODY$ LANGUAGE plpgsql;

如果需要只需要打印某个字段，如下：

CREATE OR REPLACE FUNCTION "myschema"."pr_dat"()
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
    v_current_month DATE := DATE_TRUNC('month', CURRENT_DATE);
    v_record TEXT; -- 设定为与 name 字段相匹配的数据类型
BEGIN
    FOR v_record IN
        SELECT name FROM users WHERE DATE_TRUNC('month', created_at) = v_current_month
    LOOP
        RAISE NOTICE 'Data: %', v_record;
    END LOOP;
    RETURN;
EXCEPTION
    WHEN others THEN
        RAISE EXCEPTION 'Error occurred: %', SQLERRM;
END;
$BODY$ LANGUAGE plpgsql;

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””。