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!

Answer 1

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