批量删除前缀 xx% 的数据库表（存储过程函数）

CREATE OR REPLACE FUNCTION "public"."drop_tmp_table"("user_name" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$
#variable_conflict use_variable
    DECLARE
        _tbl text;
    BEGIN
        FOR _tbl  IN
            SELECT quote_ident(table_schema) || '.'
                       || quote_ident(table_name)      -- escape identifier and schema-qualify!
            FROM   information_schema.tables
						
            WHERE  table_name LIKE 'dsp_' || user_name  || '_%'      -- your table name prefix
              AND    table_schema NOT LIKE 'pg\_%'    -- exclude system schemas
            LOOP
                RAISE NOTICE '%', 'DROP TABLE ' || _tbl;
                EXECUTE 'DROP TABLE ' || _tbl;         -- see below
            END LOOP;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100