Need to truncate tables using plpgsql function

Question

I have writen a pl/pgsql function to trucate tables. HIST_CLEANUP_TBL has 5 table_name in it. Out of 5, 1 table_name doesn't exist in the database.

Need is: It has to check all tables. If table does not exist's write the error to a error table and truncate the remaining table.

However, here when ever the unavailable table comes in the loop the function getting terminated.

How to captuture the error code and text in a variable and how to continue the loop though 1 table does not exist.

We are using postgresql 8.2

BEGIN  

    for recordvar in 
    SELECT 'truncate table  ' || TABLE_NAME  as SQRY  FROM HIST_CLEANUP_TBL
    loop
       QUERY1 := recordvar.SQRY;
       RAISE NOTICE 'QUERY: (%)', QUERY1;     

       BEGIN
       execute QUERY1;
       RAISE NOTICE 'QUERY EXECUTED SUCCESSFULLY';   
       EXCEPTION
       WHEN others
       THEN
       --GET STACKED DIAGNOSTICS 
      ERR_NUM := SQLSTATE;
      ERR_MESG := SQLERRM ;

       RAISE NOTICE 'ERROR WHILE EXECUTING THE QUERY : %',ERR_NUM;

      insert into Refresh_Error_tbl(sqlerr_code,sqlerr_desc,sql_desc, INSERT_DT,refresh_load_id) values(ERR_NUM,ERR_MESG,QUERY1,current_timestamp,'1');
       END;
      end loop; 
END;

Is it possible your exception clause is itself throwing an exception? For example, if the insert goes wrong? If there is an exception inside the exception handler then for sure it will abort — harmic
– harmic, Commented Feb 21, 2014 at 10:31
HIST_CLEANUP_TBL Table_name A B C D E Here, C table doe not exists in database. The function is running for table A and B when C comes it terminating the function and table D and E are not truncated. So need is it hase to write the table C to error table and process D and E — user3145945
– user3145945, Commented Feb 21, 2014 at 13:14

Tomasz Siorek · Accepted Answer · 2014-02-21 17:59:19Z

2

Cause of the error must be somewhere else. Following code is working properly:

CREATE TABLE hist_cleanup (
    table_name  text
);

CREATE TABLE table_a();
CREATE TABLE table_b();
--  table_c missing
CREATE TABLE table_d();
CREATE TABLE table_e();

INSERT INTO hist_cleanup
VALUES
    ('table_a')
,   ('table_b')
,   ('table_c')
,   ('table_d')
,   ('table_e');

CREATE TABLE refresh_error (
    sqlerr_code     text
,   sqlerr_desc     text
,   sql_desc        text
,   insert_dt       timestamp
,   refresh_load_id int
);

CREATE OR REPLACE FUNCTION cleanup()
RETURNS VOID AS $$
DECLARE
    _rec    record;
    _query  text;
BEGIN  

    FOR _rec IN (
        SELECT  table_name
        FROM    hist_cleanup
    ) LOOP
        _query := 'TRUNCATE TABLE ' || _rec.table_name;

        RAISE NOTICE 'QUERY: (%)', _query;     

        BEGIN
            EXECUTE _query;
            RAISE NOTICE 'QUERY EXECUTED SUCCESSFULLY';   
        EXCEPTION
            WHEN OTHERS
            THEN
                RAISE NOTICE 'ERROR WHILE EXECUTING THE QUERY: % %', SQLSTATE, SQLERRM;
                INSERT INTO refresh_error (
                    sqlerr_code
                ,   sqlerr_desc
                ,   sql_desc
                ,   insert_dt
                ,   refresh_load_id )
                VALUES (
                    SQLSTATE
                ,   SQLERRM
                ,   _query
                ,   CURRENT_TIMESTAMP
                ,   '1'
                );
        END;
    END LOOP;

    RETURN;

END $$ LANGUAGE plpgsql;

SELECT  cleanup();

The output:

NOTICE:  QUERY: (TRUNCATE TABLE table_a)
NOTICE:  QUERY EXECUTED SUCCESSFULLY
NOTICE:  QUERY: (TRUNCATE TABLE table_b)
NOTICE:  QUERY EXECUTED SUCCESSFULLY
NOTICE:  QUERY: (TRUNCATE TABLE table_c)
NOTICE:  ERROR WHILE EXECUTING THE QUERY: 42P01 relation "table_c" does not exist
NOTICE:  QUERY: (TRUNCATE TABLE table_d)
NOTICE:  QUERY EXECUTED SUCCESSFULLY
NOTICE:  QUERY: (TRUNCATE TABLE table_e)
NOTICE:  QUERY EXECUTED SUCCESSFULLY

edited Feb 21, 2014 at 17:59

answered Feb 21, 2014 at 17:52

Tomasz Siorek

7215 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3145945 Over a year ago

Thank you for the solution I will recode the function

user3145945 Over a year ago

Hi Tomasz, I re-coded the function as per your suggestion it is running fine. Problem is I was not able to store the SQLSTATE and SQLERRM to a variable. When ever I tried to store it throwing error if we ignore thne it is running fine

Tomasz Siorek Over a year ago

Glad to hear it. As for the variables meant to store the SQLSTATE and SQLERRM variables values, maybe they weren't declared or were declared with unproper type? Regardless of that, I think you can accept my answer.

sergei Over a year ago

Thank you! My problem was pretty same, I needed to truncate 7 tables in for loop and check whether it succeeds. When putted FK into table, the script raises an error. Thank you once again!

Collectives™ on Stack Overflow

Need to truncate tables using plpgsql function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related