Shulou(Shulou.com)06/01 Report--

Create a stored procedure that deletes all tables

CREATE OR REPLACE FUNCTION delAllTable ()

RETURNS void

AS

$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

-- execute stored procedures

Select * from delAllTable () as temp

-- create a stored procedure that deletes all serial numbers

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

-- execute stored procedures

Select * from delAllSeq () as temp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.