How to find if a function exists in PostgreSQL and where?

Question

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).

I'm not the one who down voted you but what you're asking is ugly, ugly, ugly! Almost certainly there's a better way to tackle whatever problem you're trying to solve. — Alvin Thompson
– Alvin Thompson, Commented Jun 14, 2015 at 6:44
Google didn't answer it.. not even close nor any other questions on stackoverflow. — John
– John, Commented Jun 14, 2015 at 6:46
I hate to sound flippant, but if no one has ever tried to do something even remotely similar, that should tell you something. What is the original problem you're trying to solve? — Alvin Thompson
– Alvin Thompson, Commented Jun 14, 2015 at 6:59
This is the original problem i'm trying to solve. I'm studying an existing DB with hundreds of functions and it will be easier to navigate if I can get the function code from it's name. The fact that no one has tried to do something similar isn't saying anything - most of the questions here are things that no one has done before, if it was done then you would have find it in search and never ask the questions. — John
– John, Commented Jun 14, 2015 at 7:00
I'm not sure what you mean by "navigate". Do you want to accomplish something similar to what an IDE or database app does and programmatically list all functions so you can see what they do? If true, you may be better off using a database app that can simply dump all the functions/stored procedures as SQL. No point reinventing the wheel. — Alvin Thompson
– Alvin Thompson, Commented Jun 14, 2015 at 7:09

Nick Barnes · Accepted Answer · 2021-03-17 11:29:09Z

5

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.

edited Mar 17, 2021 at 11:29

answered Jun 14, 2015 at 10:30

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to find if a function exists in PostgreSQL and where?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related