arrays - 绕过字符串数组作为参数并在 PostgreSQL 中使用动态查询

Question

过去几年我在 PostgreSQL 工作，但我对数组概念以及如何在 PostgreSQL 中处理数组一无所知。我需要一个动态查询来选择多个表中的列，结果将在游标中，列名应该动态变化。

例如(如果用户需要 col1、col5、col6、col25，在一个多表中总共有 30 列)，因此 select 语句查询将动态地更改为:

select col1, col5,col6,col25 from table ....

另一个用户需要col2,col5,col7,col29,col26，select语句会动态变化为

select col2,col5,col7,col29,col26 from table .... and so on.

存储过程传递参数将是数组

create or replace function func_my_method(check_in character varying, sel character varying[])...

这个 sel[] 包含 like

          sel[0]:='col1_name'
          sel[1]:='col5_name'
          sel[2]:='col6_name'
          sel[3]:='col25_name'

所以首先我们必须将数组值拆分为单独的变量，这些变量将在 select 语句中假定为

'select'||col1, col5,col6,col25||'from......'

最后想简单说一下，我需要在参数中传递一个数组，并且必须分隔一个数组值，它将分配给单独的变量。这些变量将用于以动态方式选择语句

Answer 1

裸refcursor可以包含任意数量的列。虽然你需要一个特殊的声明来阅读它:FETCH ...

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 ...';
  RETURN ref;
END;
$func$;

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

http://rextester.com/ZCZT84224

注意:您可以在函数体中省略refcursor 参数和DECLARE 参数。这样 PostgreSQL 将为 refcursor 生成一个(无冲突的)名称，调用 SELECT func_my_method(...) 时将返回该名称。您将需要在 FETCH ... 语句中使用该名称。

更新:如果您想完全限定(某些)列(即也写表名和列)，您需要:

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 ...';
  RETURN ref;
END;
$func$;

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

(这会将 sel 参数拆分为 上完全限定名称的“部分”。 - 但有一个缺点:表和列名称不能包含 .)

或者:

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 ...';
  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;

(但这有一个令人不安的后果:由于数组需要是矩形的，所以所有列子数组的尺寸都必须完全相同；因此您需要为所有列提供表名，或者都不为任何列提供表名.)

http://rextester.com/JNI24740