Search code examples
sqlrmonetdbr-dbimonetdblite

hHw to pass a system function to MonetDB through R?

Using MonetDB.R, I want to run this command but I don't want to open up mclient. Is there a generic way to do this?

create function div_noerror(l double, r double) returns double
Solution

  • from Hannes, the author of MonetDB.R --

    You can just create it in your DB. This will fail if it already exists, but you can catch that error. so 

    dbSendQuery(con, "create function div_noerror(l double, r double) returns double external name calc.div_noerror”) should work.

    here's a slightly longer usage example:

    # example of division
dbGetQuery( con , "SELECT 1 / 2 AS a" )

# example of division by zero, which causes an error
dbGetQuery( con , "SELECT 1 / 0 AS a" )

# load the `div_noerror` function through a system call
dbSendQuery( con , "CREATE FUNCTION div_noerror(l DOUBLE, r DOUBLE) RETURNS DOUBLE EXTERNAL NAME calc.div_noerror" ) 

# use the div_noerror function instead of the `/` operator
dbGetQuery( con , "SELECT div_noerror( 1 , 2 ) AS a" )

# division by zero now returns a missing instead of crashing the entire query
dbGetQuery( con , "SELECT div_noerror( 1 , 0 ) AS a" )