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

问题描述

是否有可能使用这样的Postgres里？这是PL / SQL是我想要做的例子：

 程序CREATE_PAYMENT（P_AMOUNT在数量上，
                         P_INVOICE_LIST IN SIMPLEARRAYTYPE，
                         P_AMOUNT_LIST在NUMBER_TABLE  - 大量的合格名单
                            。
                            。
                            。）s_chk_amnt数;invoice_list SIMPLEARRAYTYPE;
amount_list NUMBER_TABLE;开始
       - 检查是否量列表为空或包含零
    如果p_amount_list不为null，p_amount_list.COUNT＆LT;＆GT; 0 THEN
      FOR R IN p_amount_list.FIRST..p_amount_list.LAST
      循环
        s_chk_amnt：= s_chk_amnt + p_amount_list（R）;
      END LOOP;
    万一;
 

我可以声明字符和数字作为函数的输入参数列表清单？结果
我发现与 FOREACH元素一些例子，但我不知道如何抓住从号码列表中某个元素像甲骨文与 p_amount_list（R） 。

解决方案

CREATE OR REPLACE FUNCTION CREATE_PAYMENT（p_amount_list数字[]）
  返回数值AS
$ FUNC $
宣布
   s_chk_amnt数字：= 0; - 初始化变量！
    - [R数字;
开始
- 如果p_amount_list＆LT;＆GT; {}，那么 - 只是噪音
   FOREACH R IN ARRAY p_amount_list
   循环
      s_chk_amnt：= s_chk_amnt + R;
   END LOOP;
- 万一;RETURN s_chk_amnt;
结束
$ FUNC $ LANGUAGE PLPGSQL


主要分

• 甲骨文公司数量是的 数字 Postgres里。但是，如果你没有小数位数，你宁愿在Postgres里使用 INT 或 BIGINT 。 关于Oracle和Postgres的之间的类型映射。




• Postgres没有表类型像甲骨文的。使用数组类型的数字数组在这种情况下：数字[]




• 这位前pression 如果p_amount_list＆LT;＆GT; {}... 将排除NULL和空阵的一致好评。没有必要在你原来的第二检查等。但如果是没有必要的。对于空或空数组，循环无论如何都不会进入。




• 研究持有元素本身，而不是它的下标。 （因此，它必须是一个匹配的数据类型。）

这正好证明了PLPGSQL功能的 FOREACH 循环的基本语法。否则，将昂贵的废话的，更好地更换了更简单，更快速：

SELECT SUM（ELEM）AS sum_amount
从UNNEST（p_amount_list）ELEM;


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;

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

Major points

• Oracle's number is numeric in Postgres. But if you don't have fractional digits, you'd rather use int or bigint in Postgres. About type mapping between Oracle and Postgres.

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

• 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 holds the element itself, not an index to it. (Therefore it must be a matching data type.)

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屋！