Add SQL functions

To add custom functions you want to use making queries you can use javascript function that returns SQL string where arguments are marked with $.

To use PostgreSQL JSON array element function:

// zazler server setup here
db = zazler.db("n", "sqlite:///tmp/db", { read: "*" })
db.sqlFn.jsonEl = function (callback, ev, count, inf) {
  callback("$0::json -> $1");
  • select=jsonEl(J,3)&J=[1,2,3,4,5] gives result 4 ( '[1,2,3,4,5]'::json -> 3 ).
  • select=jsonEl(J,2*2)&J=[1,2,3,4,5] gives result 5 – you can use any other SQL in arguments
  • select=jsonEl(J,1)-1&J=[1,2,3] gives result 1 – you can use defined function in whatever context

Arguments are escaped and handled by Zazler. User have no access to arguments directly.

count says how many arguments are given.

inf argument is function extra information. In query syntax there is possible to say extra info for functions. Like this fn.extrainfo(arg1,arg2). This is ment for cases where some SQL special keyword must be used. Cast function has datatype keywords. With this extra information it is convenient to define cast on URL like, cast.varchar(arg) etc.

If you want to pass all arguments, there is special variable for that: $,. If defined function is function (cb) { cb('myfn($,)') } and you call myfn(1,2,3) on URL then in SQL there will be myfn(1, 2, 3).