Why does the PL/pgSQL compiler do this?

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. ;-)

Responses

Browse pgsql-sql by date

  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