How to write a custom function in SQL?

[JrgMyr]

Dear all.

The Script Repository has some fine examples of how to write a custom function in either Javascript or TCL. Unfortunately, there are no examples for custom functions written in SQL in the documentation.

Example: Let's assume we want to create a scalar function leftstr() based on the existing instr() function. The code surely contains something like instr(t, 1, n).

In Javascript there is thearguments[] array. How do I populate the parameters t and n in this case?

Any ideas?

[pawelsalawa]

By looking at the code, it seems there are 3 formats of variables allowed:

• :var_name
• ?var_name
• $var_name

The "var_name" part should be aligned with named arguments of the function. If I'm not mistaken, the SQL-implemented does not allow using arguments if the function does not explicitly list them as named arguments. I haven't tested it (I'm on holidays at the moment, I don't have possibility to try it out).

[JrgMyr]

Yes. That's what I did.
I can also trigger an error message like "wrong number of arguments to function".
I just can't make it work.

[JrgMyr]

[pawelsalawa]

I believe your leftstr implementation code should start with SELECT first. The implementation code has to be a full, valid SQL statement, not just expression, because it allows also INSERTs, UPDATEs, DELETEs and even multiple SQL statements as single function.

[JrgMyr]

Oh, thanks, Pawel.
That was the solution.

select substr(:t, 1, :n); indeed works!