[postgresql] Postgres 데이터베이스에서 모든 테이블 자르기

plpgsql에서는 명시 적 커서가 거의 필요하지 않습니다. 루프 의 더 간단하고 빠른 암시 적 커서 를 사용하십시오 FOR.

참고 : 테이블 이름은 데이터베이스마다 고유하지 않으므로 테이블 이름을 스키마로 한정해야합니다. 또한 함수를 기본 스키마 ‘public’으로 제한합니다. 여러분의 필요에 적응하지만, 시스템 스키마를 제외해야 pg_*하고 information_schema.

이 기능에 매우주의 하십시오 . 그들은 당신의 데이터베이스를 핵화합니다. 어린이 안전 장치를 추가했습니다. 폭탄을 프라이밍하기 위해 RAISE NOTICE줄을 주석 처리하고 주석 EXECUTE을 제거하십시오 …

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      RAISE NOTICE '%',
      -- EXECUTE  -- dangerous, test before you execute!
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

format()Postgres 9.1 이상이 필요합니다. 이전 버전에서는 다음과 같이 쿼리 문자열을 연결합니다.

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

단일 명령, 루프 없음

TRUNCATE한 번에 여러 테이블을 사용할 수 있으므로 커서 나 루프가 전혀 필요하지 않습니다.

• 배열에서 테이블 이름 전달

모든 테이블 이름을 집계하고 단일 명령문을 실행하십시오. 더 간단하고 빠르게 :

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   RAISE NOTICE '%',
   -- EXECUTE  -- dangerous, test before you execute!
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$ LANGUAGE plpgsql;

요구:

SELECT truncate_tables('postgres');

정제 된 쿼리

기능이 필요하지 않습니다. Postgres 9.0 이상에서는 DO명령문 에서 동적 명령을 실행할 수 있습니다 . 그리고 Postgres 9.5 이상에서는 구문이 더 간단 할 수 있습니다.

DO
$func$
BEGIN
   RAISE NOTICE '%',
   -- EXECUTE
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$func$;

사이의 차이에 대해 pg_class, pg_tables그리고 information_schema.tables:

• 주어진 스키마에 테이블이 있는지 확인하는 방법

정보 regclass및 인용 테이블 이름 :

• PostgreSQL 함수 매개 변수로서의 테이블 이름

반복 사용

바닐라 구조와 모든 빈 테이블을 사용 하여 “템플릿”데이터베이스 (이름을 지정 my_template)를 작성하십시오. 그런 다음 DROP/CREATE DATABASE 주기를 수행하십시오.

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

이다 매우 빠른 , 파일 수준에서 포스트 그레스 복사 전체 구조 때문이다. 동시성 문제 또는 기타 오버 헤드가 느려지지 않습니다.

동시 연결로 인해 DB가 삭제되지 않으면 다음을 고려하십시오.

• 다른 사람이 연결될 수있는 동안 강제 삭제 DB

답변

#!/bin/bash
PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' ||  tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | 
tr "\\n" " " |
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"

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

        IF EXISTS (
            SELECT column_name
            FROM information_schema.columns
            WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
        ) THEN
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
        END IF;

    END LOOP;
END;
$$ LANGUAGE plpgsql;