截断 postgres 数据库中的所有表

发布于 2024-11-03 17:14:17 字数 859 浏览 0 评论 0原文

2010 年 5 月，Aaron 和 Henning 都提供了注册一个函数的代码，当稍后使用“用户名”参数调用该函数时，该函数将截断所有表。它在 Windows 7 上的 postgres 上运行良好。不幸的是，在 Ubuntu 上的 postgres 8.3 上两者都不起作用。

An error has occurred:
ERROR:  syntax error at or near "$1"
LINE 1:   $1 
          ^
QUERY:    $1 
CONTEXT:  SQL statement in PL/PgSQL function "truncate_tables" near line 6

我还尝试简化 select 语句以专注于 BEGIN For 子句，删除我在 Windows 中使用的复杂 WHERE 子句。
你能看到这里的问题吗？谢谢。检索表名后是否无法传递或读取？ $1 的问题不是意味着它找不到输入吗？

DECLARE 
    stmt RECORD;  
    statements CURSOR FOR  
    SELECT tablename FROM pg_tables  
    WHERE  tablename !~* 'sql_*' and tablename !~* 'pg_*' and tablename !~* 'schema_*';  
BEGIN  
    FOR stmt IN statements LOOP  
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';  
    END LOOP;  
END;

原文

In May 2010, Aaron and Henning both provided the code to register a function that when called later with a parameter for 'username' would truncate all the tables. It worked fine with postgres on Windows 7. Neither will work unfortunately for postgres 8.3 on Ubuntu.

An error has occurred:
ERROR:  syntax error at or near "$1"
LINE 1:   $1 
          ^
QUERY:    $1 
CONTEXT:  SQL statement in PL/PgSQL function "truncate_tables" near line 6

I have also tried simplifying the select statement to focus on the BEGIN For clause,
by removing the complicated WHERE clause I used in Windows.
Can you see the problem here? Thanks.
Is it unable to pass or read the tablenames after they are retrieved? Doesn't a problem with $1 mean it can't find its input?

DECLARE 
    stmt RECORD;  
    statements CURSOR FOR  
    SELECT tablename FROM pg_tables  
    WHERE  tablename !~* 'sql_*' and tablename !~* 'pg_*' and tablename !~* 'schema_*';  
BEGIN  
    FOR stmt IN statements LOOP  
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';  
    END LOOP;  
END;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泪意 2024-11-10 17:14:17

版本 8.3 和 8.4 之间似乎某些函数语法发生了变化。试试这个：

CREATE OR REPLACE FUNCTION public.truncate_tables(username IN VARCHAR) RETURNS void AS $
DECLARE
    stmt RECORD;
BEGIN
    FOR stmt IN SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public' LOOP
        execute 'TRUNCATE TABLE public.' || quote_ident(stmt.tablename) ||' CASCADE;';
    END LOOP;
END;
$ LANGUAGE plpgsql;

It appears that some of the function syntax changed between version 8.3 and 8.4. Try this:

CREATE OR REPLACE FUNCTION public.truncate_tables(username IN VARCHAR) RETURNS void AS $
DECLARE
    stmt RECORD;
BEGIN
    FOR stmt IN SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public' LOOP
        execute 'TRUNCATE TABLE public.' || quote_ident(stmt.tablename) ||' CASCADE;';
    END LOOP;
END;
$ LANGUAGE plpgsql;

回复收藏 0 原文

~没有更多了~