Oracle至PostgreSQL的PIPELINED迁移及类型、函数转换需求

Let’s walk through converting your Oracle types and pipelined function to PostgreSQL, step by step. PostgreSQL uses composite types and set-returning functions as equivalents to Oracle’s object/table types and pipelined logic.

Step 1: Convert Oracle Object/Table Types to PostgreSQL #

Oracle’s T_dates object maps directly to a PostgreSQL composite type, and we don’t need a separate "table of" type—PostgreSQL functions can return a set of the composite type directly.

-- Create composite type equivalent to Oracle's T_dates OBJECT
CREATE TYPE t_dates AS (
    p_st_date VARCHAR(32767),
    p_en_date VARCHAR(32767)
);

Step 2: Rewrite the Pipelined Function #

Oracle’s PIPELINED function uses PIPE ROW() to emit rows incrementally. In PostgreSQL, we use RETURN NEXT to achieve the same behavior. I’ll complete the logic from your partial code (assuming it generates daily date-time ranges between the input start/end dates):

PostgreSQL Equivalent Function #

CREATE OR REPLACE FUNCTION f_get_dates(
    p_st_date VARCHAR,
    p_end_date VARCHAR,
    p_st_time VARCHAR,
    p_end_time VARCHAR
) RETURNS SETOF t_dates AS $$
DECLARE
    v_day_count INTEGER;
    p_temp_st_date DATE := TO_DATE(p_st_date, 'MM-DD-YYYY');
    v_end_date DATE := TO_DATE(p_end_date, 'MM-DD-YYYY');
    v_current_st_date VARCHAR;
    v_current_en_date VARCHAR;
BEGIN
    -- Calculate number of days between start and end date
    v_day_count := v_end_date - p_temp_st_date;

    -- Iterate through each day and emit rows
    FOR i IN 0..v_day_count LOOP
        -- Combine date and time for start/end values
        v_current_st_date := TO_CHAR(p_temp_st_date + i, 'MM-DD-YYYY') || ' ' || p_st_time;
        v_current_en_date := TO_CHAR(p_temp_st_date + i + 1, 'MM-DD-YYYY') || ' ' || p_end_time;

        -- Emit the row (equivalent to Oracle's PIPE ROW)
        RETURN NEXT (v_current_st_date, v_current_en_date)::t_dates;
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

Key Differences to Note #

• Return Type: Instead of a table type, we use SETOF t_dates to signal the function returns multiple rows of our composite type.
• Row Emission: RETURN NEXT replaces Oracle’s PIPE ROW()—each call sends one row to the result set.
• Type Casting: We cast the tuple (v_current_st_date, v_current_en_date) to t_dates to match the function’s return type.

Step 3: Use the Function #

You can call the function directly in a SELECT statement, just like in Oracle:

SELECT * FROM f_get_dates('01-01-2024', '01-03-2024', '09:00:00', '17:00:00');

This will output rows like:

内容的提问来源于stack exchange，提问作者lakshmi sowmya