Selecting from Stored Procedures and Storing SP Results into a Table

Question

I'm trying to build a couple of tables based upon the results of a few Stored procedures inside an Informix Database, that I cannot edit, and the parameter of the second SP requires values from another (will be called a few times). I have figured out what to do, but am having trouble executing it, predominantly because I cannot save my DB into a Temp table so I can then modify it.

If someone could give me some pointers, that would be great.

Some examples of things I've tried

call <stored procedure(...)> INTO TEMP db

and

create temp table name(...)
INSERT INTO namefrom storedprocedure()

and

create temp table name(...)
Select INTO name from storedprocedure()

Kind Regards, Fox

Answer 1

You can use:

SELECT * FROM TABLE(stored_procedure()) INTO TEMP p32;

This inserts the rows returned by the stored procedure into the temporary table p32.

There may be other ways to do it, but this seemed to work and is fairly straight-forward to understand.

^{(Tested: Informix 12.10.FC6 on a Mac running macOS 10.14.6 Mojave.)}

Test schema

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8, 4) NOT NULL,
    pt_period       SMALLINT NOT NULL
                    CHECK (pt_period BETWEEN 1 AND 7),
    pt_group        CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (pt_group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
                                        '7', '8', '9', '10', '11', '12', '13',
                                        '14', '15', '16', '17', '18')),
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 1, '1',  'Y');
INSERT INTO elements VALUES(  2, 'He',  'Helium',          4.0026, 1, '18', 'Y');
INSERT INTO elements VALUES(  3, 'Li',  'Lithium',         6.9410, 2, '1',  'Y');
INSERT INTO elements VALUES(  4, 'Be',  'Beryllium',       9.0122, 2, '2',  'Y');
INSERT INTO elements VALUES(  5, 'B',   'Boron',          10.8110, 2, '13', 'Y');
INSERT INTO elements VALUES(  6, 'C',   'Carbon',         12.0110, 2, '14', 'Y');
INSERT INTO elements VALUES(  7, 'N',   'Nitrogen',       14.0070, 2, '15', 'Y');
INSERT INTO elements VALUES(  8, 'O',   'Oxygen',         15.9990, 2, '16', 'Y');
INSERT INTO elements VALUES(  9, 'F',   'Fluorine',       18.9980, 2, '17', 'Y');
INSERT INTO elements VALUES( 10, 'Ne',  'Neon',           20.1800, 2, '18', 'Y');
INSERT INTO elements VALUES( 11, 'Na',  'Sodium',         22.9900, 3, '1',  'Y');
INSERT INTO elements VALUES( 12, 'Mg',  'Magnesium',      24.3050, 3, '2',  'Y');
INSERT INTO elements VALUES( 13, 'Al',  'Aluminium',      26.9820, 3, '13', 'Y');
INSERT INTO elements VALUES( 14, 'Si',  'Silicon',        28.0860, 3, '14', 'Y');
INSERT INTO elements VALUES( 15, 'P',   'Phosphorus',     30.9740, 3, '15', 'Y');
INSERT INTO elements VALUES( 16, 'S',   'Sulphur',        32.0650, 3, '16', 'Y');
INSERT INTO elements VALUES( 17, 'Cl',  'Chlorine',       35.4530, 3, '17', 'Y');
INSERT INTO elements VALUES( 18, 'Ar',  'Argon',          39.9480, 3, '18', 'Y');

The full table has 118 elements listed, of course.

Sample procedure

DROP PROCEDURE IF EXISTS lightweights;

CREATE PROCEDURE lightweights()
    RETURNING INTEGER AS atomic_number, VARCHAR(3) AS symbol, VARCHAR(20) AS name;

    DEFINE num INTEGER;
    DEFINE sym VARCHAR(3);
    DEFINE nam VARCHAR(20);

    FOREACH SELECT e.atomic_number, e.symbol, e.name
        INTO num, sym, nam
        FROM elements AS e
        WHERE atomic_number < 10
        RETURN num, sym, nam WITH RESUME;
    END FOREACH;

END PROCEDURE;

Example execution of the procedure to create a temp table

$ sqlcmd -d stores
SQL[3368]: select * from table(lightweights());
1|H  |Hydrogen            
2|He |Helium              
3|Li |Lithium             
4|Be |Beryllium           
5|B  |Boron               
6|C  |Carbon              
7|N  |Nitrogen            
8|O  |Oxygen              
9|F  |Fluorine            
SQL[3368]: select * from table(lightweights()) into temp p32;;
SQL[3369]: select * from p32;
1|H  |Hydrogen            
2|He |Helium              
3|Li |Lithium             
4|Be |Beryllium           
5|B  |Boron               
6|C  |Carbon              
7|N  |Nitrogen            
8|O  |Oxygen              
9|F  |Fluorine            
SQL[3370]: q;
$

Note that in sufficiently recent versions of Informix, you can replace TEMP with STANDARD or RAW to create a permanent table instead of a temporary table (and deleting TEMP is equivalent to replacing it with STANDARD). See SELECT statement > INTO table clauses.

Answer 2

Here's an example of a user-defined function (aka stored procedure) returning multiple values that are inserted into a temporary table. It uses the stores_demo database.

create function func1()
returning char(15), char(15);
define v_fname, v_lname char(15);

foreach select fname, lname into v_fname, v_lname from customer
  return v_fname, v_lname with resume;
end foreach
end function;

create temp table tt1(fname char(15), lname char(15));
insert into tt1 execute function func1();