Whether all table functions are read only?

[Former Member]

When I try to use create or insert statement in a table function I always get error info: : EXPLAIN PLAN and CALL statement are not allowed; DDL is not supported in the READ ONLY procedure/function...

I just wonder how can I use create or insert statement in table function or there cannot use such statement in table function at all？If all table function are read only and whether I can set this property

[pfefferf · Accepted solution]

As the documentation clearly describes (CREATE FUNCTION). This is not possible. For a user defined function all DDL and DML statements are not allowed. You can do this in SQLScript only with procedures.

Regards,
Florian

[rgehlis]

Sorry, but this is just bad. It would be immensely useful to have table functions at least be able to call a procedure. Why this is not possible is beyond me and very frustrating.

[vittal_navale2]

Totally agree!

[lbreddemann]

Out of curiosity: what is the scenario where you want to be able to run a SELECT against a table function and have some data changed right away?

[rgehlis]

Hi Lars,

in my case I'm running a chat bot that is supposed to look up (from a certain table) if a connected user has a certain ID to be able to separate the users conversation with the bot in another table. If the look up doesn't find an entry for that user, it is supposed to insert one automatically. Doing this with a procedure is turning this one step into 2 steps. Looking up if there is an ID and returning it and creating an ID in case there isn't one yet, could otherwise be done with one tableFunction. It would be quite elegant.

Regards,

Robert

[lbreddemann]

And what's the reason for you to say this is not possible to do in a single procedure call?

Let's say you want to store your ID in a variable called userChatID.

The code to call the procedure would look like this:

userID = 1234;
call "getUserChatID" (:userID, :userChatID);

Simple, straightforward and not un-elegant.

The procedure would then look maybe a bit like this:

create procedure "getUserChatID" 
    (IN "userID" BIGINT
   , OUT "userChatID")
as
begin
declare "tempUserChatID" BIGINT = 0;

select "userChatID" into tempUserChatID 
from "specialChatTable"
where "userID" = :userID;

if (:tempUserChatID = 0) then
   select userChatIDseq.nextval into tempUserChatID from dummy;
   insert into "specialChatTable" ("userID", "userChatID")
                           values (:userID, :tempUserChatID);
end if;

userChatID = :tempUserChatID;
end;

So, to me, that again looks straightforward and the code makes it clear enough what it does.

From here I fail to see what extra elegance and immense usability a write-enabled table function would have added.

[rgehlis]

Hi Lars,

I see what you mean, but I think you fail to see that what you posted is my current workaround. My problem with this is, that I have to to a seperate select statement after the procedure is done, to get the UserID. If TableFunctions were not excusively read only, then it could do all of it in one place.

Regards,

Robert

[lbreddemann]

You're saying that you cannot evaluate the OUT parameter "userChatID" in your calling code? Why's that?

Why is there the need for an additional SELECT in your code?

Turning a simple SELECT into a write operation certainly is not more elegant - it would just add confusion about what your code is doing.

[FCI]

Yes, table function are read-only (source : HA300).

Regards,

Frederic

[Former Member]

Really thanks for your help, for now I can understand why I always get the error