SQL prefixes add prefix to create SQL table function return list

[m-tyler]

One of the more tedious tasks when creating table functions is getting a list of columns with data types from the query result.

This extension has features to create RPG DS layouts, it makes sense to me to allow for creating SQL table function return structures. This could even be extended to also develop SQL procedure parameters from a query.

[SanjulaGanepola]

@m-tyler Can you share an example of what you would like to be generated given an example query?

[m-tyler]

Sample table function

create or replace MyFunction 
(
  Parm1 char(5),
  Parm2 date default null
)
returns table (
  Column1 char(10),
  Column2 dec(5,2),
  ...
)
begin
return select Column1, Column2 from TheTable where Column5 = Parm1;
end;

Essentially the same pattern used to generate the RPG DS but mainly replacing dcl-ds row_t qualified template;...end-ds; with returns (...) and alter different data type terminology (packed(5:0) becomes decimal(5,0)).

I typically run the statement I am going to use in my table function in the ACS RSS tool. Look at the result details and then use the copy column name with data . However, if I am not using the ACS tool then I don't have simpler way in the Code DB2 product to get the same results.

Hope this helps

[SanjulaGanepola]

@m-tyler I have a PR open to add support for this here: #459 (comment).

Rather than just generate the return list, it creates the skeleton for a UDTF (check out the screencap in the PR for an example). Does this work?

[m-tyler]

I am fine with a basic UDTF being generated as I can just copy the bits I need. Keeping that skeleton might help others start a UDTF where they might have found it difficult before.

Looking over the PR I like the results so far.

Thanks!