PGSQL循环删除库中表

PGsql循环删除库中表CREATE OR REPLACE FUNCTION public.del_table()RETURNS voidLANGUAGE plpgsqlAS $function$DECLAREtmp VARCHAR(512);DECLARE names CURSOR FORselect tablename from pg_tables where schemaname='publ

_Zephyrus_

643人浏览 · 2021-04-16 15:35:28

_Zephyrus_ · 2021-04-16 15:35:28 发布

PGsql循环删除库中表

CREATE OR REPLACE FUNCTION public.del_table()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    tmp VARCHAR(512);
DECLARE names CURSOR FOR 
    select tablename from pg_tables where schemaname='public';
BEGIN
  FOR stmt IN names LOOP
    tmp := 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
    RAISE NOTICE 'notice: %', tmp;
    EXECUTE 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
  END LOOP;
  RAISE NOTICE 'finished .....';
END;
 
$function$
;