批次刪除 postgresql 中的資料表

這個有好幾個作法,最直接的就是使用其他的語言(shell, python)中的 regular expression 處理。但這邊我們來介紹 plpgsql (PostgreSQL procedure language) 的作法。

pl/pgsql 的結構大概長這樣:

CREATE OR REPLACE FUNCTION your_function_name(variable text) RETURNS TEXT AS
$$
DECLARE
      -- 這邊放要定義的變數,例如
    -- 變數 定義;
    variable text;
    sql text;
    return_val text;
BEGIN
        -- 1. 這個區塊放你要撰寫的 SQL 程序語言
    -- 2. 要 assign 數值給已定義的變數,用 := 
    -- 3. 要把 string 串接(concatenate)起來的話,用 || ,例:
    sql := 'CREATE TABLE ' || quote_ident(variable) || '( id integer, name string)' ;
    execute sql;
    return_val := 'Table ' || quote_ident(variable) || ' created!';
    RETURN return_val;
END;

$$ LANGUAGE plpgsql;

語法基本的原則就是要有宣告,有開頭(BEGIN)就要有結尾(END),所以本例中的 pl/pgsql 如下:

CREATE OR REPLACE FUNCTION drop_tables(schema text, pattern text) RETURNS TEXT AS 
$$
-- 先定義變數
DECLARE
    r record;
    sql text;
    res text;
BEGIN
    res := 'Dropped table(s): ';
    FOR r in select tablename from pg_tables where tablename like pattern
    LOOP
        sql := 'DROP TABLE ' || quote_ident(schema) || '.' || quote_ident(r.tablename);
        res := res || r.tablename || ' ';
        execute sql;
    END LOOP;
    res := substr(res, 1, length(res)-1);
    RETURN res;
END;
$$ language plpgsql;

之後只要用下面的方式就可以刪除 like pattern 的資料表了

SELECT drop_tables('public', 'table_pattern%');

參考資料:

Comments

comments powered by Disqus