Last active
September 23, 2019 12:32
-
-
Save regedarek/cde4c124c6d2f5ce3e776bd26e1a466e to your computer and use it in GitHub Desktop.
Sprawdzenie ilości wszystkich tabel w bazie danych
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1.Wejście do bazy | |
psql kw_app_production | |
2. Funkcja do liczenia ilości rekordów | |
CREATE TYPE table_count AS (table_schema TEXT,table_name TEXT, num_rows INTEGER); | |
CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count AS ' | |
DECLARE | |
the_count RECORD; | |
t_name RECORD; | |
r table_count%ROWTYPE; | |
BEGIN | |
FOR t_name IN | |
SELECT table_schema,table_name | |
FROM information_schema.tables | |
where table_schema !=''pg_catalog'' | |
and table_schema !=''information_schema'' | |
ORDER BY 1,2 | |
LOOP | |
FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name | |
LOOP | |
END LOOP; | |
r.table_schema := t_name.table_schema; | |
r.table_name := t_name.table_name; | |
r.num_rows := the_count.count; | |
RETURN NEXT r; | |
END LOOP; | |
RETURN; | |
END; | |
' LANGUAGE plpgsql; | |
3. Wykonaj funkcję | |
select count_em_all(); | |
4. Wychodzisz z postgresa komendą: \q |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Najważniejsze tabele:
profiles, users, payments, membership_fees, contracts