How can I find the number of User-defined functions and Stored Procedures in PostgreSQL database?
Add a comment
|
3 Answers
It will work for you :
(editted by @a_horse_with_no_name 's warn)
SELECT count(*)
FROM information_schema.routines
WHERE routines.specific_schema='schema_name'
1 Comment
berkancetin
oohh you are so right. I am editing the answer, sorry.
This excludes metadata schemas of postgres:
select count(*) from information_schema.routines t where t.routine_schema not in ('pg_catalog', 'information_schema');
If you're only interested in the number of procedures then:
select count(*) from information_schema.routines t where t.routine_schema not in ('pg_catalog', 'information_schema') and t.routine_type = 'PROCEDURE';
Comments
If you want the list of procedures and functions with schema then you can use
SELECT specific_schema,routine_name, routine_type
FROM information_schema.routines
where specific_schema ='Schema_name'
and if you want just a count of procedures and functions then you can use
SELECT count(*)
FROM information_schema.routines
where specific_schema ='Schema_name'
Comments
