Multiple clause with lines SQL-CodePudding

I have implemented the following function in a functional way but with the problem that I had implemented it with temporary tables and inserts.

Since I want to optimize the code, I have decided to try the SQL with as .. statements.

The downside is that the way to implement it with SQL with as statements is different and I'm opting for this output:

ERROR: syntax error at end of input LINE 48: ...r = p_id_var AND fvr.utz BETWEEN p_utz_begin AND p_utz_end);
^ SQL state: 42601 Character: 1754

This is the code:

CREATE OR REPLACE FUNCTION tlm.main_dash_tele_freq_blackout(
    p_id_unit integer,
    p_utz_begin timestamp without time zone,
    p_utz_end timestamp without time zone)
    RETURNS TABLE(can_freq interval, can_blackout interval, gps_freq interval, gps_blackout interval, chargeloss boolean) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
     CAN_freq interval;
     CAN_blackout interval;
     CAN_chargeloss boolean;
     GPS_freq interval;
     GPS_blackout interval;
     max_diff integer;
     p_id_var integer;
    
BEGIN

p_id_var = 1001;
    
with main_dash_tele_freq_blackout_first_reading as (
    SELECT fvr.utz, fvr.val FROM var.oper_readings fvr WHERE fvr.id_unit = p_id_unit AND fvr.id_var = p_id_var AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),

main_dash_tele_freq_blackout_second_reading as ( 
    SELECT fr.utz , fr.val FROM main_dash_tele_freq_blackout_first_reading fr WHERE fr.id != 1),

 main_dash_tele_freq_blackout_result_reading as (
    SELECT ff.utz, ss.utz,  (ss.utz - ff.utz), (ss.val - ff.val) FROM main_dash_tele_freq_blackout_first_reading ff FULL JOIN main_dash_tele_freq_blackout_second_reading ss ON ff.id = ss.id
)
; 

CAN_freq = (SELECT AVG(diff) FROM main_dash_tele_freq_blackout_result_reading WHERE diff < '00:10:00');

CAN_blackout = (SELECT  AVG(diff)  FROM main_dash_tele_freq_blackout_result_reading WHERE diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1));

CAN_chargeloss = (SELECT  (MAX(diff_val)>10)  FROM main_dash_tele_freq_blackout_result_reading WHERE diff > '00:10:00' AND (diff_val > 1 OR diff_val < -1));

------------------------------------------------------ Similar case for this variables ------------------------------------------------

with main_dash_tele_freq_blackout_first_GPS_reading as (
    SELECT  fvr.utz, fvr.lat, fvr.lon FROM var.oper_geo_readings fvr WHERE fvr.id_unit = p_id_unit AND fvr.utz BETWEEN p_utz_begin AND p_utz_end
),

 main_dash_tele_freq_blackout_second_GPS_reading as ( 
    SELECT fr.utz , fr.lat, fr.lon FROM main_dash_tele_freq_blackout_first_GPS_reading fr WHERE fr.id != 1
),

 main_dash_tele_freq_blackout_result_GPS_reading as (
    SELECT ff.utz, ss.utz,  (ss.utz - ff.utz), (ss.lat - ff.lat), (ss.lon - ff.lon) FROM main_dash_tele_freq_blackout_first_GPS_reading ff FULL JOIN main_dash_tele_freq_blackout_second_GPS_reading ss ON ff.id = ss.id
);

GPS_freq = (SELECT AVG(diff) FROM main_dash_tele_freq_blackout_result_GPS_reading WHERE diff < '00:10:00');

GPS_blackout = (SELECT  AVG(diff)  FROM main_dash_tele_freq_blackout_result_GPS_reading WHERE diff > '00:10:00');

RETURN QUERY (SELECT CAN_freq, CAN_blackout, GPS_freq, GPS_blackout, CAN_chargeloss );

END
$BODY$;

CodePudding user response：

You can use like as these syntaxes:

declare variable1 integer; 
declare variable2 integer; 

with 
  tb1(a) as (
    select 1
    union all 
    select 2
    union all 
    select 3
),  
  tb2(a) as (
    select 5
    union all 
    select 10
    union all 
    select 15
)
select (select sum(tb1.a) from tb1) into variable1, (select sum(tb2.a) from tb2) into variable2;

return query 
select variable1, variable2

return query 
with 
  tb1(a) as (
    select 1
    union all 
    select 2
    union all 
    select 3
),  
  tb2(a) as (
    select 5
    union all 
    select 10
    union all 
    select 15
)
select (select sum(tb1.a) from tb1), (select sum(tb2.a) from tb2);