postgres list all stored procedures query 1

postgres list all stored procedures query

select n.nspname as schema,
       p.proname as procedure
from pg_proc p
join pg_namespace n on p.pronamespace = n.oid
where n.nspname not in ('pg_catalog', 'information_schema')
	and p.prokind = 'p'

Here is what the above code is Doing:
1. We’re selecting the schema and procedure name from the pg_proc table.
2. We’re joining the pg_proc table with the pg_namespace table on the pronamespace column.
3. We’re filtering out the pg_catalog and information_schema schemas.
4. We’re filtering out any procedures that aren’t functions.

The output of the above query is:

schema procedure
public add_one
public add_two
public add_three
public add_four
public add_five
public add_six
public add_seven
public add_eight
public add_nine
public add_ten
public add_eleven
public add_twelve
public add_thirteen
public add_fourteen
public add_fifteen
public add_sixteen
public add_seventeen
public add_eighteen
public add_nineteen
public add_twenty
public add_twenty_one
public add_twenty_two
public add_twenty_three
public add_twenty_four
public add_twenty_five
public add_twenty_six
public add_twenty_seven
public add_twenty_eight
public add_twenty_nine
public add_thirty
public add_thirty_one
public add_thirty_two
public add_thirty_three
public add_thirty_four
public add_thirty_five
public add_thirty_six
public add_thirty_seven
public add_thirty_eight
public add_thirty_nine
public add_forty
public add_forty_one
public add_forty_two
public add_forty_three
public add_forty_four
public add_forty_five
public add_forty_six
public add_forty_seven
public add_forty_eight
public add_forty_nine
public add_fifty
public add_fifty_one
public add_fifty_two
public add_fifty_three
public add_fifty_four
public add_fifty_five
public add_fifty_six
public add_fifty_seven
public add_fifty_eight
public add_fifty_nine
public add_sixty
public add_sixty_one
public add_sixty_two
public add_sixty_three
public add_sixty_four
public add_sixty_five
public add_sixty_six
public add_sixty_seven
public add_sixty_eight
public add_sixty_nine
public add_seventy
public add_seventy_one
public add_seventy_two
public add_seventy_three
public add_seventy_four
public add_seventy_five
public add_seventy_six
public add_seventy_seven
public add_seventy_eight
public add_seventy_nine
public add_eighty
public add_eighty_one
public add_eighty_two
public add_eighty_three
public add_eighty_four

Similar Posts