Database functions as filters in grid

[tchief]

What kind of change does this PR introduce?

Feature. Added a property dbFunctions: array of db function names which could be used in filters by end user.

What is the current behavior?

Only set of static operators could be used.

What is the new behavior?

Developer could add db function names and user would be able to use those functions during filtering process.

If property dbFunctions was not set, the behavior and UI stays the same as before this PR.

Additional context

DB functions. Use case: secret cities locations

App that collects requests from users (requestor) with info about city name (for simplicity). App has a table with info about cities and theirs coordinates. And this info should stay private. App has a grid that displays all requests with some filters for users (operator).

Operator should be able to find requests nearby some city (say, 25 km around Cambridge). These parameters are dynamic (distance 25 km and city Cambridge), and operator should be able to pass different ones.

Imagine operators select from column, in filter, nearest_cities db function, and set value as Camrbidge,25. Then grid instead of this sql query: select ... where "from" in ('Cambridge','25')... would generate this one: select ... where "from" in (select * from nearest_cities_names('Cambridge','25'))...

Schema

create table public.requests (
    id bigint generated by default as identity primary key,
   "from" varchar, -- city name, like London
   "details" varchar
};

create table public.cities (
    id bigint generated by default as identity primary key,
    city text not null,
    location geography(point,4326)
);

create or replace function nearest_cities(city text, distance float) returns table(city text) as $$
  ...
$$

Edge functions. Use case: evacuation

It also could be used with edge functions, having db function(s) as a proxy.

Extending previous example.

There is a 3rd party service that could be used to retrieve up-to-date info about available cars at location. You pass coordinates and max hrs till departure and get a list of cities with transport options available. You can only query that service, no syncing with own db is supported.

Now you need to allow operators to filter requests where transport option is available in their city in next x hrs.

You could build either sort of generic db function proxy to run_edge_functions, or one db function per one edge function.

create or replace function run_edge_functions(_name text, _args text[]) returns table(data text) as $$
  select json_array_elements_text(("content"::json->>'data')::json) as data
  from http(...)
$$

Summary

The feature both controversial and powerful.