Here is my function:
CREATE OR REPLACE FUNCTION public.tsc_out_only(areaid_array character varying[], d_date date, start_time__ timestamp without time zone, end_time__ timestamp without time zone)
RETURNS TABLE(imei___ character varying, area_id_ uuid, uc_name_ character varying, cluster__id_ text, campaign__date_ date, individual_dept_time_ interval, departure__status_ text, num_of_dept_ bigint, final_dep_check_ interval)
LANGUAGE plpgsql
AS $function$
DECLARE
i integer := 1;
BEGIN
FOR i IN 1..array_length(areaid_array, 1) LOOP
RETURN QUERY
with
total_imei as
(
select
assets."IMEI" as imei, caa."campaignScopeAreaID" as uc_id
from
campaign_staff staff
left join campaign_staff_assets csa on csa."campaignStaffID" = staff."ID" and csa."date" = (select max(date) from campaign_staff_assets cs2 where cs2."campaignStaffID"= csa."campaignStaffID" and cs2.date<=d_date::date)
left join campaign_area_assets caa on caa."assetID" = csa."assetID"
and caa."campaignScopeAreaID" = csa."campaignScopeID"
left join assets assets on assets."ID" = caa."assetID"
left join staff_type staff_type ON staff_type."ID" = staff."staffTypeID"
left join campaign_asset_status cas on cas."campaignStaffID" = staff."ID" and cas."date" = (select max(date) from campaign_asset_status cas2 where cas2."campaignStaffID"= cas."campaignStaffID" and cas2.date<=d_date::date)
left join campaign_scope cs on cs."areaID" = caa."campaignScopeAreaID"
where
caa."campaignScopeAreaID" = areaid_array[i]::uuid and
assets."IMEI" is not null
and staff_type."ID" = 'acf0cc70-7c92-474d-82af-f37494f94c09' -- Team ID
and (cas.status is null or cas.status <> 'DeployedWoPhone')
and d_date::date between cs."campaignStartDate" and cs."catchupEndDate"
--and caa."campaignScopeAreaID"='2337a34b-5f76-4854-b39a-ec1b3deddc91'--TESTING
)
,
orig_dataset as not materialized (
select caa."campaignScopeAreaID" as uc_id , cr.imei ,cr."geoJson" as points_geom ,
lead(cr."geoJson") over (partition by cr.imei order by cr."generatedAt") as leading_points,
LEAD(cr."geoJson", 2) OVER (PARTITION BY cr.imei ORDER BY cr."generatedAt") AS leading_points_2,
LEAD(cr."geoJson", 3) OVER (PARTITION BY cr.imei ORDER BY cr."generatedAt") AS leading_points_3,
LEAD(cr."geoJson", 4) OVER (PARTITION BY cr.imei ORDER BY cr."generatedAt") AS leading_points_4,
LEAD(cr."geoJson", 5) OVER (PARTITION BY cr.imei ORDER BY cr."generatedAt") AS leading_points_5,
cr."generatedAt"+interval '5 hours' as time_created
from campaign_pings cr
left join assets a on a."IMEI" = cr.imei
left join campaign_area_assets caa on a."ID" = caa."assetID"
where
cr.imei in (select imei from total_imei)
and
cr."generatedAt" + interval '5 hours' between start_time__ and end_time__
--and caa."campaignScopeAreaID"='2337a34b-5f76-4854-b39a-ec1b3deddc91'--TESTING
-- and caa."campaignScopeAreaID" = areaid_array[i]::uuid
--and io.imei is not null
)
,
cluster_groups as not materialized (
select areaid , id as cluster_id, geojson as cluster_centroid from predict_tsc tsc
where approved = 'Approved' and date::date=d_date
and
--areaid ='2337a34b-5f76-4854-b39a-ec1b3deddc91'--TESTING
areaid::uuid = areaid_array[i]::uuid
)
,
dept_cluster_raw as (
SELECT
routes.uc_id ,
cg.cluster_id ,
cg.cluster_centroid ,
routes.imei ,
routes.time_created::date as campaign_date,
count(routes.time_created) as num_of_dept,
min(routes.time_created) as min_per_imei_cluster,
max(routes.time_created) as final_dep_check
FROM cluster_groups as cg
CROSS JOIN LATERAL (
SELECT routes.time_created,routes.imei, routes.uc_id
FROM orig_dataset as routes
where
--routes.time_created between start_time__ and end_time__
--and
(
ST_DWithin(
st_transform(routes.points_geom, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points_2, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points_2, 4326)::geography,
st_transform(leading_points, 4326)::geography,
10
)
AND NOT ST_DWithin(
st_transform(leading_points_3, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points_3, 4326)::geography,
st_transform(leading_points_2, 4326)::geography,
10
)
AND NOT ST_DWithin(
st_transform(leading_points_4, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points_4, 4326)::geography,
st_transform(leading_points_3, 4326)::geography,
10
)
AND NOT ST_DWithin(
st_transform(leading_points_5, 4326)::geography,
st_transform(ST_SetSRID(cg.cluster_centroid, 4326), 4326)::geography,
100
)
AND NOT ST_DWithin(
st_transform(leading_points_5, 4326)::geography,
st_transform(leading_points_4, 4326)::geography,
10
)
)
) routes --where routes.uc_id=cg.areaid::uuid
group by 1,2,3,4,5
)
,
dept_master_data as
(
select imei,uc_id,cluster_id,campaign_date,min_per_imei_cluster as individual_dept_time,
num_of_dept,final_dep_check from dept_cluster_raw
)
,
dept_labelled as
(
select imei,uc_id,cluster_id,campaign_date, date_trunc('seconds', individual_dept_time::time) as individual_dept_time ,
case when individual_dept_time is not null
then 'Dept_time_exists'
else 'No_dept_found'
end as dept_status,
num_of_dept,
final_dep_check
from dept_master_data
)
,
result_set as
(
select dl.*
from dept_labelled dl
/*full outer join arrival_labelled al
on dl.imei=al.imei
and dl.uc_id=al.uc_id
and dl.cluster_id=al.cluster_id
and dl.campaign_date = al.campaign_date */
order by dl.uc_id,dl.campaign_date
)
,
final_times as
(
select * from result_set
)
,
final_times_ranked as (
select imei ,uc_id as uc_id,cluster_id as cluster_id,campaign_date as campaign_date,individual_dept_time,dept_status,num_of_dept,final_dep_check
from final_times)
,
final_times_ranked_2 as (
select *--, dense_rank() over (partition by imei order by individual_dept_time,cluster_id) as ranked_imei
from final_times_ranked)
,
combined as (
select * from final_times_ranked_2 --where ranked_imei<=1
)
,
display_result as (
select combined.*,total_imei.imei as binded_imei_,total_imei.uc_id as uc_id_binded
from combined
right join total_imei
on total_imei.imei=combined.imei
)
,
naming_uc as
(
select display_result.*, a.name as uc_name
from display_result left join area a on a."ID" = display_result.uc_id_binded
)
,
answer as (
select *,coalesce (dept_status,'No_dept_found') as Departure_status_ from naming_uc
--order by uc_id,cluster_id
)
,
joined as (
select *,d_date::date as campaign_date_binded from answer
where uc_id_binded in (select tsc.areaid::uuid as uc_id_imported from predict_tsc tsc)
),
joined_2 as (
select coalesce (imei,binded_imei_) as imei, coalesce(uc_id,uc_id_binded) as uc_id, cluster_id, coalesce (campaign_date,campaign_date_binded) as campaign_date,
individual_dept_time,departure_status_,num_of_dept,final_dep_check
from joined
),
show_this as (
select joined_2.imei,joined_2.uc_id, a.name as uc_name, joined_2.cluster_id::text,joined_2.campaign_date,individual_dept_time,departure_status_,num_of_dept,date_trunc('seconds',final_dep_check::time) as final_dep_check
--to_char(to_timestamp((tcr.num_of_pings_in_tsc::numeric/60) * 60), 'MI:SS') as time_in_tsc
from joined_2
left join area a on a."ID" = joined_2.uc_id
--left join total_cluster_raw tcr on tcr.imei =joined_2.imei
--and tcr.uc_id = joined_2.uc_id
--and tcr.cluster_id=joined_2.cluster_id
--and tcr.campaign_date =joined_2.campaign_date
order by uc_id,cluster_id
)
,
remainder_1 as (
select imei from total_imei
except
(select imei from show_this
except
--bad results
(select imei from show_this
where departure_status_ = 'No_dept_found'
))
)
,
-----------------------------------------------------------------------
script_1_and_2_combo_results as (
(select * from show_this
except
select * from show_this
where Departure_status_ = 'No_dept_found'
)
--union
--select * from renaming_TSC_with_count
)
,
final_master_results as (
select * from script_1_and_2_combo_results
union
------------
select * from show_this
where (Departure_status_ = 'No_dept_found'
and
imei not in (select imei from script_1_and_2_combo_results))
---------------
)
,
final_master_results_formatted as
(
select * from final_master_results
union
(select imei, uc_id,uc.name as uc_name, null as cluster_id, d_date::date as campaign_date, null as individual_dept_time,
'No_dept_found' as departure_status_, null as num_of_dept,null as final_dep_check
--null as time_in_tsc
from total_imei
left join area uc on uc."ID" = total_imei.uc_id
where imei not in (select imei from final_master_results )
)
)
select * from final_master_results_formatted;
END LOOP;
END
$function$
;
The problem is that instead of columns as specified in the function definition, I am getting comma separated values in each row. How do I change this, so that I have values in columns. The problem is that instead of columns as specified in the function definition, I am getting comma separated values in each row. How do I change this, so that I have values in columns.
when I run this function call, I am getting comma separated values in each row
select tsc_out_only(array['7e555edb-f93f-49af-a9ab-493873ff5487'], '2024-04-29','2024-04-29 06:00:00','2024-04-29 18:00:00')
Yes, that selects one column (named tsc_out_only) whose type is your record (that is the return type declared by the function). It's not a comma-separated string, it's just a row literal that you'll get shown as text, depending on your client. Regardless, if you actually want multiple columns, you need to use
select * from tsc_out_only(array['7e555edb-f93f-49af-a9ab-493873ff5487'], '2024-04-29','2024-04-29 06:00:00','2024-04-29 18:00:00')
or
select tsc_out_only(array['7e555edb-f93f-49af-a9ab-493873ff5487'], '2024-04-29','2024-04-29 06:00:00','2024-04-29 18:00:00').*