How to List All Stored Procedures in Informix

How do I list all stored procedures in Informix?

Yes, there is. It's called sysprocedures. Try this to see all there's to see:

select * from sysprocedures

For more information on what detailed information is available, read about sysprocedures and sysprocbody and sysproccolumns.

How do I list all functions in Informix?

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.

Simple informix select stored procedure

Remove the foreach and the _ as first letter, also add a ; at the end of the select.

create procedure this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))

select * from table 
where column1 = this and
column2 = that and
column3 = other;

end procedure;

informix 12.10 in a procedure, putting multiple queries on conditions in the loop

Do you have INSERT INTO TEMP before each of the SELECT statements, or do you use UNION between SELECT statements, or what? To some extent, it doesn't matter what the answer is.

You can combine the first three queries easily enough:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE (Huge_Expression1) AND ((condition1) OR (condition2) OR (condition3))

You could combine all three sequences, using a notation like:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE ((Huge_Expression1) AND ((condition1) OR (condition2) OR (condition3))
        OR ((Huge_Expression2) AND ((condition1) OR (condition2) OR (condition3))
        OR ((Huge_Expression3) AND ((condition1) OR (condition2) OR (condition3))

You might be able to reduce that to:

INSERT INTO temp_table
    SELECT *
      FROM table1
     WHERE ((Huge_Expression1) OR (Huge_Expression2) OR (Huge_Expression3))
       AND ((condition1) OR (condition2) OR (condition3))

You might need to worry about duplicate rows.

If the huge conditions are stable, you might be able to use views:

CREATE VIEW table1_hc1
    AS (SELECT *
          FROM table1
         WHERE Huge_Expression1
       );

You can then write the first triplet of queries as:

INSERT INTO temp_table
    SELECT *
      FROM table1_hc1
     WHERE ((condition1) OR (condition2) OR (condition3))

You can do much the same job with the other two 'huge conditions'.

Alternatively, if you're using Informix 14.10 or later, you could use CTEs (common table expressions):

WITH
   table1_hc1 AS (SELECT * FROM table1 WHERE (Huge_Expression1)),
   table1_hc2 AS (SELECT * FROM table1 WHERE (Huge_Expression2)),
   table3_hc3 AS (SELECT * FROM table1 WHERE (Huge_Expression3))
SELECT *
  FROM table1_hc1 WHERE ((condition1) OR (condition2) OR (condition3))
 UNION
SELECT *
  FROM table1_hc2 WHERE ((condition1) OR (condition2) OR (condition3))
 UNION
SELECT *
  FROM table1_hc3 WHERE ((condition1) OR (condition2) OR (condition3))

Or as:

WITH
   table1_hc1 AS (SELECT * FROM table1 WHERE (Huge_Expression1)),
   table1_hc2 AS (SELECT * FROM table1 WHERE (Huge_Expression2)),
   table3_hc3 AS (SELECT * FROM table1 WHERE (Huge_Expression3))
SELECT *
  FROM (SELECT * FROM table1_hc1
         UNION
        SELECT * FROM table1_hc2
         UNION
        SELECT * FROM table1_hc3
       )
 WHERE ((condition1) OR (condition2) OR (condition3))

There may be other ways to rewrite it all if the three huge expressions have a lot in common.

I observe that your outline code does not show how the procedure parameter is used in the queries. That could affect the queries used. In particular, you can't embed the variable into the view definitions. It needn't affect the other rewrite significantly.

^{Just in case it isn't clear, none of the suggested SQL has been anywhere near a DBMS. There could be problems in the syntax that I've not spotted, but the idea should be clear enough, I hope. One key point is that the huge expressions are only written once each — that is important.}

Informix 12.1 How to choose a condition, based on condition in case clause

I think what you're trying to do might require:

INSERT INTO Table2
    SELECT column1, column2, column3, column4
      FROM Table1
     WHERE column1 NOT IN ('a', 'b') 
       AND ((input1  = 1 AND column2 IN ('a', 'b')) OR
            (input1  = 2 AND column3 IN ('x', 'y')) OR
            (input1 != 1 AND input1 != 2 AND column4 NOT IN ('1', '2'))
           )

Note that I've changed the target table for the INSERT to Table2; there isn't a good reason to insert rows into the table you're selecting the data from — the data is already there and duplicate rows only add confusion. However, if you insist that the target table should be the same as the source table and there is no primary key to stop you abusing it, then you can use INSERT INTO Table1 SELECT … FROM Table1 …, somewhat to my surprise (Informix 12.10.FC6 tested).

You could rewrite that as:

INSERT INTO Table2
    SELECT column1, column2, column3, column4
      FROM Table1
     WHERE column1 NOT IN ('a', 'b') 
       AND ((input1     IN (1)    AND column2     IN ('a', 'b')) OR
            (input1     IN (2)    AND column3     IN ('x', 'y')) OR
            (input1 NOT IN (1, 2) AND column4 NOT IN ('1', '2'))
           )

That emphasizes the similarities and differences in the major components of the OR'd terms.

Assembling an MCVE (Minimal, Complete, Verifiable Example — or MRE or whatever name SO now uses) or an SSCCE (Short, Self-Contained, Correct Example) from this produces:

DROP TABLE IF EXISTS Table1;
DROP TABLE IF EXISTS Table2;
DROP PROCEDURE IF EXISTS procedure1;

CREATE TEMP TABLE Table1
(
    column1 CHAR(1) NOT NULL,
    column2 CHAR(1) NOT NULL,
    column3 CHAR(1) NOT NULL,
    column4 CHAR(1) NOT NULL
);

INSERT INTO Table1 VALUES('a', 'a', 'x', '1');
INSERT INTO Table1 VALUES('b', 'b', 'y', '4');
INSERT INTO Table1 VALUES('c', 'c', 'y', '4');
INSERT INTO Table1 VALUES('d', 'c', 'y', '4');

CREATE TEMP TABLE Table2
(
    column1 CHAR(1) NOT NULL,
    column2 CHAR(1) NOT NULL,
    column3 CHAR(1) NOT NULL,
    column4 CHAR(1) NOT NULL
);

CREATE PROCEDURE procedure1(input1 SMALLINT)

    INSERT INTO Table2
        SELECT column1, column2, column3, column4
          FROM Table1
         WHERE column1 NOT IN ('a', 'b') 
           AND ((input1  = 1 AND column2 IN ('a', 'b')) OR
                (input1  = 2 AND column3 IN ('x', 'y')) OR
                (input1 != 1 AND input1 != 2 AND column4 NOT IN ('1', '2'))
               );

    INSERT INTO Table2
        SELECT column1, column2, column3, column4
          FROM Table1
         WHERE column1 NOT IN ('a', 'b') 
           AND ((input1     IN (1)    AND column2     IN ('a', 'b')) OR
                (input1     IN (2)    AND column3     IN ('x', 'y')) OR
                (input1 NOT IN (1, 2) AND column4 NOT IN ('1', '2'))
               );

END PROCEDURE;

EXECUTE PROCEDURE procedure1(1);
SELECT * FROM Table2;
DELETE FROM Table2;

EXECUTE PROCEDURE procedure1(2);
SELECT * FROM Table2;
DELETE FROM Table2;

EXECUTE PROCEDURE procedure1(3);
SELECT * FROM Table2;

Using my SQLCMD program (unrelated to, and older than, Microsoft's johnny-come-lately program of the same name), I get:

+ DROP TABLE IF EXISTS Table1;
+ DROP TABLE IF EXISTS Table2;
+ DROP PROCEDURE IF EXISTS procedure1;
+ CREATE TEMP TABLE Table1
(
    column1 CHAR(1) NOT NULL,
    column2 CHAR(1) NOT NULL,
    column3 CHAR(1) NOT NULL,
    column4 CHAR(1) NOT NULL
);
+ INSERT INTO Table1 VALUES('a', 'a', 'x', '1');
+ INSERT INTO Table1 VALUES('b', 'b', 'y', '4');
+ INSERT INTO Table1 VALUES('c', 'c', 'y', '4');
+ INSERT INTO Table1 VALUES('d', 'c', 'y', '4');
+ CREATE TEMP TABLE Table2
(
    column1 CHAR(1) NOT NULL,
    column2 CHAR(1) NOT NULL,
    column3 CHAR(1) NOT NULL,
    column4 CHAR(1) NOT NULL
);
+ CREATE PROCEDURE procedure1(input1 SMALLINT)

    INSERT INTO Table2
        SELECT column1, column2, column3, column4
          FROM Table1
         WHERE column1 NOT IN ('a','b') 
           AND ((input1  = 1 AND column2 IN ('a' ,'b')) OR
                (input1  = 2 AND column3 IN ('x' ,'y')) OR
                (input1 != 1 AND input1 != 2 AND column4 NOT IN ('1' ,'2'))
               );

    INSERT INTO Table2
        SELECT column1, column2, column3, column4
          FROM Table1
         WHERE column1 NOT IN ('a','b') 
           AND ((input1     IN (1)    AND column2     IN ('a' ,'b')) OR
                (input1     IN (2)    AND column3     IN ('x' ,'y')) OR
                (input1 NOT IN (1, 2) AND column4 NOT IN ('1' ,'2'))
               );

END PROCEDURE;
+ EXECUTE PROCEDURE procedure1(1);
+ SELECT * FROM Table2;
+ DELETE FROM Table2;
+ EXECUTE PROCEDURE procedure1(2);
+ SELECT * FROM Table2;
c|c|y|4
d|c|y|4
c|c|y|4
d|c|y|4
+ DELETE FROM Table2;
+ EXECUTE PROCEDURE procedure1(3);
+ SELECT * FROM Table2;
c|c|y|4
d|c|y|4
c|c|y|4
d|c|y|4

Since there are two INSERT statements, it is expected that Table2 will contain two copies of each row inserted. Of course, the table should have a primary key to prevent such abuses of relational theory.

You should also consider whether you'd be better off writing the 3 operations separately without mentioning input1 in the queries:

CREATE PROCEDURE procedure2(input1 SMALLINT)

    IF input1 = 1 THEN
        INSERT INTO Table2
            SELECT column1, column2, column3, column4
              FROM Table1
             WHERE column1 NOT IN ('a', 'b') 
               AND column2     IN ('a', 'b');
    ELIF input1 = 2 THEN
        INSERT INTO Table2
            SELECT column1, column2, column3, column4
              FROM Table1
             WHERE column1 NOT IN ('a', 'b') 
               AND column3     IN ('x', 'y');
    ELSE
        INSERT INTO Table2
            SELECT column1, column2, column3, column4
              FROM Table1
             WHERE column1 NOT IN ('a', 'b') 
               AND column4 NOT IN ('1', '2');
    END IF;

END PROCEDURE;

You might also consider using views or even the CTE (common table expression) feature if your query is massively big.

Informix SQL 11.5 creating stored procedure without temporary table

That "Only a DBA can create...." error is because you are trying to create a procedure belonging to "informix" and your user lacks DBA permission. Basically, you can't create objects that belong to someone else. If you have RESOURCE permission you should be able to create a SPL which belongs to you.
Have a read at (https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0147.htm) it describes the different database permissions and what you are allowed to do.

---

Related Topics