Re: use stored proc that return multiple columns in a query

brenddie pisze: > On Oct 6, 7:58 pm, "Dirk Gunsthďż˝vel" <d...@guncon.de> wrote: >> Hi there, >> >> Arts remark and warning is correct. Still there are situations >> where you "inherited" such a procedure, cant redo the >> whole design and must make it usable in selects. Often >> wrappers for the individual values are ok - i.e. if performance >> is not a big issue. If it is, there is no easy way. >> >> The following works for me: >> >> Lets assume your procedure name is myproc and it returns >> two values a of type a_type and b of type b_type. Lets also assume >> it has one input parameter inp of some type. You want to call >> the procedure in a select on table mytable on col0. Some other >> column col1 of this table is also to be selected. >> >> Apparently >> SELECT myproc(col0), col1 FROM mytable WHERE ... >> cant work as you know. >> >> Now the following steps will get you what you want: >> >> 1. Define a rowtype myrowtype containing fields a of a_type >> and b of b_type >> >> 2. Write a wrapper mywrapper for your stored procedure >> returning myrowtype by calling the original procedure and setting >> the values a and b in the myrowtype object being returned. >> >> 3. This wrapper procedure can now be used in selects: >> SELECT mywrapper(col0), col1 FROM mytable >> will work. But: most clients will have problems in getting >> a rowtype value from the resultset. >> >> 4. Therefore, we rewrite the result to a simple resultset: >> SELECT val.a, val.b, col1 FROM >> TABLE ( >> MULTISET( >> SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE... >> ) >> )>> >> It is a good idea to hide this statement in a view. >> >> Hope this helps. >> >> Regards, >> Dirk >> > > > This sounds exactly like what Im looking for. I created a custom data > type with the required fields. Then tried to create a wrapper but I'm > stuck trying to return the custom data type. > > Following my previous example, > > Created a data type > > create row type 'informix'.product_info_type ( > price MONEY(12,2), > description VARCHAR(100)) > > Then tried to create a wrapper around get_product_info() > > CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER ) > RETURNING product_info_type;> DEFINE product_info_type type product_info_type; > > CALL get_product_info(product_id) > RETURNING product_info_type.price, product_info_type.description; > > RETURN product_info_type; > > END PROCEDURE; > > But I'm stuck trying to figure out the correct usage of row types. I > haven't worked with custom data types/rows before and cant find the > correct way of declaring and returning row types. This must be basic > stuff but I cant find any examples on the documentation or anywhere on > the internet. > > How do you define, assign and return row types ? > > > Thanks Alternative approach is to use output parameters on condition that one return record is enough (your example suggest that). My example: Create Procedure get_product_info(product_id integer, out product_price money(10,2), out product_desc Char(20)) returning int; ... setting product_price and product_desc ... return 1; end procedure; Then you can do following select: select f1, f2 from products where get_product_info(product_id, f1#money(10,2), f2#Char(20))=1 I don't remember if you should specify Char(20) or just Char. You have an idea, remaining details are in documentation. Regards, Robsosno