postgreDatabase 无法识别创建的函数？SQL 状态：42883

Question

NULL我的函数已成功创建，但当我尝试使用它时，会收到错误消息：

错误：函数coalize2（字符变化、字符变化、角色变化、角色改变、角色变化）不存在SQL状态：42883提示：没有任何函数与给定的名称和参数类型匹配。您可能需要添加显式类型强制转换。

我的函数的目的是获取第一个NON NULL值，并将其与右侧的值进行比较。如果右侧的值不是NULL，则我将使用该值，如果是，则我使用COALESCE值。我的功能如下：

CREATE OR REPLACE FUNCTION coalesce2(variadic anyarray)
RETURNS anyelement AS
$BODY$
BEGIN
FOR i IN 1 .. array_upper($1, 1)
LOOP
IF $1[i] IS NOT NULL THEN
  RETURN COALESCE($1[i+1], $1[i]);
END IF;
END LOOP;
RETURN NULL;  
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;

以下是正在使用的功能：

聚结2(vw_header_to_node_13.substname、vw_head _to_node_12.substname，vw_header_to_node _11.substname，vw_header_to_node_10.subsetname、vw_head _to_node_9.subsetname，vw_header_to_node _8.subsetname，vw_header_to_node_7.subsetname，vw_header_to_node _6.subsetame，vw_header_to_node_5.subsetname，vw_header_to_node_4.subsetname，vw_header_to_node_2.subsetname、vw_head _to_node_1.subsetname、vw_header_to_node.subsetname，vw_header_to_node.setname）AS prctr2

我对函数没有太多的经验，我不明白为什么它不能识别新创建的函数。如有任何建议，不胜感激。

Answer 1

您需要将ARRAY传递给您的函数。

SELECT coalesce2(ARRAY['one', 'two', 'three']);

但是这个功能会很贵！我不建议在这样的内联SQL中使用PL/pgSQL函数。您最好使用CASE语句并创建一个不依赖于使用函数来格式化数据的新表。

并回答您关于如何使用CASE语句的第二个问题。

注意：您的SQL示例让我担心，因为看起来您正在执行10个LEFT OUTER JOIN语句，这是非常昂贵的。它还以"vw_"为前缀，这让我觉得你有10个VIEWS，而且VIEWS也可以隐藏非常糟糕的SQL。

我希望您没有使用VIEWS和许多LEFT OUTER JOIN语句。分析最好使用一个大的平面表，其中包含您需要的每一个面向列存储的属性，或者使用经典的Star Schema。转换数据一次，然后使用该输出进行分析。

回答问题。下面是一个示例表，其中包含一些与您相似的数据：

    drop table if exists foo;
    create table foo
    (id int not null,
     col1 text,
     col2 text,
     col3 text,
     col4 text,
     col5 text,
     col6 text,
     col7 text,
     col8 text,
     col9 text,
     col10 text)
     distributed by (id);
    insert into foo (id, col1, col2) values (1, 'x1', 'x1');
    insert into foo (id, col2, col3) values (2, 'x2', 'x2');
    insert into foo (id, col3, col4) values (3, 'x3', 'x3');
    insert into foo (id, col4, col5) values (4, 'x4', 'x4');
    insert into foo (id, col5, col6) values (5, 'x5', 'x5');
    insert into foo (id, col6, col7) values (6, 'x6', 'x6');
    insert into foo (id, col7, col8) values (7, 'x7', 'x7');
    insert into foo (id, col8, col9) values (8, 'x8', 'x8');
    insert into foo (id, col9, col10) values (9, 'x9', 'x9');
    insert into foo (id, col10) values (10, 'x10');

这就是它作为CASE语句的样子：

    select id, case when col1 is not null then coalesce(col2, col3, col4, col5, col6, col7, col8, col9, col10) 
            when col2 is not null then coalesce(col3, col4, col5, col6, col7, col8, col9, col10) 
            when col3 is not null then coalesce(col4, col5, col6, col7, col8, col9, col10) 
            when col4 is not null then coalesce(col5, col6, col7, col8, col9, col10)
            when col5 is not null then coalesce(col6, col7, col8, col9, col10) 
            when col6 is not null then coalesce(col7, col8, col9, col10) 
            when col7 is not null then coalesce(col8, col9, col10)
            when col8 is not null then coalesce(col9, col10)
            when col9 is not null then coalesce(col10)
            else col10 end
    from foo
    order by id;

现在作为SQL函数（而不是PL/pgSQL）：

    create or replace function fn_coalesce2(text, text, text, text, text, text, text, text, text, text) returns text as
    $$
    select  case when $1 is not null then coalesce($2, $3, $4, $5, $6, $7, $8, $9, $10) 
            when $2 is not null then coalesce($3, $4, $5, $6, $7, $8, $9, $10) 
            when $3 is not null then coalesce($4, $5, $6, $7, $8, $9, $10) 
            when $4 is not null then coalesce($5, $6, $7, $8, $9, $10)
            when $5 is not null then coalesce($6, $7, $8, $9, $10) 
            when $6 is not null then coalesce($7, $8, $9, $10) 
            when $7 is not null then coalesce($8, $9, $10)
            when $8 is not null then coalesce($9, $10)
            when $9 is not null then coalesce($10)
            else $10 end;
    $$
    language sql;
    select id, fn_coalesce2(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)
    from foo
    order by id;
    id | fn_coalesce2 
    ----+--------------
      1 | x1
      2 | x2
      3 | x3
      4 | x4
      5 | x5
      6 | x6
      7 | x7
      8 | x8
      9 | x9
     10 | x10
    (10 rows)

Answer 2

Greenplum不支持一些有趣的Postgres功能，因为它使用了旧的Postgres8.2版本。

1. 函数只能接收任意数组伪类型的ARRAY参数。在你的情况下，它可以是

   select coalesce2(ARRAY[v1.a, v2.a, v3.a]) AS prctr2  from v1,v2,v3
   

2. PL/SQL函数不能使用"anyarray"参数。我在尝试在我的Greenplum实例上测试您的功能时收到了以下错误

   ERROR:  plpgsql functions cannot take type anyarray
   

3. 提供了签名的函数可以用"sql"语言实现。它适用于任何类型/大小的数组。

   CREATE OR REPLACE FUNCTION coalesce2(variadic anyarray)
   RETURNS anyelement AS
   $BODY$
   select
       first_value(c) over(order by s)
   from (
       select c,s
       from (
           select
               unnest($1) c, 
               generate_series(1,array_upper($1,1)) s
       ) b
       where c is not null
   ) a;
   $BODY$
   LANGUAGE sql IMMUTABLE;
   

4. 我不知道使用这个函数的上下文，但看起来像是为了创建数组而连接视图/表。您可以看到我的函数"unnest"数组返回到表中，所以也许您可以创建更有效的SQL查询，而不是使用带有数组参数的函数。