Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    5

    Unanswered: Generic external UDF function written in C

    Is there a way to develop an external UDF template such that it takes a (IN parameter) varchar and returns a table. This assumes the varchar is in the form of a select statement. The returned table may have to be altered to accommodate the columns in the select statement.

    For example:

    In param = "select colA from tableA where id = 7"

    Returning table would have a single column of data type that resembles col A.

    The goal is to make a simple UDF that returns the result set from the statement that it is passed.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    No .. When defining a table UDF, you have to define the output table structure , eg .
    CREATE FUNCTION MYFUNC()
    RETURNS TABLE (TIMERECEIVED DATE,
    SUBJECT VARCHAR(15),
    SIZE INTEGER,
    TEXT VARCHAR(30)

    Is there a reason you do not want to use a Stored Proc ???

    HTH

    Sathyaram

    PS: This is your third post with the same question. Please do not start another one.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2007
    Posts
    5

    Question Defining output structure

    Maybe I was not clear with my question...

    Lets assume the returned table is a single column for discussion purposes.

    So, is it possible to return the result set from a query that is passed as varchar?

    BTW, this is my first post on this subject. Could you direct us to the 2 other posts that have been opened? And hopefully answered.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your question is : Can you pass a query as input to a UDF and get the result set of the query as output of table function.

    The simple answer is Yes. (Disclaimer: AFAIK … I have not tried defining external table function myself)

    There are a few more points to remember though:

    a) your resultset column names will be as per your UDF definition and NOT as per your input query.
    b) The number and datatypes of the resultset columns is fixed at the UDF definition time.

    As example for above ,
    If you define your UDF :

    CREATE FUNCTION MYFUNC(QRY VARCHAR(1000))
    RETURNS TABLE (TIMERECEIVED DATE,
    SUBJECT VARCHAR(15)

    )


    Then your call,
    Select * from table (‘myfunc(select date(timestampcol) as d,namecol as n from employee’) as udf

    Then your resultset will have the column names TIMERECEIVED and SUBJECT and not D and N.

    You call cannot be :
    Select * from table (‘myfunc(select date(timestampcol) as d,namecol as n , salarycol from employee’) as udf


    Have a read through


    http://publib.boulder.ibm.com/infoce...n/r0000917.htm
    PS: My apologies, your earlier post had a slightly different question .. I misundersood
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sathyaram_s
    Your question is : Can you pass a query as input to a UDF and get the result set of the query as output of table function.

    The simple answer is Yes. (Disclaimer: AFAIK … I have not tried defining external table function myself)
    You can remove the AFAIK. It is definitively possible. As we explained extensively before (primarily in the DB2 newsgroup), there are a lot of examples shipped with DB2 that show how to implement table functions. You have to declare and open a cursor inside the table function in the OPEN call, and each FETCH call to the UDF translates to a FETCH on the cursor. When DB2 makes the SQLUDF_TF_CLOSE call to the table UDF, you close the cursor. It's simple as that - and exactly the same you would have to do when implementing a ResultSet class in Java if you were to write a JDBC driver.

    You (the OP) should simply give it a try and if it does not work, ask specific questions...

    There are a few more points to remember though:
    a) your resultset column names will be as per your UDF definition and NOT as per your input query.
    That is not much of a problem because you can simply (re)name the columns of every table that occurs in the FROM clause:
    Code:
    SELECT ... FROM TABLE ( func(...) ) AS func_result_table(a, b, c)
    or this for base tables, views, and nicknames:
    Code:
    SELECT ... FROM t1 AS t(a, b, c)
    b) The number and datatypes of the resultset columns is fixed at the UDF definition time.

    As example for above , If you define your UDF :
    Code:
    CREATE FUNCTION MYFUNC(QRY VARCHAR(1000))
    RETURNS TABLE (TIMERECEIVED DATE,
    SUBJECT VARCHAR(15))
    Then your call,
    Code:
    Select * from table (‘myfunc(select date(timestampcol) as d,namecol as n from employee’) as udf
    Then your resultset will have the column names TIMERECEIVED and SUBJECT and not D and N.

    You call cannot be :
    Code:
    Select * from table (‘myfunc(select date(timestampcol) as d,namecol as n , salarycol from employee’) as udf
    Actually, you can provide such a query (with corrected positioning of the single-quotes). As long as the table function makes sure that only two columns are returned to DB2 and the third column is ignored. The only other criteria is that the data types match with whatever was defined at CREATE FUNCTION time, as you already pointed out.
    Last edited by stolze; 03-31-07 at 14:06.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2007
    Posts
    5

    Function error

    Sathyaram:

    I took your suggestion - trying to learn UDFs.

    Modifying the function slightly:

    CREATE FUNCTION userid.MYFUNC(QRY VARCHAR(1000))
    RETURNS TABLE (med varchar(128)
    )

    Gave me an error:

    SQL0491N The definition of routine userid.myfunc must have a returns clause, and one of: the external clause (with other required keywords); the RETURN statement; or the source clause. SQLSTATE = 42601

    I assume this is because we are not actually returning anything.

    Therefore modified the query as such - - just to return a value:

    CREATE FUNCTION userid.MYFUNC(QRY VARCHAR(1000))
    RETURNS TABLE (result varchar(128)
    )

    begin atomic
    return
    select med from systools.policy;
    end
    ;

    However - i am looking to execute qry.

    The method to call the function would be from another function or procedure. In fact, this could be a procedure as well. Select * from myfunc (squery); - OR - Call myproc (squery) to return a list of varchar

    At this point and time, my original assignment is completed. I am just seeing if DB2 has the capabilities within a SQL UDF or external C UDF. This is now more of a personal R&D objective - will try Knut's cursor suggestion and post any errors that I run into.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, the SQL0491 error is because you did not provide a valid CREATE FUNCTION statement. Have a look at the syntax diagram in the manual. You will see that either an EXTERNAL NAME clause (for external table functions), a RETURN clause (for SQL-bodied functions), or a SOURCE clause (for sourced table functions) is mandatory.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •