Informix

nested-group-icon.png

DB2

Expand all | Collapse all

question on sysprocedures table

  • 1.  question on sysprocedures table

    Posted Thu January 14, 2021 07:01 PM
    According to the manual (unless it's changed in 14.10), sysprocedures.isproc is the way you determine whether a routine is a function or a procedure.  The column is supposed to be 't' for procedure and 'f' for function.

    I'm working on something that will get a list of all functions and all procedures, and use dbschema save each to a text file.  I was wanting to segregate the procedures in one directory and the functions in another.  So, I have queries to build the list of procedures (sysprocedures.isproc = 't' and mode in ('D', 'O', 'R')) and another list of functions (sysprocedures.isproc = 'f' and mode in ('D', 'O', 'R')).  

    When I view the dbschema output of several of the functions, they have 'create procedure ...' rather than 'create function ...'.  Yes, these "procedures" do return values, just like a function does, but I was hoping to be able to have all of the 'create procedure ...' routines in the directory with all of the procedures.

    I can work out other ways to achieve my goals, but I was just wondering - is there any way to tell from sysprocedures whether the routine was created with 'create procedure' vs 'create function'?

    Thanks.

    ------------------------------
    Mark Collins
    ------------------------------


  • 2.  RE: question on sysprocedures table

    Posted Thu January 14, 2021 07:33 PM
    Mark:

    Unfortunately if the proc returns something it us a function by definition.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: question on sysprocedures table

    Posted Thu January 14, 2021 07:43 PM
    Art,

    Thanks for the quick response.  I figured that would be the case, and worked out an alternate method to achieve the desired result.



    Mark

    ------------------------------
    Mark Collins
    ------------------------------