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?.
select version();
version |
|---|
PostgreSQL 9.5.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit |
SELECT 1
CREATE TABLE "probe_data" (
"id" int4,
"sensor" varchar(255) COLLATE "pg_catalog"."default",
"value" numeric(6,2),
"created_date" timestamp(6)
)
;
CREATE TABLE
insert into probe_data values(1, 'System1.orp.orp', 306.00,'2020-07-07 11:08:46');
insert into probe_data values(2, 'System2.orp.orp', 36.00,'2020-07-06 11:08:46');
insert into probe_data values(3, 'System3.orp.orp', 6.00,'2020-07-04 11:08:46');
INSERT 0 1
create or replace function example (param1 jsonb) returns jsonb as
$body$
declare
jsondata jsonb;
begin
jsondata = (select array_to_json(array_agg(row_to_json(d))) from (
select sensor,id,value,created_date from probe_data where created_date >=(param1->>'fromdate')::TIMESTAMP and created_date <= (param1->>'todate')::TIMESTAMP ) d);
RETURN jsondata;
end;
$body$
LANGUAGE plpgsql;
CREATE FUNCTION
select * from example( '{
"sensor":"sensor1",
"fromdate":"2020-07-07",
"todate":"2020-07-08"
}'::jsonb)
example |
|---|
[{"id": 1, "value": 306.00, "sensor": "System1.orp.orp", "created_date": "2020-07-07T11:08:46"}] |
SELECT 1