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 TABLE public.customerorder (
order_id serial NOT NULL
, customer_id int NOT NULL
, order_placed_on timestamptz NOT NULL
, order_description varchar(180) NOT NULL
);
INSERT INTO public.customerorder VALUES
(1, 2579927, '2022-11-15 16:56:48.750 +0100', 'TEST SAMPLE')
, (2, 2579927, '2022-11-16 16:56:48.750 +0100', 'TEST SAMPLE')
, (3, 2579927, '2022-08-03 16:56:48.750 +0100', 'TEST SAMPLE')
, (4, 2579927, '2022-08-01 16:56:48.750 +0100', 'TEST SAMPLE')
;
CREATE TABLE
INSERT 0 4
CREATE OR REPLACE FUNCTION public.customer_orders(
_customer_id int
, _sort_field_and_direction text
, _limit int
, _offset int
, _start_period date = CURRENT_DATE - 30 -- !!!
, _end_period date = CURRENT_DATE -- !!!
)
RETURNS TABLE (id int, customerid int, description varchar, placedon timestamptz)
LANGUAGE plpgsql AS
$func$
DECLARE
_sql text := '
SELECT order_id, customer_id, order_description, order_placed_on
FROM customerorder
WHERE customer_id = $1
AND order_placed_on BETWEEN $2 AND $3
ORDER BY ' || _sort_field_and_direction || '
LIMIT $4
OFFSET $5';
BEGIN
RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql
USING _customer_id -- $1
, _start_period -- $2
, _end_period -- $3
, _limit -- $4
, _offset -- $5
;
END
$func$;
CREATE FUNCTION
-- run with default period (last 30 days)
SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', 10, 0);
| id | customerid | description | placedon |
|---|
SELECT 0
-- run with custom period
SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', 10, 0, '2022-08-01', '2022-08-11');
| id | customerid | description | placedon |
|---|---|---|---|
| 3 | 2579927 | TEST SAMPLE | 2022-08-03 16:56:48.75+01 |
| 4 | 2579927 | TEST SAMPLE | 2022-08-01 16:56:48.75+01 |
SELECT 2