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;
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" |