Why does the PL/pgSQL compiler do this? - Mailing list pgsql-sql

<div dir="ltr"><span style="font-size:12.8px">Here is the complete function, but all you need to look at is the
exceptionblock. (I didn't write this code) :-)  I will ask the question after the code.</span><div
style="font-size:12.8px"><br/></div><div style="font-size:12.8px"><p class="MsoNormal"><font face="monospace,
monospace"size="1">CREATE OR REPLACE FUNCTION etl_app.detl_tx_pull_client_<wbr />stat(</font><p class="MsoNormal"><font
face="monospace,monospace" size="1">    p_start_date character varying,</font><p class="MsoNormal"><font
face="monospace,monospace" size="1">    p_end_date character varying)</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">  RETURNS boolean AS</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">$BODY$</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">DECLARE</font><p
class="MsoNormal"><fontface="monospace, monospace" size="1">        COUNT INTEGER;</font><p class="MsoNormal"><font
face="monospace,monospace" size="1">        SOURCE RECORD;</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">       v_check_count INTEGER;</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">BEGIN</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">        COUNT := 0;</font><p
class="MsoNormal"><fontface="monospace, monospace" size="1"> </font><p class="MsoNormal"><font face="monospace,
monospace"size="1">        SELECT count(*) into v_check_count</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">        FROM fs_QSN_APP.tx_pull_client_stat</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">        WHERE updateddate >= TO_DATE(p_start_date,'DD-MON-<wbr />YY HH24:MI:SS') AND updateddate
<=TO_DATE(p_end_date,'DD-MON-YY HH24:MI:SS');</font><p class="MsoNormal"><font face="monospace, monospace"
size="1"> </font><pclass="MsoNormal"><font face="monospace, monospace" size="1">        IF v_check_count > 0
then</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">                RAISE INFO 'Rows detected=%',
v_check_count;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">                DELETE FROM 
QSN_APP.tx_pull_client_stat;</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">                RAISE
INFO'Done Deleting tx_pull_client_stat';</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">               INSERT INTO QSN_APP.tx_pull_client_stat (PULL_STAT_KEY,<wbr
/>COUNTRYCODE2TPOSTALCOORDINATE,<wbr/>POSTALCODE2TPOSTALCOORDINATE,<wbr />SERVICE2TX_SERVICE_CATALOG,<wbr
/>MATCH_RATE,REVENUE_AMT,LAST_<wbr/>CALCULATED_DATE,KEY2TX_<wbr />CRITERIA_TREE,CREATEDDATE,<wbr
/>CREATEDBYT2USER,UPDATEDDATE,<wbr/>UPDATEDBY2TUSER)</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">               select PULL_STAT_KEY,<wbr />COUNTRYCODE2TPOSTALCOORDINATE,<wbr
/>POSTALCODE2TPOSTALCOORDINATE,<wbr/>SERVICE2TX_SERVICE_CATALOG,<wbr />MATCH_RATE,REVENUE_AMT,LAST_<wbr
/>CALCULATED_DATE,KEY2TX_<wbr/>CRITERIA_TREE,CREATEDDATE,<wbr />CREATEDBYT2USER,UPDATEDDATE,<wbr
/>UPDATEDBY2TUSER</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">                FROM
fs_QSN_APP.tx_pull_client_<wbr/>stat;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">       
        RAISEINFO 'Done Inserting tx_pull_client_stat';</font><p class="MsoNormal"><font face="monospace, monospace"
size="1">       END IF;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> </font><p
class="MsoNormal"><fontface="monospace, monospace" size="1">        RETURN TRUE;</font><p class="MsoNormal"><font
face="monospace,monospace" size="1">EXCEPTION WHEN OTHERS THEN</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">        RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">        ROLLBACK;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">       
RETURNFALSE;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">END;</font><p
class="MsoNormal"><fontface="monospace, monospace" size="1">$BODY$</font><p class="MsoNormal"><font face="monospace,
monospace"size="1">  LANGUAGE plpgsql VOLATILE</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> 
COST100;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"><br /></font><p class="MsoNormal"><font
face="georgia,serif" size="1">So, here is the question. Why does the compiler not catch:</font><p
class="MsoNormal"><fontface="georgia, serif" size="1">1) ROLLBACK; is not a valid PL/pgSQL command</font><p
class="MsoNormal"><fontface="georgia, serif" size="1">2) ROLLBACK; and RETURN FALSE; can never be reached</font><p
class="MsoNormal"><fontface="georgia, serif" size="1"><br /></font><p class="MsoNormal"><font face="georgia, serif"
size="1">Again,my question is about the compiler, not about wrongness of the error handling  code. </font><p
class="MsoNormal"><fontface="georgia, serif" size="1">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 upthe call stack.</font><p class="MsoNormal"><font face="georgia, serif" size="1"><br /></font><p
class="MsoNormal"><fontface="georgia, serif" size="1">This code is what happens when you let an Oracle PL/SQL
programmertry his hand at PL/pgSQL. ;-)</font></div></div>