Postgresql

PL/pgSQL 函式創建中的錯誤

  • September 5, 2012

我不知道這個問題是否更適合這里或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;

引用自:https://dba.stackexchange.com/questions/22909