A Quick Way to Access Function Definition in PostgreSQL

When you are a new member in a data team like me, you need to work with the written procedures which has been saved as functions in Postgres. In the SQL Server you can easily use SP_HELPTEXT for store procedures. Store procedure is called function in Postgres.

Postgres has a table and its name is pg_proc which has some columns like id, name, source code and etc. But it doesn’t give us full text of function. Moreover, it has a function called pg_get_functiondef. when you pass oid of function which you are looking for, it returns full code inside of that.

Another problem that we would like to solve is to find forgotten names or long names of functions by typing a part of their name, so we need to filter the Postgres pg_proc table with LIKE operator in where clues.

To facilitate this operation, I decided to write this function which is enough to pass a part of function name to it and then it returns all similar functions as result.

CREATE FUNCTION func_text(func_name varchar)
  RETURNS TABLE (function varchar, full_text varchar) AS
$func$
BEGIN
   RETURN QUERY
    SELECT P.proname :: varchar, F.full_text :: varchar
    FROM
    (
      SELECT oid, proname
      FROM pg_proc
      WHERE lower(proname) like '%' || lower(func_name) || '%'
    ) AS P
    JOIN LATERAL
    (SELECT pg_get_functiondef(P.oid) AS full_text) AS F ON TRUE;
END
$func$ LANGUAGE plpgsql;

After creating this function you can call this function with table result like this query:

SELECT *
FROM func_text('func_text')

I pass the ‘func_text’ name as an input to it in order to check the result: