ANBOB™

在 PostgreSQL 中，PL/pgSQL 不直接支持 TABLE OF 类型，这是 Oracle PL/SQL 中的一种集合类型。不过，PostgreSQL 提供了其他方式来实现类似的功能，例如使用数组、复合类型（composite types）和表值函数（table-valued functions）。最近在一个生产数据库oracle迁移到postgresql替换过程中，遇到了拆分由逗号分隔的字符串返回table的需求。

在oracle中自定义了一个type, function 反悔该对象。

create type utv as table of varchar2(200);

create function strlist returne utv 
as
begin
...
end;

PostgreSQL 提供了多种方法来拆分由逗号分隔的字符串，包括使用unnest(string_to_array())，regexp_split_to_array() ，regexp_split_to_table()

1. 使用数组

PostgreSQL 支持数组类型，可以用来存储多个相同类型的值。你可以使用数组来替代 TABLE OF 类型。

示例：

假设你有一个表 employees，并且你想在 PL/pgSQL 中存储一些员工记录。

-- 创建一个复合类型
CREATE TYPE employee_type AS (
    id INT,
    name TEXT,
    salary NUMERIC
);

-- 创建一个函数来返回一个员工数组
CREATE OR REPLACE FUNCTION get_employees()
RETURNS employee_type[] AS $$
DECLARE
    emp_array employee_type[];
BEGIN
    -- 将查询结果存储到数组中
    SELECT ARRAY(
        SELECT (id, name, salary)::employee_type
        FROM employees
    ) INTO emp_array;

    RETURN emp_array;
END;
$$ LANGUAGE plpgsql;

2. 使用表值函数

PostgreSQL 支持表值函数，可以返回一个表的结果集。这在处理多行数据时非常有用。

create or replace function function_name (
   parameter_list
)
returns table ( column_list )
language plpgsql
as
$$
declare
-- variable declaration
begin
-- body
end;
$$;

示例：

假设你有一个表 employees，在 PL/pgSQL 中返回一个员工记录集。

-- 创建一个表值函数
CREATE OR REPLACE FUNCTION get_employees()
RETURNS TABLE (id INT, name TEXT, salary NUMERIC) AS $$
BEGIN
    RETURN QUERY SELECT id, name, salary FROM employees;
END;
$$ LANGUAGE plpgsql;
----  or --------

CREATE OR REPLACE FUNCTION split_csv(input_string text)
RETURNS TABLE (value text) AS $$
BEGIN
    RETURN QUERY SELECT unnest(string_to_array(input_string, ','));
END;
$$ LANGUAGE plpgsql;

SELECT * FROM split_csv('apple,banana,cherry');
 value
--------
 apple
 banana
 cherry
(3 rows)

3. 使用临时表

你也可以在 PL/pgSQL 中使用临时表来存储中间结果。

示例：

CREATE OR REPLACE FUNCTION process_employees()
RETURNS VOID AS $$
BEGIN
    -- 创建一个临时表
    CREATE TEMPORARY TABLE temp_employees (
        id INT,
        name TEXT,
        salary NUMERIC
    );

    -- 插入数据到临时表
    INSERT INTO temp_employees (id, name, salary)
    SELECT id, name, salary FROM employees;

    -- 处理临时表中的数据
    -- 例如，更新某些记录
    UPDATE temp_employees
    SET salary = salary * 1.1
    WHERE id IN (1, 2, 3);

    -- 将处理后的数据插入到另一个表中
    INSERT INTO processed_employees (id, name, salary)
    SELECT id, name, salary FROM temp_employees;
END;
$$ LANGUAGE plpgsql;

4. 使用游标

游标可以用来逐行处理查询结果，适用于需要逐行处理数据的场景。

示例：

CREATE OR REPLACE FUNCTION process_employees_cursor()
RETURNS VOID AS $$
DECLARE
    emp_record RECORD;
    emp_cursor CURSOR FOR SELECT id, name, salary FROM employees;
BEGIN
    OPEN emp_cursor;

    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN NOT FOUND;

        -- 处理每一行数据
        -- 例如，更新某些记录
        IF emp_record.id IN (1, 2, 3) THEN
            UPDATE employees
            SET salary = emp_record.salary * 1.1
            WHERE id = emp_record.id;
        END IF;
    END LOOP;

    CLOSE emp_cursor;
END;
$$ LANGUAGE plpgsql;

5.自定义类型

-- 定义类型
CREATE TYPE strlist AS (v text[]);

-- 创建函数
CREATE OR REPLACE FUNCTION split_csv1(input_string text)
RETURNS strlist
LANGUAGE plpgsql
AS $$
DECLARE
    var_list strlist;
BEGIN
    -- 将输入字符串拆分为数组
    var_list.v := string_to_array(input_string, ',');
    
    -- 返回结果
    RETURN var_list;
END;
$$;

-- or --

CREATE OR REPLACE FUNCTION split_csv(input_string text)
RETURNS TABLE (value text) AS $$
BEGIN
    RETURN QUERY SELECT unnest(string_to_array(input_string, ','));
END;
$$ LANGUAGE plpgsql;

SELECT * FROM split_csv1('apple,banana,cherry');
   v
--------
 apple
 banana
 cherry

总结

在 PostgreSQL 中，虽然没有直接支持 TABLE OF 类型，但可以通过使用数组、表值函数、临时表和游标、自定义等方式来实现类似的功能。选择哪种方法取决于具体的使用场景和需求。希望这些示例能帮助你在 PostgreSQL 中实现类似 Oracle PL/SQL 中的 TABLE OF 功能。

打赏

微信扫一扫，打赏作者吧～