From: | Michael Moore <michaeljmoore(at)gmail(dot)com> |
---|---|
To: | postgres list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Why does the PL/pgSQL compiler do this? |
Date: | 2016-10-31 22:13:58 |
Message-ID: | CACpWLjOkzeKNNLccAnR7EyMYH+4w8SnhEve43rD+VLoXQ4ROEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Here is the complete function, but all you need to look at is the exception
block. (I didn't write this code) :-) I will ask the question after the
code.
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. ;-)
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-10-31 22:24:14 | Re: Why does the PL/pgSQL compiler do this? |
Previous Message | Michael Moore | 2016-10-31 19:10:09 | Re: PL/pgSQL Audit Utility |