Functions

Functions

There is possibility to use the database native functions in a rule.
About PostgreSQL native functions, you can read (here).

For example, the NOW() function, returns the current time as timestamp. So we can create a simple rule using this function. The JSON structure forces us to write the function name as a string. So, to distinguish the name of the function from the regular text, add the #func: prefix before the function name. Databukcet server read such a string as a function.

{
  "rules": [
    ["createdAt", "<", "#func:NOW()"]		
  ]
} 

The above example is correct, but it does not make much sense, because it is always true. How can we modify this example, to get all data created at least one day ago? To create this rule, we can use another function named "INTERVAL":

{
  "rules": [
    ["createdAt", "<", "#func:NOW() - INTERVAL '1 DAY'"]		
  ]
} 

We can also verify the correctness of such a function structures through SQL query:

select NOW() - INTERVAL '1 DAY';

If the SQL query works, the rule also should work.

Let's analyze example with JSON property. In the previous example, we used createdAt field which is the standard table column with defined type. The database engine knows how to interprett rules with such a field. What happens when we need to use property, e.g. $.birthDate. It is stored in the JSON, but there is no information that this is a datetime property. When we try to compare this property with constant datetime, given in the proper format, Databucket knows that the value has to be cast to datetime before comparing. See the following example:

{
  "rules": [
    ["$.birthDate", "<", "2000-11-18T20:00:00.000Z"]		
  ]
} 

But, we might want to compare this to the function result. In such a case, we need to forse casting to datetime by adding ::timestamp like in the following example:

{
  "rules": [
    ["$.birthDate::timestamp", "<", "#func:NOW() - INTERVAL '5 YEARS'"]		
  ]
}