SQL Server存储过程转PostgreSQL函数结果不一致，求有效转换方案

Let's break down the issues in your current PostgreSQL function and fix it to match the behavior of the original SQL Server stored procedure exactly.

Key Issues in Your Current Implementation #

• Unnecessary permanent table: Creating a regular Last30Days table will throw errors on repeated calls (since the table already exists). Temporary tables are better here, or even better—generate dates on-the-fly without tables entirely.
• Incorrect date range: Your current logic generates dates starting from CURRENT_DATE + 1, but the original procedure targets all dates from the first to the last day of the month provided in p_todaydate.
• Poor date type handling: Storing dates as varchar leads to avoidable conversion errors and performance hits. Stick to native date/timestamp types.
• Mismatched join condition: The original procedure compares dates (not full timestamps), but your code converts strings to timestamps for the join, which can cause mismatches.
• Grouping flaw: Including RA."Subscription_Id" in GROUP BY splits rows when there are no matching records (since it becomes NULL), while the original procedure groups solely by the date.

Corrected PostgreSQL Function #

CREATE OR REPLACE FUNCTION public.usp_appdownloadformonth(p_subscription_id integer, p_todaydate timestamp without time zone)
RETURNS TABLE(rowno bigint, downloaddate date, downloadsperday bigint)
LANGUAGE plpgsql
AS $function$
begin
    RETURN QUERY
    WITH last30days AS (
        -- Generate all dates from the first to last day of the target month
        SELECT generate_series(
            date_trunc('month', p_todaydate)::date,
            (date_trunc('month', p_todaydate) + interval '1 month - 1 day')::date,
            interval '1 day'
        )::date AS datevalue
    )
    SELECT
        ROW_NUMBER() OVER(ORDER BY l.datevalue ASC) AS rowno,
        l.datevalue AS downloaddate,
        COUNT(ra."DevicePlatform") AS downloadsperday
    FROM last30days l
    LEFT JOIN "ReportingRegisteredApps" ra
        -- Match dates exactly, just like the original procedure
        ON l.datevalue = ra."RegistrationDate"::date
        AND ra."Subscription_Id" = p_subscription_id
    GROUP BY l.datevalue
    ORDER BY l.datevalue;
END;
$function$;

What Changed & Why #

1. CTE for date generation: We use a Common Table Expression (CTE) instead of a temporary table to generate the date range directly—this is cleaner, faster, and avoids table management overhead.
2. Accurate date range: generate_series starts at the first day of the month (via date_trunc('month', p_todaydate)::date) and ends at the last day (calculated by adding a month and subtracting one day).
3. Native date types: All date values use the date type, eliminating string conversion errors and making the code more intuitive.
4. Fixed join logic: We cast ra."RegistrationDate" to date to match the original SQL Server behavior, and move the Subscription_Id filter directly into the join (instead of a subquery) for better performance and readability.
5. Simplified grouping: We only group by the date from last30days, ensuring one row per date regardless of whether there are matching records in ReportingRegisteredApps.
6. Improved return type: downloaddate is now a date type instead of character varying, which is the correct data type for date values.

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