Topic
  • 7 replies
  • Latest Post - ‏2014-10-20T05:53:35Z by Fandino
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic function return a table (inside module)

‏2010-07-23T20:34:24Z |
Hi I tried to made a function that returns a table, i want the function inside a module.

create module mymodule
alter module mymodule publish
function f() returns table(c11 smallint)
begin atomic
return select edlevel from employee;
end

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0628N Multiple or conflicting keywords involving the "RETURNS TABLE"
clause are present. LINE NUMBER=5. SQLSTATE=42613

My question is, how to make a function (inside a module) that return a table, I've tried to google for it and didn't found any information. Thx
  • doole
    doole
    208 Posts

    Re: function return a table (inside module)

    ‏2010-07-26T14:23:18Z  
    Unfortunately, it's not supported.

    I was just looking at the 9.7 documentation and the way this restriction is documented is totally non-intuitive and requires very carefully reading ALTER MODULE, CREATE FUNCTION, COMPOUND SQL (compiled), and RETURN.

    I've discussed this with the developer responsible for modules, and he'll get the docs improved.

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: function return a table (inside module)

    ‏2011-02-04T10:22:02Z  
    Any idea when this feature will be available?

    Many thanks

    Victor
  • doole
    doole
    208 Posts

    Re: function return a table (inside module)

    ‏2011-02-07T14:07:10Z  
    Any idea when this feature will be available?

    Many thanks

    Victor
    This support is on our to-do list. Unfortunately we don't have an ETA for it yet.

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs
  • Fandino
    Fandino
    7 Posts

    Re: function return a table (inside module)

    ‏2014-01-13T11:17:42Z  
    • doole
    • ‏2011-02-07T14:07:10Z
    This support is on our to-do list. Unfortunately we don't have an ETA for it yet.

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs

    Almost two years after, the DB2 10.5 Information Center says regarding SQL table functions in modules:

    The module function definition can only specify the RETURNS TABLE clause when the SQL-routine-body is an compound SQL (compiled) statement that specifies NOT ATOMIC. The module function definition must not specify the SOURCE clause, the TEMPLATE clause, or the LANGUAGE OLEDEB option (SQLSTATE 42613).

    So, seems that table functions are now supported inside modules (I am using DB2 10.5 FP2). However, still fails. The question is how a compiled function can return a table.

    Is the documentation right?

    Thanks

  • Fandino
    Fandino
    7 Posts

    Re: function return a table (inside module)

    ‏2014-10-10T14:39:09Z  

    Hello

    Any update on this topic? DB2 10.5 Information Center still says table functions are possible in modules:

    The module function definition can only specify the RETURNS TABLE clause when the SQL-routine-body is an compound SQL (compiled) statement that specifies NOT ATOMIC. The module function definition must not specify the SOURCE clause, the TEMPLATE clause, or the LANGUAGE OLEDEB option (SQLSTATE 42613)

    Anyone has been able to create one of these functions in a module?

    Thanks

     

  • RickSwagerman
    RickSwagerman
    109 Posts

    Re: function return a table (inside module)

    ‏2014-10-15T17:46:42Z  
    • Fandino
    • ‏2014-10-10T14:39:09Z

    Hello

    Any update on this topic? DB2 10.5 Information Center still says table functions are possible in modules:

    The module function definition can only specify the RETURNS TABLE clause when the SQL-routine-body is an compound SQL (compiled) statement that specifies NOT ATOMIC. The module function definition must not specify the SOURCE clause, the TEMPLATE clause, or the LANGUAGE OLEDEB option (SQLSTATE 42613)

    Anyone has been able to create one of these functions in a module?

    Thanks

     

    Thanks for bringing this question up again.

    The reason for that change in text (that actually occurred in the 10.1 documentation) is a result of the addition of support for the PIPE statement. A table function that uses a PIPE statement to return the data for a table can be defined in a module.

    To see an example of a table function using the PIPE statement check out my April 2013 sqltips4DB2.com blog entry called "In the PIPE: Compiled SQL PL and PL/SQL Table Functions" which you can find at https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/in_the_pipe. You should be able to define the example function called next52 using an ALTER MODULE statement.

    At this time, there is still no support for a inlined table function using the RETURNS statement in a module.

    ...Rick

  • Fandino
    Fandino
    7 Posts

    Re: function return a table (inside module)

    ‏2014-10-20T05:53:35Z  

    Thanks for bringing this question up again.

    The reason for that change in text (that actually occurred in the 10.1 documentation) is a result of the addition of support for the PIPE statement. A table function that uses a PIPE statement to return the data for a table can be defined in a module.

    To see an example of a table function using the PIPE statement check out my April 2013 sqltips4DB2.com blog entry called "In the PIPE: Compiled SQL PL and PL/SQL Table Functions" which you can find at https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/in_the_pipe. You should be able to define the example function called next52 using an ALTER MODULE statement.

    At this time, there is still no support for a inlined table function using the RETURNS statement in a module.

    ...Rick

    Hello Rick

    thank you very much for the clarification. Module usage is quite limited without inlined table function support.

    Would you suggest to implement an inlined table functions as a pipelined function in a module until support for the first become available? Any considerable performance impact?

    Victor