row level security – How to move a WHERE condition that depends on a joined table to a RLS policy in PostgreSQL

Given the following table

create table cities (
    name    text,
    state   text

insert into cities (name, state) values
('Los Angeles','CA'),
('New York','NY'),
('San Francisco', 'CA');

one can create a query like this;


select set_config('query.params', '({"id": 1, "state":"CA"}, {"id": 2, "state":"NY"})', true);

    _query.param->'id' as id,
    _query.param->'state' as state,
    _result.root as result
from json_array_elements(current_setting('query.params', true)::json) _query (param)
left outer join lateral (
    select coalesce(json_agg("root"), '()') as "root"
        from (
            select row_to_json("_0_root") as "root"
            from (
                -- core query start --
                select name
                from "data"."cities" tbl
                    tbl.state = _query.param->>'state' -- <-- move this condition to "dynamic" RLS
                -- core query end --
            ) as "_0_root"
        ) as "_2_root"
) _result on ('true');


the output looks like this

1   CA  ({"name":"Los Angeles"}, {"name":"San Francisco"})
2   NY  ({"name":"New York"})

What this query accomplishes conceptually is that you run the same “core” query multiple times with different parameters and aggregate the result as JSON.

The question/problem now is this:
Would it be possible to move the tbl.state = _query.param->>'state' condition inside a RLS policy that reads the query.params setting but is somehow aware of the outer query “parameters” for the current row?

This particular example is just and example (not set in stone/important), the shape of the query can be changed, the shape of the query.params json can be changed. The question is if it’s possible to create a combination of array of params + core query + rls policy so that core query is executed multiple times, the filters/conditions are specified in the rls policy and they act each time based on current element of the params array.