CREATE OR REPLACE FUNCTION test_xxxx(in_deduces_id bigint,in_no bigint,threshold float)
RETURNS table(id bigint,ts TIMESTAMP,deduces_id bigint ,no bigint ,data float[]) AS $$
BEGIN
RETURN QUERY
with cte_x as (
SELECT min(a.data[3]) as min_x,max(a.data[3]) as max_x from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
),
cte_y as (
SELECT min(a.data[2]) as min_y,max(a.data[2]) as max_y from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
),
cte_z as(
SELECT min(a.data[1]) as min_z,max(a.data[1]) as max_z from samples2 a where a.deduces_id = in_deduces_id and a.no=in_no
),
cte_samples as (
SELECT a.id,a.ts,a.deduces_id,a.no,a.data from samples2 a
where a.deduces_id = in_deduces_id and a.no=in_no and a.data[5] >threshold
)
SELECT * from cte_samples union all
(SELECT cte_samples.id,cte_samples.ts,cte_samples.deduces_id,cte_samples.no,array[cte_z.min_z,cte_y.min_y,cte_x.min_x] from cte_samples ,cte_x,cte_y,cte_z limit 1) union all
(SELECT cte_samples.id,cte_samples.ts,cte_samples.deduces_id,cte_samples.no,array[cte_z.max_z,cte_y.max_y,cte_x.max_x] from cte_samples ,cte_x,cte_y,cte_z limit 1);
END;
$$
LANGUAGE plpgsql;
select * from test_xxxx(1,1,0.005);//