Redshift Stored Procedure to execute multiple statements in a row

Question

I have a SQL script like so:


    SELECT 'drop table if exists '||schemaname||'.'|| tablename || ' cascade;' 
    FROM pg_tables 
    WHERE schemaname in ('schema_1','schema_2','schema_2');

I want to create a stored procedure to execute all the commands this script generates. If you run the script now it gives an output like so:


    drop table if exists schema_1.table_a cascade;
    drop table if exists schema_2.table_b cascade;
    drop table if exists schema_3.table_c cascade;
    drop table if exists schema_1.table_d cascade;

I know you can have loops execute multiple lines one after another but I'm having trouble following the redshift docs to get this work. Any help would be appreciated!

stackoverflow.com/questions/39332772/… and loop over the result stackoverflow.com/questions/38042699/… — nbk
– nbk, Commented Sep 21, 2023 at 21:15

abhinavkm · Accepted Answer · 2023-09-21 22:18:45Z

0

I used the following snippet:

CREATE OR REPLACE PROCEDURE drop_tables_in_schemas()
AS $$
DECLARE
    schema_name VARCHAR(255);
    table_name VARCHAR(255);
    drop_statement VARCHAR(1000);
    cur CURSOR FOR
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname IN ('schema_1', 'schema_2', 'schema_3');
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO schema_name, table_name;
        EXIT WHEN NOT FOUND;
        
        drop_statement := 'DROP TABLE IF EXISTS ' || schema_name || '.' || table_name || ' CASCADE;';
        EXECUTE drop_statement;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;


CALL drop_tables_in_schemas();

answered Sep 21, 2023 at 22:18

abhinavkm

316 bronze badges

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

Collectives™ on Stack Overflow

Redshift Stored Procedure to execute multiple statements in a row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related