Retrieving PostgreSQL Overloaded Function Definitions

I thought for fun we’d look at a few different ways to retrieve the definitions of overloaded functions in PostgreSQL. It came up for me in my work today, so I wanted to pass it along.

We have two count_by_two() overloaded functions with one taking an Integer Array and the second a plain Integer as input parameters.

If you have a sufficient PostgreSQL IDE you can always view the Data Definition Language of the function with a right-click-something as shown here in Navicat PostgreSQL.

Short of a beefy IDE there are other ways to retrieve the definition of a particular overloaded function.

We can retrieve all by name…

…and that’s cool, but it’s not sufficient for those of who need to know we can get precisely what they want from a single query.

Here we’re using pg_get_functiondef() with the name of the function and include the parameter type. The tricky thing here is knowing the full representation of the type. _INT4, for instance, isn’t going to cut it.

Here’s a slightly longer way of going about it, where we query pg_proc and use pg_get_function_arguments() as a condition.