SQL Server转PostgreSQL迁移：表类型参数传函数求等效代码

Got it, let's break down how to translate your SQL Server code to PostgreSQL—especially the tricky part of passing table-like data to functions. PostgreSQL doesn't have direct equivalents to SQL Server's table variables or table-valued parameters, but we can use composite types and temporary tables/arrays to replicate the same behavior.

Step 1: Replace SQL Server Table Type with a PostgreSQL Composite Type #

First, create a composite type that matches the structure of your InactiveOwnDetails table type. This acts as a blueprint for the row data:

CREATE TYPE InactiveOwnDetails AS (
    EntType VARCHAR(10),  -- Matches SQL Server's nvarchar(10)
    EntName VARCHAR(400)  -- Matches SQL Server's nvarchar(400)
);

Step 2: Rewrite the Function to Accept Composite Input #

PostgreSQL functions can accept arrays of our composite type (or use TABLE parameters if you're on PostgreSQL 12+). Here's the equivalent of your Fn_GetInactivePpMailBodyContent function:

Option 1: Composite Type Array (Works in all modern PostgreSQL versions) #

CREATE OR REPLACE FUNCTION Fn_GetInactivePpMailBodyContent(
    p_inactive_details InactiveOwnDetails[]
) RETURNS TEXT AS $$  -- TEXT replaces SQL Server's NVARCHAR(MAX)
DECLARE
    result_string TEXT;
BEGIN
    -- Mimic your original logic (adjust this for actual data processing later)
    result_string := '<p> Dear'|| CAST(12 AS TEXT) || ', ';
    RETURN result_string;
END;
$$ LANGUAGE plpgsql;

Option 2: TABLE Parameter (PostgreSQL 12+) #

If you're running PostgreSQL 12 or newer, you can use a TABLE parameter directly, which feels closer to SQL Server's readonly table type:

CREATE OR REPLACE FUNCTION Fn_GetInactivePpMailBodyContent(
    p_inactive_details TABLE(EntType VARCHAR(10), EntName VARCHAR(400))
) RETURNS TEXT AS $$
DECLARE
    result_string TEXT;
BEGIN
    result_string := '<p> Dear'|| CAST(12 AS TEXT) || ', ';
    RETURN result_string;
END;
$$ LANGUAGE plpgsql;

Step 3: Rewrite the Stored Procedure #

PostgreSQL uses PROCEDURE (available since version 11) for code that modifies data. We'll use a temporary table to simulate SQL Server's table variable (@InactiveOwnDetails):

CREATE OR REPLACE PROCEDURE SP_GenerateInactiveOwnEmail()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Create a temporary table to act like SQL Server's table variable
    -- ON COMMIT DROP ensures it's cleaned up automatically after the procedure runs
    CREATE TEMP TABLE temp_InactiveOwnDetails (
        EntType VARCHAR(10),
        EntName VARCHAR(400)
    ) ON COMMIT DROP;

    -- Optional: Add your logic to populate the temporary table here
    -- INSERT INTO temp_InactiveOwnDetails (EntType, EntName) VALUES (...);

    -- Call the function and insert the result into your target table
    -- Option 1: For the array-based function
    INSERT INTO InactiveOwnEmailActivity (Subject_Email)
    SELECT Fn_GetInactivePpMailBodyContent(
        ARRAY(SELECT row(EntType, EntName)::InactiveOwnDetails FROM temp_InactiveOwnDetails)
    );

    -- Option 2: For the TABLE parameter-based function (PostgreSQL 12+)
    -- INSERT INTO InactiveOwnEmailActivity (Subject_Email)
    -- SELECT Fn_GetInactivePpMailBodyContent(
    --     TABLE(SELECT EntType, EntName FROM temp_InactiveOwnDetails)
    -- );
END;
$$;

Key Differences to Keep in Mind #

• No Table Variables: PostgreSQL doesn't support table variables like SQL Server—temporary tables or composite arrays are the standard alternatives.
• String Concatenation: Use || instead of + (PostgreSQL reserves + for arithmetic operations).
• Data Types: NVARCHAR(MAX) maps directly to PostgreSQL's TEXT type, which handles unlimited-length text.
• Stored Procedures: PostgreSQL 11+ supports PROCEDURE; for older versions, use a FUNCTION with VOLATILE and RETURNS VOID.

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