php - pgsql - 如何仅获取存储过程中非空值的总和

Question

我已经在 pgsql 中创建了一个存储过程。特定列中有一个 json 对象，我需要获取该 json 对象中数据的总和。在遇到空值之前，它可以完美运行。json 对象中的元素可以有空值。但是当我得到总和时它给出了一个错误。

Statement could not be executed (22P02 - 7 - ERROR: invalid input syntax for integer: "" CONTEXT: PL/pgSQL function gettargetreports(date,date) line 3 at RETURN QUERY)

这是我的存储过程。我该如何解决这个问题。

CREATE OR REPLACE FUNCTION public.gettargetreports(
IN start_dates date,
IN end_dates date)
RETURNS TABLE(report_id integer, report_name text, profileid text, 
    conv integer, visits integer, avgtime integer, revenue integer, 
    backlink integer, newvisits integer, bouncerate integer, roomnights 
    integer, visibility integer, marketshare integer, pagesvisits integer, 
    transactions integer, domainauthority integer, seocontribution 
    integer, overallwebvisits integer, newvisitspercentage integer) AS
$BODY$
BEGIN 
RETURN QUERY (
    (SELECT
        rpt.report_id,
        rpt.report_name,
        rpt.report_data->>'profile' as profileId,
        sum((rpt.report_target_data->>'conv')::int)::int as conv,
        sum((rpt.report_target_data->>'visits')::int)::int as visits,
        sum((rpt.report_target_data->>'avgTime')::int)::int as avgTime,
        sum((rpt.report_target_data->>'revenue')::int)::int as revenue,
        sum((rpt.report_target_data->>'backlink')::int)::int as backlink,
        sum((rpt.report_target_data->>'newVisits')::int)::int as newVisits,
        sum((rpt.report_target_data->>'bounceRate')::int)::int as bounceRate,
        sum((rpt.report_target_data->>'roomNights')::int)::int as roomNights,
        sum((rpt.report_target_data->>'visibility')::int)::int as visibility,
        sum((rpt.report_target_data->>'marketshare')::int)::int as marketshare,
        sum((rpt.report_target_data->>'pagesVisits')::int)::int as pagesVisits,
        sum((rpt.report_target_data->>'transactions')::int)::int as transactions,
        sum((rpt.report_target_data->>'domainAuthority')::int)::int as domainAuthority,
        sum((rpt.report_target_data->>'seoContribution')::int)::int as seoContribution,
        sum((rpt.report_target_data->>'overallWebVisits')::int)::int as overallWebVisits,
        sum((rpt.report_target_data->>'newVisitsPercentage')::int)::int as newVisitsPercentage
    FROM public.proc_targetreport as rpt
    WHERE (
        rpt.start_date >= (date_trunc('MONTH', start_dates))::date AND
        rpt.end_date <= (date_trunc('MONTH', end_dates))::date 
        )
        GROUP BY rpt.report_id,rpt.report_name,profileId
    )
 );
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

report_target_data 是json对象的列名

Answer 1

你可以尝试一个猴子黑客，只有当你有正常的数值或空字符串时才有效，例如:

t=# select (concat('0',('{"s":null}'::json->>'s')))::int;
 concat
--------
      0
(1 row)

t=# select (concat('0',('{"s":""}'::json->>'s')))::int;
 concat
--------
      0
(1 row)

t=# select (concat('0',('{"s":29}'::json->>'s')))::int;
 concat
--------
     29
(1 row)