My question is similar to the headline of the question posted here However the answer there are different and much more complex than what I need.
My problem is simple… I have a function name say func1 and I would like to know if and where this function was implemented. Get it’s location, it’s arguments and it’s code. I’ve tried to build one using pg_proc :
select proname,prosrc from pg_proc where proname like 'func1';
How do I add schema name, function code (text) , and function argument list?
Another issue which is curious to me… Can I make it work if instead of func1 I’ll search lower? lower is a build in function which is not implemented by the user.
The question do not address my whole problem. as it doesn’t explain how do get in what schema the function is defined nor if it will work on functions which are build in SQL standard like lower(string).
Advertisement
Answer
Everything is there in the pg_proc table.
The source code for the function is in the prosrc field. The pronamespace field is the schema identifier (if you want its name, you need to join to the pg_namespace table).
Reconstructing the parameter list from the information in pg_proc is not at all straightforward, but SELECT pg_get_function_arguments(oid) FROM pg_proc will do it for you. There are a few related functions which you might find useful.
Yes, lower() is in pg_proc, as are most built-in functions (though a few, like CAST() and COALESCE(), are actually part of the grammar). But only a tiny fraction are written in SQL; for most, the prosrc field just gives the name of the function in the underlying C code.