CODEWITHSUNDEEP
informix
CodeMuncher
CodeMuncher

Reputation: 23

How to return tabled data from an procedure in an Informix database

Im trying to return multiple rows of data from an existing table (table1) from a procedure.

I'm using RazorSQL and Informix 12.x. With MSSQL this was simple but with Informix I can't seem to get it right.

drop function if exists test_multi;

create FUNCTION test_multi()
    RETURNING MULTISET(table1 NOT NULL);
    return select * from table1;
END FUNCTION;

EXECUTE FUNCTION test_multi();

I get : ERROR: A syntax error has occurred. I could use an assist. thanks

Upvotes: 2

Views: 1030

Answers (1)

Simon Riddle
Simon Riddle

Reputation: 1116

The syntax error most likely relates to the RETURNING statement where the element type(s) of the table need to be specified. In addition the results need to be returned as a MULTISET.

Here is an example:

CREATE TABLE t1(c1 int, c2 int, c3 char(8));
CREATE FUNCTION p1()
RETURNING MULTISET(ROW(c1 int, c2 int, c3 char(8)) NOT NULL);
RETURN MULTISET(select * from t1);
END FUNCTION;

And an example of using it with dbaccess:

INSERT INTO t1 VALUES(1, -1, "abc");
INSERT INTO t1 VALUES(2, -2, "defg");
EXECUTE FUNCTION p1();

(expression)  MULTISET{ROW(1          ,-1         ,'abc     '),ROW(2          ,
              -2         ,'defg    ')}

Upvotes: 2

Related Questions