Using array in dynamic commands inside PL/pgSQL function

Question

In a PL/pgSQL function, I am creating a view using the EXECUTE statement. The where clause in the view takes as input some jenkins job names. These job names are passed to the function as a comma-separated string. They are then converted to an array so that they can be used as argument to ANY in the where clause. See basic code below:

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;

The creation of the function is successful but an error message is returned when I try to call the function. See below:

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

It seems like there is something going wrong with quotes & the passing of an array of string. I tried all following options with the same result:

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

or

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

or

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

Any ideas? Thank you

Why are you creating a view in the first place? If you run that query directly you can pass parameter values through placeholders and don't need to bother about formatting literals correctly. — user330315
– user330315, Commented Jun 7, 2018 at 13:33
quote_ident() is for identifiers, i.e. references to tables and columns. You probably want quote_literal() instead. — Nick Barnes
– Nick Barnes, Commented Jun 7, 2018 at 13:36

score 2 · Accepted Answer · 2018-06-07 14:08:43Z

There is no need for dynamic SQL at all. There isn't even the need for PL/pgSQL to do this:

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;

You could do this with PL/pgSQL as well, you just need to use RETURN QUERY WITH ....

Collectives™ on Stack Overflow

Using array in dynamic commands inside PL/pgSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related