Re: Strategy for preparing a query containg dynamic case / when

Hi subin,

can you clarify please?

On Thu, Aug 11, 2022 at 3:42 PM subin <subtitle(dot)indo(at)gmail(dot)com> wrote:

> Please let me know if that is okay.
>
> On Thu, Aug 11, 2022 at 11:11 AM Theofilos Theofovos <fithis2001(at)gmail(dot)com>
> wrote:
>
>> 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
>>
>

--
Dr. Vasileios Anagnostopoulos (MSc,PhD)
Researcher/Developer