删除数据库所有表序列号-白红宇

--创建删除所有表的存储过程

CREATE OR REPLACE FUNCTION delAllTable()

RETURNS void

$BODY$

DECLARE

tmp VARCHAR(512);

DECLARE names CURSOR FOR

select tablename from pg_tables where tableowner='wxdch';

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;

$BODY$ LANGUAGE 'plpgsql' VOLATILE;

--执行存储过程

select * from delAllTable() as temp;

--创建删除所有序列号的存储过程

CREATE OR REPLACE FUNCTION delAllSeq()

RETURNS void AS

$BODY$

DECLARE

tmp VARCHAR(512);

DECLARE names CURSOR FOR

select from pg_class where relowner=(select usesysid from pg_user where usename='wxdch') and relkind='S' ;
BEGIN
FOR stmt IN names LOOP
tmp := 'DROP sequence '|| quote_ident(stmt.relname) || ' CASCADE;';
RAISE NOTICE 'notice: %', tmp;
EXECUTE 'DROP sequence '|| quote_ident(stmt.relname) || ' CASCADE;';
END LOOP;
RAISE NOTICE 'finished .....';
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION delAllSeq() OWNER TO postgres;
--执行存储过程
select from delAllSeq() as temp;

本文转自韬光星夜 51CTO博客，原文链接：http://blog.51cto.com/xfqxj/2055031，如需转载请自行联系原作者