Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE OR REPLACE FUNCTION goods__list_json (_options json, _limit int = NULL, _offset int = NULL, OUT _result jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql SECURITY DEFINER AS
$func$
DECLARE
   _tbl  CONSTANT text   := 'public.goods_full';
   _cols CONSTANT text[] := '{id, id__category, category, name, barcode, price, stock, sale, purchase}';   
   _oper CONSTANT text[] := '{<, >, <=, >=, =, <>, LIKE, "NOT LIKE", ILIKE, "NOT ILIKE", BETWEEN, "NOT BETWEEN"}';
   _sql           text;
BEGIN
   SELECT concat('SELECT jsonb_agg(t) FROM ('
           , 'SELECT ' || string_agg(t.col, ', '  ORDER BY ord) FILTER (WHERE t.arr->>0 = 'true')
                                               -- ORDER BY to preserve order of objects in input
           , ' FROM '  || _tbl
           , ' WHERE ' || string_agg (
                             CASE WHEN (t.arr->>1)::int BETWEEN  1 AND 10 THEN
                                format('%s %s %L'       , t.col, _oper[(arr->>1)::int], t.arr->>2)
                                  WHEN (t.arr->>1)::int BETWEEN 11 AND 12 THEN
                                format('%s %s %L AND %L', t.col, _oper[(arr->>1)::int], t.arr->>2, t.arr->>3)
                               -- ELSE NULL  -- = default - or raise exception for illegal operator index?
                             END
                           , ' AND '  ORDER BY ord) -- ORDER BY only cosmetic
           , ' OFFSET ' || _offset  -- SQLi-safe, no quotes required
           , ' LIMIT '  || _limit   -- SQLi-safe, no quotes required
           , ') t'
          )
   FROM   json_each(_options) WITH ORDINALITY t(col, arr, ord)
   WHERE  t.col = ANY(_cols)        -- only allowed column names - or raise exception for illegal column?
   INTO   _sql;

   IF _sql IS NULL THEN
      RAISE EXCEPTION 'Invalid input resulted in empty SQL string! Input: %', _options;
   END IF;
   
   RAISE NOTICE 'SQL: %', _sql;
   -- EXECUTE _sql INTO _result;
   _result := to_jsonb(_sql);     -- show SQL string instead of result in fiddle
END
$func$;
SELECT goods__list_json('{"name": [true, 7, "Ad%"]
                        , "category": [true]
                        , "stock": [false, 4, 0]}'
                        , 20, 0);


goods__list_json


"SELECT jsonb_agg(t) FROM (SELECT name, category FROM public.goods_full WHERE name LIKE 'Ad%' AND stock >= '0' OFFSET 0 LIMIT 20) t"


-- test with BETWEEN, illegal column, illegal operator:
SELECT goods__list_json('{"name": [true, 7, "Ad%"]
                        , "category": [true]
                        , "stock": [false, 4, 0]
                        , "price": [true, 11, 10, 20]
                        , "; DELETE * FROM tbl; --": [true, 1, 2]
                        , "purchase": [true, 0, 2]}'
                        , 20, 0);


goods__list_json


"SELECT jsonb_agg(t) FROM (SELECT name, category, price, purchase FROM public.goods_full WHERE name LIKE 'Ad%' AND stock >= '0' AND price BETWEEN '10' AND '20' OFFSET 0 LIMIT 20) t"


-- test without _limit & _offset and not WHERE conditions:
SELECT goods__list_json('{"name": [true]
                        , "category": [true]
                        , "; DELETE * FROM tbl; --": [true, 1, 2]
                        , "purchase": [true, 0, 2]}'); -- illegal operator


goods__list_json


"SELECT jsonb_agg(t) FROM (SELECT name, category, purchase FROM public.goods_full) t"