Metadonnées de PostgreSQL

Version de PostgreSQL

SELECT version();

Liste des tables

SELECT t.tablename
FROM pg_tables t
WHERE t.schemaname = 'public'

Supprimer toutes les tables

CREATE OR REPLACE FUNCTION drop_tables(schema_name text)
RETURNS void AS
$$
DECLARE r record;
BEGIN
  FOR r IN SELECT quote_ident(t.tablename) AS table_name
           FROM pg_tables t
           WHERE t.schemaname = schema_name
  LOOP
    RAISE INFO 'Dropping table %.%', schema_name, r.tablename;
    EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', quote_ident(schema_name), r.table_name);
  END LOOP;
END
$$ LANGUAGE plpgsql;

Puis

SELECT drop_tables('public');

Liste des vues

SELECT v.viewname
FROM pg_views v
WHERE v.schemaname = 'public'

Liste des colonnes

Pour avoir la liste des colonnes d’une table, avec le type de chaque colonne, on utilise la table information_schema.columns.

SELECT *
FROM information_schema.columns
WHERE table_name = 'sw_users';

Liste des fonctions

SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'FUNCTION'
  AND routine_schema = 'public';

Dans une version plus détaillée:

SELECT p.oid::regprocedure
FROM pg_catalog.pg_proc p
    JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind = 'f'
  AND n.nspname = 'public';

Supprimer toutes les fonctions

CREATE OR REPLACE FUNCTION drop_functions(schema_name text)
  RETURNS void AS
$$
DECLARE r record;
BEGIN
  FOR r IN SELECT p.oid::regprocedure as qualified_name
           FROM pg_catalog.pg_proc p
               JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
           WHERE p.prokind = 'f'
             AND n.nspname = schema_name
  LOOP
    RAISE INFO 'Dropping function %.%', schema_name, r.qualified_name;
    EXECUTE 'DROP FUNCTION IF EXISTS ' || schema_name || '.' || r.qualified_name;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Puis

SELECT drop_functions('public');

Liste des clés étrangères

SELECT conname AS constraint_name,
       conrelid::regclass AS source_table,
       confrelid::regclass AS target_table,
       pg_get_constraintdef(oid) AS definition
FROM   pg_constraint
WHERE  contype = 'f'
  AND  connamespace = 'public'::regnamespace
ORDER  BY conrelid::regclass::text, contype DESC;

Références