PostgreSQL refcursor execute, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table check_in(
    col1 serial primary key,
    col2 int
);

insert into check_in(col2)
select g
from   generate_series(1, 10) g;

CREATE OR REPLACE FUNCTION func_my_method(check_in text, sel text[], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg(quote_ident(c), ', ')
                                     FROM   unnest(sel) c) || ' FROM ' || quote_ident(check_in);
  RETURN ref;
END;
$func$;

SELECT func_my_method('check_in', ARRAY['col1', 'col2'], 'sample_name');
FETCH ALL IN sample_name;

CREATE OR REPLACE FUNCTION func_my_method2(check_in text, sel text[], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg((SELECT string_agg(quote_ident(c), '.')
                                                        FROM   unnest(string_to_array(fq, '.')) c), ', ')
                                     FROM   unnest(sel) fq) || ' FROM ' || quote_ident(check_in);
  RETURN ref;
END;
$func$;

SELECT func_my_method2('check_in', ARRAY['col1', 'check_in.col2'], 'sample_name2');
FETCH ALL IN sample_name2;

CREATE OR REPLACE FUNCTION func_my_method3(check_in text, sel text[][], ref refcursor)
  RETURNS refcursor
  LANGUAGE plpgsql
AS $func$
BEGIN
  OPEN ref FOR EXECUTE 'SELECT ' || (SELECT string_agg((SELECT string_agg(quote_ident(sel[i][j]), '.')
                                                        FROM   generate_subscripts(sel, 2) j), ', ')
                                     FROM   generate_subscripts(sel, 1) i) || ' FROM ' || quote_ident(check_in);
  RETURN ref;
END;
$func$;

SELECT func_my_method3('check_in', ARRAY[ARRAY['check_in', 'col1'], ARRAY['check_in', 'col2']], 'sample_name3');
FETCH ALL IN sample_name3;


absolute service time: 0,39 sec