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.

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.

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.

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

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...

That is not much of a problem because you can simply (re)name the columns of every table that occurs in the FROM clause:
or this for base tables, views, and nicknames:
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.

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.

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.