A function that shows schema size in PostgreSQL

Thanks to Emanuel Calvo for publishing this procedure.

I put it in a script:

SET search_path TO public;
SHOW search_path;
CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS $$
SELECT SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT FROM pg_tables WHERE schemaname = $1
$$ LANGUAGE SQL;

and then executed it:

]$ psql
psql (11.7)
Type "help" for help.


postgres=# \i cre_funk.sql
SET
 search_path
-------------
 public
(1 row)

CREATE FUNCTION

When this is done, it can be executed in any database part of the postgres instance, as long as you connect to the right one first:

postgres=# \connect sales
You are now connected to database "sales" as user "postgres".
sales=# select pg_size_pretty(pg_schema_size('sales_archive'));
 pg_size_pretty
----------------
 28 MB
(1 row)

Av en eller annen grunn får vi nå: HINT: No function matches the given name and argument types. You might need to add explicit type casts. når vi kjører den i postgres versjoner > 11