Showing posts with label language. Show all posts
Showing posts with label language. Show all posts

Monday, March 25, 2013

Get List of Functions by LANGUAGE with Basic Statistics

Have you ever needed the ability to get a list of all your database functions by language and some basic stats on the lines of code? Here's a quick little query that works on 8.4. It should work for 8.4+.

SELECT
    lanname,
    count(lanname),
    max(qty),
    ceil(avg(qty))
FROM
(
    SELECT
        p.proname,
        l.lanname,
        (
            SELECT(Length(p.prosrc) - Length(REPLACE(p.prosrc, E'\n', ''))) / Length(E'\n')
        ) AS qty
    FROM pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
    JOIN pg_catalog.pg_language l ON p.prolang=l.oid
    WHERE
        nspname IN ('public', 'schema1', 'schema2', 'schema3')
) t1
GROUP BY
    lanname
;