CODEWITHSUNDEEP
amazon-redshift
kushagraagarwal
kushagraagarwal

Reputation: 103

Not able to dynamically truncate table in Redshift Stored Procedure

I have a table in Redshift (let's call it a status table) where I set the status of tables which I want to truncate. I created a Redshift Stored Procedure in order to achieve that. Here is my code for the SP:

CREATE OR REPLACE PROCEDURE <schema>.truncate_table()
AS $$
DECLARE
    v_tpsl RECORD;
    exec_statement VARCHAR(256);
BEGIN
    FOR v_tpsl in SELECT * from <schama>.tablename_process_status_log WHERE status = 'TRUE' LOOP
    exec_statement = 'TRUNCATE TABLE <schema>.' + quote_ident(v_tpsl.staging_table_name) + '_test;';
    RAISE INFO 'statement = %', exec_statement;
    EXECUTE exec_statement;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

Now when I am CALLING the Stored Procedure, I am getting this error: SQL Error [500310] [34000]: [Amazon](500310) Invalid operation: cursor does not exist;

enter image description here

I looked at the documentation of the SP to check if Truncate is possible or not. By looking at the examples, it looks like it's possible.

I am not sure what is going wrong in this. I am using RedshiftJDBC42-no-awssdk-1.2.34.1058.jar and connecting via DBeaver.

Upvotes: 2

Views: 2680

Answers (1)

kushagraagarwal
kushagraagarwal

Reputation: 103

It looks like I have found the answer. According to this, Any cursor that is open (explicitly or implicitly) is closed automatically when a COMMIT, ROLLBACK, or TRUNCATE statement is processed. In my next iteration of the loop, it's trying to accessing the cursor which is already closed.

Upvotes: 2

Related Questions