Dear expert,
Could you please review and suggest to optimize performance of the PLSQL
procedure in PostgreSQL?
I have attached the same.
Thanks in advance
Regards,
Dinesh Chandra
________________________________
DISCLAIMER:
This email message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies of
the original message. Check all attachments for viruses before opening them.
All views or opinions presented in this e-mail are those of the author and may
not reflect the opinion of Cyient or those of our affiliates.
-----Function to get weather forcast based on threshold value of weather
parameters
CREATE OR REPLACE FUNCTION dome.geteffectiveforcast4(
coldmaxtemp double precision,
coldmintemp double precision,
hotmaxtemp double precision,
hotmintemp double precision,
windmax double precision,
windmin double precision,
rainmax double precision,
rainmin double precision,
humiditymax double precision,
humiditymin double precision,
snowmax double precision,
snowmin double precision)
RETURNS SETOF "TABLE(lockey text, from_time timestamp without time zone,
to_time timestamp without time zone, weathertype text, flood_height text,
severity text)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY select s."LocKey",min(s.dt) from_time, max(s.dt) to_time,'Red
Cold Temperature' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by
"Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over
(partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature"<0
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over
(partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over
(partition by "LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature">=coldmintemp and "Temperature"<= coldmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Hot Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,row_number() over
(partition by "LocKey" order by "Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly" where "Temperature" >
hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Cold Temperature' as WeatherType,'' as "Flood_height",'' as
"Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" +
"Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Temperature" between hotmintemp and hotmaxtemp
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity"
from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" >windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Wind Speed' as WeatherType,'' as "Flood_height",'' as "Severity"
from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Wind Speed" between windmin and windmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Rain' as WeatherType,
s."Flood_height",s."Severity" from (select
"LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by
"Date" + "Time") as rn,
("Date" + "Time" - (concat(row_number() over (partition by "LocKey"
order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from
dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" >rainmax and "Rain" between
fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Rain' as WeatherType,s."Flood_height",s."Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
,fc."Flood_height",fc."Severity" from
dome."AccuWeatherForcastHourly",dome.flood_config2 fc
where "Rain" between rainmin and rainmax and "Rain" between
fc."Accuweather_MinRain" and fc."Accuweather_MaxRain"
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp,s."Flood_height",s."Severity"
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" >snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Snow' as WeatherType,'' as "Flood_height",'' as "Severity" from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Snow" between snowmin and snowmax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Red Humidity' as WeatherType,'' as "Flood_height",'' as "Severity"
from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" >humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp
UNION
select s."LocKey",min(s.dt) from_time, max(s.dt)
to_time,'Amber Humidity' as WeatherType,'' as "Flood_height",'' as "Severity"
from (
select "LocKey","Date" + "Time" as dt,
row_number() over (partition by "LocKey" order by "Date" + "Time")
as rn,
("Date" + "Time" - (concat(row_number() over (partition by
"LocKey" order by "Date" + "Time"),' hours')::interval)) as grp
from dome."AccuWeatherForcastHourly"
where "Humidity" between humiditymin and humiditymax
order by "LocKey", "Date" + "Time") s
group by s."LocKey",s.grp;
END;
$function$;
-----Query executing the above Weather Forcast function
Select GA.*,forcast.from_time::date as fromdate,forcast.from_time::time as
fromtime,forcast.to_time::date as todate,forcast.to_time::time as
totime,forcast.weathertype,forcast.flood_height,forcast.severity as
flood_severity from dome.gridassetinfo3 GA
,dome.geteffectiveforcast4({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})
forcast where forcast.lockey = cast(GA.lockey as text)
---Function to get lightning impacted asset information
CREATE OR REPLACE FUNCTION dome.getlightningimpactedassets(
asset_table text,
buffer double precision)
RETURNS SETOF "TABLE(lightningdate date, lightningtime time without time
zone, latitude double precision, longitude double precision, amplitude double
precision, distance double precision, feedername character varying, assetid
bigint, assetcategory character varying, asset_xy character varying,
customer_xy character varying)"
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE
ROWS 1000.0
AS $function$
BEGIN
RETURN QUERY Select
light.*,ga.feedername,ga.assetid,ga.assetcategory,ga.asset_xy,ga.customer_xy
from dome.lightning light join dome.gridassetinfo3 ga
on
st_intersects(st_buffer(st_point(light.latitude,light.longitude),buffer),ST_GeomFromText('MULTIPOINT('||ga.asset_xy||')'))
where ga.assettable=asset_table;
END;
$function$;
