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)