PostgreSQL Procedural Langauage - plpgsql

Run the command to install procedural languages or you will get:
ERROR: language "plpgsql" does not exist

Connect as the superuser

bash-3.2$

Run the command
bash-3.2$ createlang -d movecms1 plpgsql

Login as regular user
bash-3.2$ psql -U qedit -d movecms1

Now create the type to return the values
CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER);

Now create the function

 

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 c.relname
FROM
pg_catalog.pg_class c
LEFT JOIN
pg_namespace n
ON
n.oid = c.relnamespace
WHERE
c.relkind = ''r''
AND
n.nspname = ''public''
ORDER BY 1
LOOP
-- The next 3 lines are a godawful hack. :P
FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname
LOOP
END LOOP;
r.table_name := t_name.relname;
r.num_rows := the_count.count;
RETURN NEXT r;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;

COMMENT ON FUNCTION count_em_all () IS 'Spits out all tables in the public schema and the exact row counts for each.';

To call the function use this select:

=> select * from count_em_all();
table_name | num_rows
------------+----------
(0 rows)

Subject