PostgreSQL函数BEGIN块配EXCEPTION子句用法及报错排查

Ah, I see the issue here. The error you're getting is because PL/pgSQL doesn't allow explicit transaction control statements like ROLLBACK or standalone BEGIN blocks inside functions. Functions run within the context of the transaction that calls them, so you can't start/end transactions from inside the function itself. The error message hints at this: it tells you to use a BEGIN block with an EXCEPTION clause instead of explicit transaction commands.

Let's break down what's wrong in your code and fix it step by step:

Key Issues in Your Original Code #

• You're using ROLLBACK inside an exception handler, which is invalid in PL/pgSQL.
• There are unnecessary nested BEGIN blocks that complicate the structure and contribute to the error.
• Your dynamic SQL is vulnerable to SQL injection and uses error-prone string concatenation for dates/identifiers.

Fixed Function Code #

CREATE OR REPLACE FUNCTION ssp2_pcat.shift_release_dates_V5() 
RETURNS void 
LANGUAGE plpgsql 
COST 100 VOLATILE 
AS $BODY$ 
DECLARE
    C1 CURSOR FOR 
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM RESET_DATES) 
        ORDER BY 1;
    C2 CURSOR (iTable_Name VARCHAR) FOR 
        SELECT TABLE_NAME, COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = iTable_Name 
          AND UPPER(DATA_TYPE) = 'DATE' 
          AND (COLUMN_NAME LIKE '%START%' OR COLUMN_NAME LIKE '%END%') 
          AND (COLUMN_NAME NOT LIKE '%TEST%' 
               AND COLUMN_NAME NOT LIKE '%PCAT%' 
               AND COLUMN_NAME NOT LIKE '%ORDER%' 
               AND COLUMN_NAME NOT LIKE '%SEASON%' 
               AND COLUMN_NAME NOT LIKE '%_AT') 
        ORDER BY 1, 2;
    Wed DATE;
    Thurs DATE;
    Start_Date_Row INTEGER;
    End_Date_Row INTEGER;
    Start_Date_Update_Rows INTEGER;
    End_Date_Update_Rows INTEGER;
    l_start TIMESTAMP;
    l_end TIMESTAMP;
    Time_Taken VARCHAR(20);
BEGIN
    l_start := clock_timestamp();
    
    -- Calculate Wednesday and Thursday relative to tomorrow
    SELECT 'TOMORROW'::date + (3 + 7 - extract(dow FROM 'TOMORROW'::date))::int % 7 INTO Wed;
    SELECT 'TOMORROW'::date + (4 + 7 - extract(dow FROM 'TOMORROW'::date))::int % 7 INTO Thurs;
    
    UPDATE RESET_DATES 
    SET START_DATE_ROWS = NULL, 
        END_DATE_ROWS = NULL, 
        START_DATE_UPDATED = NULL, 
        END_DATE_UPDATED = NULL, 
        LAST_UPDATED = NULL;
    
    RAISE NOTICE 'Wednesday: %', Wed;
    RAISE NOTICE 'Thursday: %', Thurs;
    
    FOR i IN C1 LOOP
        FOR j IN C2(i.Table_Name) LOOP
            BEGIN -- Exception block for individual column operations
                IF j.COLUMN_NAME LIKE '%START%' THEN
                    -- Count rows with target start date (using safe dynamic SQL)
                    EXECUTE format('SELECT COUNT(*) FROM %I WHERE %I = $1', 
                                   i.TABLE_NAME, j.COLUMN_NAME) 
                    INTO Start_Date_Row 
                    USING Thurs;
                    
                    RAISE NOTICE 'Start date count query executed for %.%', i.TABLE_NAME, j.COLUMN_NAME;
                    RAISE NOTICE 'Start_Date_Row: %', Start_Date_Row;
                    
                    -- Update start dates (safe dynamic SQL)
                    EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', 
                                   i.TABLE_NAME, j.COLUMN_NAME, j.COLUMN_NAME) 
                    USING current_date + 1, Thurs;
                    
                    Start_Date_Update_Rows := SQL%ROWCOUNT;
                    RAISE NOTICE 'Start_Date_Update_Rows: %', Start_Date_Update_Rows;
                    
                    UPDATE RESET_DATES 
                    SET start_date_rows = Start_Date_Row, 
                        start_date_updated = Start_Date_Update_Rows, 
                        last_updated = current_timestamp::timestamp(0) 
                    WHERE table_name = i.TABLE_NAME;
                ELSE -- Handle END_DATE columns
                    -- Count rows with target end date
                    EXECUTE format('SELECT COUNT(*) FROM %I WHERE %I = $1', 
                                   i.TABLE_NAME, j.COLUMN_NAME) 
                    INTO End_Date_Row 
                    USING Wed;
                    
                    RAISE NOTICE 'End date count query executed for %.%', i.TABLE_NAME, j.COLUMN_NAME;
                    RAISE NOTICE 'End_Date_Row: %', End_Date_Row;
                    
                    -- Update end dates
                    EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', 
                                   i.TABLE_NAME, j.COLUMN_NAME, j.COLUMN_NAME) 
                    USING current_date, Wed;
                    
                    End_Date_Update_Rows := SQL%ROWCOUNT;
                    RAISE NOTICE 'End_Date_Update_Rows: %', End_Date_Update_Rows;
                    
                    UPDATE RESET_DATES 
                    SET end_date_rows = End_Date_Row, 
                        end_date_updated = End_Date_Update_Rows, 
                        last_updated = current_timestamp::timestamp(0) 
                    WHERE table_name = i.TABLE_NAME;
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    -- Log the error but continue processing other columns/tables
                    RAISE NOTICE 'Error processing %.%: %', i.TABLE_NAME, j.COLUMN_NAME, sqlerrm;
                    -- No ROLLBACK needed here; the exception will roll back only this block's changes
            END; -- End of exception block for this column
        END LOOP;
    END LOOP;
    
    l_end := clock_timestamp();
    Time_Taken := (l_end - l_start)::VARCHAR;
    RAISE NOTICE 'SHIFT_RELEASE_DATES Took: %', Time_Taken;
END;
$BODY$;

Key Changes Made #

1. Removed Explicit ROLLBACK: Instead, we use a BEGIN...EXCEPTION block around each column's operations. When an error occurs, this block will automatically roll back any changes made within it, but allow the function to continue processing other columns/tables.
2. Cleaned Up Nested BEGIN Blocks: Removed unnecessary nested BEGIN statements that were causing confusion and contributing to the transaction error.
3. Safe Dynamic SQL with format() and USING:
   • Used format() with %I to safely quote identifiers (table/column names), preventing SQL injection and handling names with special characters.
   • Used the USING clause to pass parameters (dates) to dynamic SQL, avoiding error-prone string concatenation and ensuring proper date handling.
4. Simplified Notice Messages: Made the RAISE NOTICE calls cleaner and more informative.

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