How do I list all functions in Informix?

Question

I'm looking for a way to list all the user-defined functions in my database running on Informix.

Is there a table in the "informix".* database that lists functions along with detail information about them?

Note: this question is deliberately very similar to How do I list all stored procedures in Informix? but applies to functions and not procedures.

Answer 1

Using the sysprocedures catalog table you can filter for non procedureSPLs:

SELECT
    *
FROM
    sysprocedures
WHERE
    isproc = 'f';

SYSPROCEDURES

Review information about user-defined routines

However, I am not aware of an exact way to determine if a function is user defined.

You could look to the owner of the function, but if it was created with the user informix then it can no longer be distinguished from the system functions.

The other catalog tables related to functions also do not seem to contain enough information to help:

SYSPROCAUTH
SYSPROCBODY
SYSPROCCOLUMNS
SYSPROCPLAN
SYSROUTINELANGS

Reading a bit more into the documentation, it seems that a lower case value mode column indicates a protected (system) routine . So the routines that are of interest are the ones with modes O, R, D and T (all uppercase).

SELECT
    *
FROM
    sysprocedures
WHERE
    isproc = 'f'
    AND mode IN ('O', 'R', 'D', 'T');

However, this does not guarantee that the routine is user created. In the sysadmin there are routines marked with mode O that were created by the system.

Also if you, for example, in Informix version 12.10.FC10, use the a regex routine, the system automatically registers the regex datablade and the corresponding support routines are created with mode O .

EXECUTE FUNCTION regex_match ('Regex module' , '[Mm]odule|DataBlade');

SELECT * FROM sysprocedures WHERE procname= 'regex_match';

procname        regex_match
owner           informix
procid          568
mode            O
retsize         200
symsize         813
datasize        0
codesize        0
numargs         4
isproc          f
specificname
externalname    $INFORMIXDIR/extend/ifxregex.1.00/ifxregex.bld(ifxregex_match_c
                har)
paramstyle      I
langid          1
paramtypes      lvarchar,lvarchar,integer,integer
variant         f
client          f
handlesnulls    t
iterator        f
percallcost     1000
commutator
negator
selfunc
internal        f
class
stack
parallelizable  t
costfunc
selconst        0.00
collation       en_US.819
procflags       0

So, system created, but with mode O.