From: | Theofilos Theofovos <fithis2001(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Strategy for preparing a query containg dynamic case / when |
Date: | 2022-08-11 10:11:08 |
Message-ID: | CAKmU=T_HAdHMtFNLMVrVQyH+QX2BCaMtjnyztzRe2btCmA+ydg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
just for the sake of the example lets have this kind of table
CREATE TABLE experimentals (
indy integer not null primary KEY,
XXX1 integer NOT NULL,
XXX2 integer NOT NULL,
json_data jsonb
);
It emulates a case where an item has integer labels XXX1, and XXX2
and characteristics described as json Z1, Z2 (can be any number of them,
e.g. Z3, Z4 ...)
each having a value in [0,1).
A synthetic population of the table can take the form
insert into experimentals
select indy, max(XXX1), max(XXX2), json_object_agg(zval, tval) as json_data
from (
select
(RANDOM() * 3)::INT as XXX1,
(RANDOM() * 5)::INT XXX2,
unnest(ARRAY['Z1', 'Z2']) as zval,
unnest(ARRAY[RANDOM(), RANDOM()]) as tval,
unnest(ARRAY[seq, seq]) as indy
FROM GENERATE_SERIES(1, 1000) seq
) exploded_jsons
group by indy;
Now, for each pair of labels we define a partitioning of the
characteristics, two possible appear here
-- XXX1 = 1, XXX2 = 2 partition (Z1, Z2) => (0..1 , 0..0.5) -> F1 (0..1,
0.5..1) -> F2
-- XXX1 = 3, XXX2 = 1 partition (Z1, Z2) => (0..0.3 , 0..0.5) -> G1
(0..0.6, 0.5..1) -> G2, ELSE G3
In concrete SQL they take the form:
-- Partitioning 1
select XXX1, XXX2, indy, json_data,
case
when
(json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
then
'F1'
else
'F2'
end as some_label
from experimentals
where XXX1 = 1 and XXX2 = 2 ;
-- Partitioning 2
select XXX1, XXX2, indy, json_data,
case
when
(json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.3 and
(json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
then
'G1'
when
(json_data->>'Z1')::float >= 0 and (json_data->>'Z2')::float < 0.6 and
(json_data->>'Z2')::float >= 0.5 and (json_data->>'Z2')::float < 1
then
'G2'
else
'G3'
end as some_label
from experimentals
where XXX1 = 3 and XXX2 = 1 ;
the partitioning is an immutable function of a column and is given to me at
runtime in some format
Now, to the real question for preparing it.
I would expect something (pseudo sql) for partitioning1
PREPARE fooplan (int, int, immutable (jsonb -> varchar) ) AS
select XXX1, XXX2, indy, json_data, $x3(json_data) as some_label
from experimentals
where XXX1 = $1 and XXX2 = $2 ;
EXECUTE fooplan(1, 2, 't', json_data ->
case
when
(json_data->>'Z2')::float >= 0 and (json_data->>'Z2')::float < 0.5
then
'F1'
else
'F2'
end
);
Is this something possible, is there any recommended strategy for these cases?
PS The real queries are containing joins and other filters, I just
include a simplified case.
Vasilis
--
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer
From | Date | Subject | |
---|---|---|---|
Next Message | Laura Smith | 2022-08-11 12:30:51 | Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed |
Previous Message | Laura Smith | 2022-08-11 10:00:07 | Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed |