Tronquer toutes les tables D'une base de données Postgres

les curseurs explicites sont rarement nécessaires en plpgsql. Il suffit d'utiliser le plus simple et plus rapide curseur implicite d'un FOR boucle:

Note: puisque les noms de table ne sont pas uniques par base de données, vous devez schema-qualifiez les noms de table pour être sûr. En outre, je limite la fonction au schéma par défaut 'public'. S'adapter à vos besoins, mais assurez-vous d'exclure le système schémas pg_* et information_schema .

Être très prudent avec ces fonctions. Ils nuke votre base de données. J'ai ajouté un dispositif de sécurité pour enfants. Commentaire de la RAISE NOTICE en ligne et retirez commentaires de EXECUTE pour amorcer la bombe ...

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() nécessite Postgres 9.1 ou plus. Dans les versions plus anciennes concaténer la chaîne de requête comme ceci:

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

Commande unique, sans boucle

puisque nous pouvons TRUNCATE plusieurs tables à la fois nous n'avons pas besoin de curseur ou de boucle du tout:

• passer les noms de table dans un tableau

regroupe tous les noms de table et exécute une instruction unique. Plus simple, plus rapide:

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;

appel:

SELECT truncate_tables('postgres');

requête raffinée

Vous n'avez même pas besoin d'une fonction. Dans Postgres 9.0+ vous pouvez exécuter des commandes dynamiques dans DO déclaration. Et dans Postgres 9.5+ la syntaxe peut être encore plus simple:

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$;

à propos de la différence entre pg_class , pg_tables et information_schema.tables :

• Comment vérifier si une table existe dans un schéma donné

à Propos de regclass et a cité les noms de table:

• nom de la Table comme PostgreSQL paramètre de la fonction

pour usage répété

il pourrait être plus simple et (beaucoup) plus rapide de créer un" modèle "de base de données (appelons-le my_template ) avec votre structure vanille et toutes les tables vides. Ensuite, passez par a DROP / CREATE DATABASE cycle:

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

C'est extrêmement fast , parce que Postgres copie toute la structure au niveau du fichier. Pas de problèmes de concurrence ou de frais généraux qui vous ralentissent.

si je dois faire cela, je vais simplement créer un schéma sql de db courante, puis drop & create db, puis Charger db avec le schéma sql.

ci-dessous les étapes à suivre:

1) Créer un dump Schema de la base de données ( --schema-only )

pg_dump mydb -s > schema.sql

2) la suppression de la base de données

drop database mydb;

3) Créer Une Base De Données

create database mydb;

4) Schéma D'Importation

psql mydb < schema.sql

dans ce cas, il serait probablement préférable d'avoir juste une base de données vide que vous utilisez comme un modèle et quand vous avez besoin de rafraîchir, laisser tomber la base de données existante et en créer une nouvelle à partir du modèle.

pourriez-vous utiliser dynamic SQL pour exécuter chaque instruction à tour de rôle? Vous devrez probablement écrire un script PL/pgSQL pour le faire.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (section 38.5.4. L'Exécution Dynamique Commandes)

vous pouvez le faire avec bash aussi:

#!/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 "{}"

vous aurez besoin d'ajuster les noms de schéma, les mots de passe et les noms d'utilisateur pour correspondre à vos schémas.

pour supprimer les données et préserver la table-structures dans pgAdmin vous pouvez faire:

• clic droit sur la base de données- > sauvegarde, sélectionnez "schéma seulement"
• supprimer la base de données
• créer une nouvelle base de données et l'appeler comme l'ancien
• faites un clic droit sur la nouvelle base de données- > restaurer - > sélectionnez la sauvegarde, sélectionnez "schéma seulement"

Nettoyage AUTO_INCREMENT version:

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;

les Gars le mieux et propre est :

1) Créer un dump Schema de la base de données (--schema-only) pg_dump mydb -s > schéma.sql

2) la suppression de la base de données drop database mydb;

3) Créer Une Base De Données créer la base de données mydb;

4) Schéma D'Importation psql mydb < schéma.sql

Son travail pour moi!

bonne journée. Hiram Walker