时间戳

首页 > 解决方案 > 在 PL/pgSQL 函数内的动态命令中使用数组

postgresql - 在 PL/pgSQL 函数内的动态命令中使用数组

问题描述

在 PL/pgSQL 函数中，我正在使用 EXECUTE 语句创建视图。视图中的 where 子句将一些 jenkins 作业名称作为输入。这些作业名称作为逗号分隔的字符串传递给函数。然后将它们转换为数组，以便它们可以用作 where 子句中 ANY 的参数。请参阅下面的基本代码：

CREATE OR REPLACE FUNCTION FETCH_ALL_TIME_AGGR_KPIS(jobs VARCHAR)
RETURNS SETOF GenericKPI AS $$
DECLARE
job_names TEXT[];
BEGIN
job_names = string_to_array(jobs,',');   

EXECUTE 'CREATE OR REPLACE TEMP VIEW dynamicView AS ' || 
'with pipeline_aggregated_kpis AS (
select
    jenkins_build_parent_id,
    sum (duration) as duration
from test_all_finished_job_builds_enhanced_view where job_name = ANY (' || array(select quote_ident(unnest(job_names))) || ') and jenkins_build_parent_id is not null 

group by jenkins_build_parent_id)
select ' || quote_ident('pipeline-job') || ' as job_name, b1.jenkins_build_id, pipeline_aggregated_kpis.status, pipeline_aggregated_kpis.duration FROM job_builds_enhanced_view b1 INNER JOIN pipeline_aggregated_kpis ON (pipeline_aggregated_kpis.jenkins_build_parent_id = b1.jenkins_build_id)';

RETURN QUERY (select
count(*) as total_executions,    
round(avg (duration) FILTER (WHERE status = 'SUCCESS')::numeric,2) as average_duration
from dynamicView);  
END
$$
LANGUAGE plpgsql;

函数创建成功，但尝试调用函数时返回错误消息。见下文：

eea_ci_db=> select * from FETCH_ALL_TIME_AGGR_KPIS('integration,test');
ERROR:  malformed array literal: ") and jenkins_build_parent_id is not null 
 group by jenkins_build_parent_id)
select "
LINE 7: ...| array(select quote_ident(unnest(job_names))) || ') and jen...
                                                         ^
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function fetch_all_time_aggr_kpis(character varying) line 8 at EXECUTE

引号和字符串数组的传递似乎出了点问题。我尝试了以下所有选项，结果相同：

where job_name = ANY (' || array(select quote_ident(unnest(job_names))) || ') and jenkins_build_parent_id is not null

或者

where job_name = ANY (' || quote_ident(job_names)) || ') and jenkins_build_parent_id is not null

或者

where job_name = ANY (' || job_names || ') and jenkins_build_parent_id is not null

有任何想法吗？谢谢

标签： postgresql

解决方案

根本不需要动态 SQL。甚至不需要 PL/pgSQL 来执行此操作：

CREATE OR REPLACE FUNCTION FETCH_ALL_TIME_AGGR_KPIS(jobs VARCHAR)
  RETURNS SETOF GenericKPI 
AS 
$$
  with pipeline_aggregated_kpis AS (
    select jenkins_build_parent_id,
           sum (duration) as duration
    from test_all_finished_job_builds_enhanced_view 
    where job_name = ANY (string_to_array(jobs,',')) 
      and jenkins_build_parent_id is not null 
    group by jenkins_build_parent_id
  ), dynamic_view as (
    select "pipeline-job" as job_name, 
            b1.jenkins_build_id, 
            pipeline_aggregated_kpis.status, 
            pipeline_aggregated_kpis.duration 
    FROM job_builds_enhanced_view b1 
      JOIN pipeline_aggregated_kpis 
        ON pipeline_aggregated_kpis.jenkins_build_parent_id = b1.jenkins_build_id
  )
  select count(*) as total_executions,    
         round(avg (duration) FILTER (WHERE status = 'SUCCESS')::numeric,2) as average_duration
  from dynamic_view;  
$$
language sql;

你也可以使用 PL/pgSQL 来做到这一点，你只需要使用RETURN QUERY WITH ....

推荐阅读