Hello,

I have encountered the following problem while migrating some functions from postgres to db2.

I want to write a function like this:

create function myfunc( tablename varchar(30))
RETURNS varchar(4000) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
begin atomic

for row as
select * from $tablename$
do
...
end for;

return sth.

end;

My problem is, that I have no idea how to use the parameter "tablename" in this sql statement you find in the for clause.

In the PostGres Function I built a string representing my select statement and executed it to iterate over the result.

Guess it's easy for someone who knows DB2 much better.

Any ideas?

Have a nice weekend.

Marcus

I don't think you can do it in an SQL function, but you can use dynamic SQL in a stored procedure, which in turn can be called from the function. Search documentation for "dynamic sql".

You can try it as follows.

create function func1(tablename varchar(50))
return........
begin atomic
declare cmd varchar(200);

SET cmd = 'SELECT * FROM' ||tablename;
PREPARE stmt from cmd;
DECLARE c1 cursor for stmt;
open c1;
fetch.........

END@

Thanks,
Madhavi

Clearly, you haven't tried it yourself. Cursors (and related statements) are not supported in user-defined SQL functions.

yea, you cannot use cursors or prepare statements in a CLP code, but you can use them in SQC(Embedded C).

you can refer to udfcli.sqc sample in sqllib/samples/c directory.

Thanks,
Madhavi

Certainly. You may have noticed though that we are talking about SQL functions here, not CLP or C functions.

You may be able to put this code into a stored procedure and call the procedure from a UDF.

You may even let that stored procedure just declare and open the cursor, and return a handle to the cursor, so that the fetches happen in the caller.
Not sure though whether a UDF is able to do the FETCHes.

No, a UDF can't do that. :-(