CREATE OR REPLACE FUNCTION etl_app.detl_tx_pull_client_stat(
p_start_date character varying,
p_end_date character varying)
RETURNS boolean AS
$BODY$
DECLARE
COUNT INTEGER;
SOURCE RECORD;
v_check_count INTEGER;
BEGIN
COUNT := 0;
SELECT count(*) into v_check_count
FROM fs_QSN_APP.tx_pull_client_stat
WHERE updateddate >= TO_DATE(p_start_date,'DD-MON-YY HH24:MI:SS') AND updateddate <= TO_DATE(p_end_date,'DD-MON-YY HH24:MI:SS');
IF v_check_count > 0 then
RAISE INFO 'Rows detected=%', v_check_count ;
DELETE FROM QSN_APP.tx_pull_client_stat;
RAISE INFO 'Done Deleting tx_pull_client_stat';
INSERT INTO QSN_APP.tx_pull_client_stat (PULL_STAT_KEY,COUNTRYCODE2TPOSTALCOORDINATE,POSTALCODE2TPOSTALCOORDINATE,SERVICE2TX_SERVICE_CATALOG,MATCH_RATE,REVENUE_AMT,LAST_CALCULATED_DATE,KEY2TX_CRITERIA_TREE,CREATEDDATE,CREATEDBYT2USER,UPDATEDDATE,UPDATEDBY2TUSER)
select PULL_STAT_KEY,COUNTRYCODE2TPOSTALCOORDINATE,POSTALCODE2TPOSTALCOORDINATE,SERVICE2TX_SERVICE_CATALOG,MATCH_RATE,REVENUE_AMT,LAST_CALCULATED_DATE,KEY2TX_CRITERIA_TREE,CREATEDDATE,CREATEDBYT2USER,UPDATEDDATE,UPDATEDBY2TUSER
FROM fs_QSN_APP.tx_pull_client_stat;
RAISE INFO 'Done Inserting tx_pull_client_stat';
END IF;
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
2) ROLLBACK; and RETURN FALSE; can never be reached
Again, my question is about the compiler, not about wrongness of the error handling code.
I understand that as far as fixing the error handling is concerned, the correct thing to do would be to remove the EXCEPTION block all together and let any errors be propagated up the call stack.
This code is what happens when you let an Oracle PL/SQL programmer try his hand at PL/pgSQL. ;-)
