Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2016
    Posts
    13

    Answered: Getting function text - Equivalent of Help_text in SQL Server

    Hello,

    I would like to know if there is any sys tables in postgres I can take a look to get the full function definition and body.
    I know there is a table called pg_proc, but it doesnt look like there is any column that gives me the entire function.

    Mostly I want the input and output parameters information along with any default values on input parameters.


    Thanks in advance!

  2. Best Answer
    Posted by shammat

    "
    Code:
    select nsp.nspname, p.proname, pg_get_function_arguments(p.oid), pg_get_functiondef(p.oid)
    from pg_proc p
       join pg_namespace nsp on p.pronamespace = nsp.oid
    where proname = 'test'
      and nsp.nspname = 'myschema';
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    To get the complete function use:
    Code:
    select pg_get_functiondef(to_regprocedure('function_name'))
    To get the function definition of an overloaded function, you have to specify the signature, e.g:
    Code:
    select pg_get_functiondef(to_regprocedure('function_name()'));
    select pg_get_functiondef(to_regprocedure('function_name(integer)'));
    If you are not on 9.4 or 9.5 you need to cast the name to the proper oid: 'function_name'::regproc.
    Note that this does not work for overloaded functions.

    To get only the parameters:
    Code:
    select pg_get_function_arguments(to_regprocedure('function_name'))
    More functions and details are in the manual: http://www.postgresql.org/docs/curre...-CATALOG-TABLE
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jan 2016
    Posts
    13
    Thanks for the quick response.

    So I tried this query
    select pg_get_functiondef(to_regprocedure('myschema.test' ))
    OR
    select to_regprocedure('myschema.test')

    ERROR: expected a left parenthesis
    ********** Error **********

    ERROR: expected a left parenthesis
    SQL state: 22P02

    Am I missing anything?

    This is how the test function looks

    CREATE OR REPLACE FUNCTION myschema.test(in_json_txt json)
    RETURNS void AS
    $BODY$

    $BODY$

  5. #4
    Join Date
    Jan 2016
    Posts
    13
    So I was able to get this working for funtion without parameters but if there is a function with any parameter then it doesnt return anything.

  6. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You need to include the parameter:

    Code:
    select pg_get_functiondef(to_regprocedure('myschema.test(json)'))
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #6
    Join Date
    Jan 2016
    Posts
    13
    Thanks shammat!

    Is it possible to find this just based off the function name?

  8. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select nsp.nspname, p.proname, pg_get_function_arguments(p.oid), pg_get_functiondef(p.oid)
    from pg_proc p
       join pg_namespace nsp on p.pronamespace = nsp.oid
    where proname = 'test'
      and nsp.nspname = 'myschema';
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #8
    Join Date
    Jan 2016
    Posts
    13
    Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •