带有SQL行的多个子句

Question

我以函数的方式实现了以下功能，但问题是我用临时表和插入实现了它。

由于我想优化代码，所以我决定尝试使用.语句。

缺点是，用as语句用SQL实现它的方式是不同的，我选择这个输出：

错误:输入第48行末尾的语法错误：...r = p_utz_begin和p_utz_end之间的p_id_var和fvr.utz )；

^ SQL状态: 42601字符: 1754

这是代码：

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$;

Answer 1

您可以使用like作为这些语法：

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);