[MySQL] Generated invalid PLPGSQL using EXIT HANDLER block

[ssherwood]

Running Voyager to migrate a set of stored procedures from MySQL (MariaDB 10.5) and identified a syntax issue with an exit handler:

Original:

...
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
	get diagnostics CONDITION 1 @sqlstate = returned_sqlstate,
	@errno = mysql_errno,
	@text = message_text;
    
	SET @full_error = CONCAT(
  	"ERROR ",
  	@errno,
  	" (",
  	@sqlstate,
  	"): ",
  	@text
	);
    
	SELECT
  	@full_error;
    
  END;
...

Results:

...
	SET full_error = CONCAT(
  	'ERROR ',
  	errno,
  	' (',
  	sqlstate,
  	'): ',
  	text
	);
    
	SELECT
  	full_error;
    
  END;
...

The result is missing a proper begin and loses additional statements. In general, this block seems largely difficult to translate into Postgres without some effort.

Sample logic we have used to replace this block by hand looks like this (which is applied at the end of the procedure):

  EXCEPTION WHEN others then
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            v_msg     = MESSAGE_TEXT,
            v_detail  = PG_EXCEPTION_DETAIL,
            v_hint    = PG_EXCEPTION_HINT,
            v_context = PG_EXCEPTION_CONTEXT;
        raise notice E'Got exception:
            state  : %
            message: %
            detail : %
            hint   : %
            context: %', v_state, v_msg, v_detail, v_hint, v_context;