postgresql删除所有库表问题！急！

[沙漠里的海豚]

写了一个存储过程来删除所有public库表，存储过程如下：
CREATE FUNCTION aaa() RETURNS void AS

$$

DECLARE
names CURSOR FOR
select tablename from pg_tables where schemaname='public';
BEGIN
FOR stmt IN names LOOP
EXECUTE 'DROP TABLE '||quote_ident(stmt.tablename)||'CASCADE;';
END LOOP;
END;

$$ LANGUAGE plpgsql;


EXECUTE PROCEDURE aaa();


执行后出现以下一些错误：
ERROR: syntax error at or near "$1"
LINE 1: $1
^
QUERY: $1
CONTEXT: SQL statement in PL/PgSQL function "aaa" near line 5
ERROR: syntax error at or near "aaa"
LINE 1: EXECUTE PROCEDURE aaa();

问题出在哪里呢？望指教。。。

[rucypli]

不懂pg的语法

[iihero]

CREATE FUNCTION aaa() RETURNS void AS $$
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;

$$ LANGUAGE plpgsql;

调用时用： select aaa();

[jyd30]

如果想删除某个 schema下的所有表，一句话足以。 DROP SCHEMA public CASCADE; 则会自动删除 public 下的所有表及函数。 之后再创建public； create schema public; grant all on schema to ... ; 授权