列表/数组的 PL/pgSQL 控制结构 [英] PL/pgSQL control structures for lists / arrays

问题描述

是否可以在 Postgres 中使用这样的东西?这是我想要做的 PL/SQL 示例:

Is it possible to use something like this in Postgres? This is the example from PL/SQL what I want to do:

PROCEDURE CREATE_PAYMENT(P_AMOUNT IN NUMBER,
                         P_INVOICE_LIST IN SIMPLEARRAYTYPE, 
                         P_AMOUNT_LIST IN NUMBER_TABLE -- pass list of amounts
                            .
                            .
                            .)

s_chk_amnt              NUMBER;

invoice_list SIMPLEARRAYTYPE;
amount_list NUMBER_TABLE;

BEGIN
      -- check if amount list is null or contains zeros
    IF p_amount_list IS NOT NULL AND p_amount_list.COUNT <> 0 THEN
      FOR r IN p_amount_list.FIRST..p_amount_list.LAST
      LOOP
        s_chk_amnt := s_chk_amnt + p_amount_list(r);
      END LOOP;
    END IF;

我可以将字符列表和数字列表声明为函数输入参数吗?
我找到了一些带有 FOREACH element 的例子，但我不知道如何像在 Oracle 中使用 p_amount_list(r) 从数字列表中获取某个元素.

Can I declare a list of characters and list of numbers as function input parameters?
I have found some examples with FOREACH element but I don't know how to grab a certain element from number list like in Oracle with p_amount_list(r).

推荐答案

CREATE OR REPLACE FUNCTION CREATE_PAYMENT(p_amount_list numeric[])
  RETURNS numeric AS
$func$
DECLARE
   s_chk_amnt numeric := 0; -- init variable!
   r          numeric;
BEGIN
-- IF p_amount_list <> '{}' THEN  -- just noise
   FOREACH r IN ARRAY p_amount_list
   LOOP
      s_chk_amnt := s_chk_amnt + r;
   END LOOP;
-- END IF;

RETURN s_chk_amnt;
END
$func$ LANGUAGE plpgsql

要点

• Oracle 的 number 是 数字.但是如果你没有小数，你更愿意在 Postgres 中使用 int 或 bigint .关于 Oracle 和 Postgres 之间的类型映射.

  Postgres 没有 "表类型像甲骨文.使用 array types，一个 numeric 的数组在这种情况下:numeric[].

  Postgres does not have "table types" like Oracle. Use array types, an array of numeric in this case: numeric[].

  表达式IF p_amount_list <>'{}' ... 将排除 NULL 和空数组"等.无需像原件那样进行第二次检查.但是 IF 根本不需要.对于 NULL 或空数组，无论如何都不会进入循环.

  The expression IF p_amount_list <> '{}' ... would rule out NULL and "empty array" alike. No need for a second check like in your original. But the IF is not needed at all. For NULL or empty array, the loop isn't entered anyway.

  r 保存元素本身，而不是它的索引.(因此它必须是匹配的数据类型.)

  r holds the element itself, not an index to it. (Therefore it must be a matching data type.)

  这将演示 plpgsql 函数中 FOREACH 循环的基本语法.否则，这将是昂贵的废话，最好用更简单、更快捷的方式代替:

  This goes to demonstrate basic syntax of a FOREACH loop in a plpgsql function. Otherwise it would be expensive nonsense, better replaced with a much simpler and faster:

  SELECT sum(elem) AS sum_amount
  FROM   unnest(p_amount_list) elem;
  

  这篇关于列表/数组的 PL/pgSQL 控制结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！