主页 / dba / 问题 / 22909
Accepted
Stephane Rolland
Asked: 2012-08-23 06:28:12 +0800 CST

PL/pgSQL 函数创建中的错误

  • 772

我不知道这个问题更适合这里还是 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 的新手，不知道这个错误信息是什么意思。

postgresql cursors

2 个回答

  1. 这个特定的例子可以更简单。
    您可以TRUNCATE一次创建多个表。聚合所有表名并执行单个语句：

    CREATE OR REPLACE FUNCTION truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE;'
      FROM   pg_tables t
      WHERE  t.tableowner = _username
      AND    t.schemaname = 'public'
   );
END;
$func$ LANGUAGE plpgsql;

    称呼：

    SELECT truncate_tables('postgres');

    string_agg()需要 PostgreSQL 9.0或更高版本。
    在 8.4 中，您可以替换：

    array-to_string(array_agg(quote_ident(t.tablename)), ', ')

    对于 v8.3，您可以编写自己的聚合函数——相当简单，但并不比循环解决方案简单。

    在 PostgreSQL 9.1 中一次删除或截断多个表时性能会下降。在即将发布的 9.2 版本中对此进行了修复。我引用发行说明：

    当许多表被删除或截断时，提高检查点的 fsync-request 队列的性能(Tom Lane)

    pgsql-hackers 上的相关主题。

    @Craig 在 SO 的相关回答帮助我发现了这一点。

    • 5
  2. Best Answer

    尝试放弃显式光标：

    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;
    • 4

相关问题