Postgresql
PL/pgSQL 函式創建中的錯誤
我不知道這個問題是否更適合這里或SO …
這是我想啟動的腳本(函式的程式碼是從關於 SO 的問題中複製的):
\c mydb create or replace function truncate_tables(username in varchar) returns void as $$ declare stmt RECORD; statements cursor for select tablename from pg_tables where tableowner = username; begin for stmt in statements loop execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;'; end loop; end; $$ language 'plpgsql';
我收到以下錯誤:
ERROR: syntax at or near "$1" LINE1: $1 QUERY $1 CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5
我是 Postgres 和 PL/pgSQL 的新手,不知道這個錯誤消息是什麼意思。
嘗試放棄顯式游標:
begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create table foo(id serial); insert into foo default values; create or replace function truncate_tables(username in varchar) returns void as $$ declare r record; begin for r in (select tablename from pg_tables where tableowner = username) loop execute 'truncate table ' || quote_ident(r.tablename) || ' cascade'; end loop; end; $$ language 'plpgsql'; -- select truncate_tables('stack'); select * from foo; /* id ---- (0 rows) */ -- rollback;