How to Use a BEGIN block with an EXCEPTION clause in a Postgres Function?

Question

I created the below Postgres Function, which is compiled without any error in PGADMIN IV, but when I'm trying to run it, its throwing the below error,

ERROR: cannot begin/end transactions in PL/pgSQL

HINT: Use a BEGIN block with an EXCEPTION clause instead.

CONTEXT: PL/pgSQL function shift_release_dates() line 35 at SQL statement SQL state: 0A000

Can someone please let me know if there is anything I'm missing in the code?

My Function is as follows,

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;
SQL_Text               VARCHAR(4000);
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();
   SELECT 'TOMORROW'::date + ( 4 + 7 - extract ( dow FROM 
 'TOMORROW'::date))::int%7 INTO Thurs ;
   SELECT 'TOMORROW'::date + ( 3 + 7 - extract ( dow FROM 
'TOMORROW'::date))::int%7 INTO Wed ;
   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
      BEGIN
       FOR j IN C2 (i.Table_Name)LOOP
       BEGIN
           IF j.COLUMN_NAME LIKE '%START%' THEN -- Start Date
              SQL_Text := 'SELECT COUNT(*) FROM ' || i.TABLE_NAME || ' WHERE 
' || j.COLUMN_NAME || ' = ''' || TO_CHAR(Thurs, 'DD-MON-YYYY') || '''';
          RAISE NOTICE '% ', (SQL_Text);
          Execute SQL_Text INTO Start_Date_Row;
          RAISE NOTICE '% ', ('Start_Date_Row: ' || Start_Date_Row);
          SQL_Text := 'UPDATE ' || i.TABLE_NAME || ' SET ' || j.COLUMN_NAME || ' = ''' || current_timestamp::date+1 || ''' WHERE ' || j.COLUMN_NAME || ' = ''' || TO_CHAR(Thurs, 'DD-MON-YYYY') || ''''; 
          RAISE NOTICE '% ', (SQL_Text);
          Execute SQL_Text; 
          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 -- END_DATE
          SQL_Text := 'SELECT COUNT(*) FROM ' || i.TABLE_NAME || ' WHERE ' || j.COLUMN_NAME || ' = ''' || TO_CHAR(Wed, 'DD-MON-YYYY') || '''';
          RAISE NOTICE '% ', (SQL_Text);
          Execute SQL_Text INTO End_Date_Row;
          RAISE NOTICE '% ', ('End_Date_Row: ' || End_Date_Row);
          SQL_Text := 'UPDATE ' || i.TABLE_NAME || ' SET ' || j.COLUMN_NAME || ' = ''' || current_timestamp::date || ''' WHERE ' || j.COLUMN_NAME || ' = ''' || TO_CHAR(Wed, 'DD-MON-YYYY') || '''';
          RAISE NOTICE '% ', (SQL_Text);
          Execute SQL_Text; 
          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;
       BEGIN
         EXCEPTION 
         WHEN OTHERS THEN
         BEGIN
         RAISE NOTICE '% ', (sqlerrm); 
         ROLLBACK;
         END;            
       END;
   END;
   END LOOP;
   END;
   END LOOP;
   l_end  := clock_timestamp();
   Time_Taken := (l_end-l_start);
   RAISE NOTICE '% ',('SHIFT_RELEASE_DATES Took: ' || Time_Taken );

END;
$BODY$;

Thanks for your time! enter code here

Eugene Eugene · Accepted Answer · 2019-02-09T17:10:32

For such kind of questions, please remember to mention postgres server version.

Anyway, documentation says:

You cannot use transaction control commands, e.g. COMMIT, SAVEPOINT, and some utility commands, e.g. VACUUM, in SQL functions.

Unfortunately, I couldn't find the same sentence for plpgsql, but from my experience, inner transactional operations are prohibited for all functions.

Nevertheless, version 11 brings procedures that allow what you need:

Add SQL-level procedures, which can start and commit their own transactions (Peter Eisentraut)

They are created with the new CREATE PROCEDURE command and invoked via CALL.

How to Use a BEGIN block with an EXCEPTION clause in a Postgres Function?

1 Answers