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

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
Updated on 2011-02-07T14:07:10Z at 2011-02-07T14:07:10Z by doole
  • doole
    doole
    208 Posts
    ACCEPTED ANSWER

    Re: function return a table (inside module)

    ‏2010-07-26T14:23:18Z  in response to SystemAdmin
    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
    ACCEPTED ANSWER

    Re: function return a table (inside module)

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

    Many thanks

    Victor
    • doole
      doole
      208 Posts
      ACCEPTED ANSWER

      Re: function return a table (inside module)

      ‏2011-02-07T14:07:10Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: function return a table (inside module)

        ‏2014-01-13T11:17:42Z  in response to doole

        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
    ACCEPTED ANSWER

    Re: function return a table (inside module)

    ‏2014-10-10T14:39:09Z  in response to SystemAdmin

    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
      ACCEPTED ANSWER

      Re: function return a table (inside module)

      ‏2014-10-15T17:46:42Z  in response to Fandino

      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
        ACCEPTED ANSWER

        Re: function return a table (inside module)

        ‏2014-10-20T05:53:35Z  in response to RickSwagerman

        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