3

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

Community
  • 1
  • 1
John
  • 1,724
  • 6
  • 25
  • 48
  • 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 Jun 14 '15 at 06:44
  • Google didn't answer it.. not even close nor any other questions on stackoverflow. – John Jun 14 '15 at 06: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 Jun 14 '15 at 06: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 Jun 14 '15 at 07: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 Jun 14 '15 at 07:09
  • no.. it's really that simple... i want to give a function name and get the function code and in what schema it's located. My other question was if it will work on function that i didn't implemented but they are PostgreSQL standard like lower(str). I don't want to dump ALL functions. I want to get information about SPECIFIC function. – John Jun 14 '15 at 07:12
  • And this must be accomplished programmatically (using SQL) instead of just using an IDE or database app? In other words, you're creating a stored procedure that takes a varchar function name and returns a result set with the function details, and you're calling that stored procedure from some other language? – Alvin Thompson Jun 14 '15 at 07:19
  • And to answer the second part of your question, I doubt it would work with built-in functions since I imagine they're precompiled and highly optimized. – Alvin Thompson Jun 14 '15 at 07:21
  • What is wrong with the query you tried? – Jakub Kania Jun 14 '15 at 07:25
  • possible duplicate of [How to display the function, procedure, triggers source code in postgresql?](http://stackoverflow.com/questions/6898453/how-to-display-the-function-procedure-triggers-source-code-in-postgresql) – Jakub Kania Jun 14 '15 at 07:26
  • Yes it must be an SQL query. I'm doing it for my own studying, it won't be integrated into a real system. This is a self learning question. It's oki to suggest other ideas to get the same result but I still want to understand if it's doable as a SQL query. My gut says it's possible as the information should be available in the information schema. I just don't know how to access it correctly so I asked my question here. – John Jun 14 '15 at 07:27
  • There is nothing wrong with the query i tried. It just didnt give me all the information I seek. for example schema name isn't in pg_proc. I wrote it in my question :\ I thank you for trying to help but It feels like you are trying to find a deeper meaning in something that is simply a postgresql noobie question. – John Jun 14 '15 at 07:29
  • @Jakub Kania it's only a part of what I was looking for. it doesn't answer my whole question regarding the schema name and if it's possible to get information about build in function like lower(string) – John Jun 14 '15 at 07:30

1 Answers1

4

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.

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63