dongzhun1857 2015-01-19 21:52
浏览 116
已采纳

将数组传递给PL / pgSQL存储过程时出错

I have this procedure:

CREATE OR REPLACE FUNCTION get_saldo_conto(idUtente integer, idConto integer, categories int[], end_date date) RETURNS numeric(8,2) AS $$
DECLARE 
row_transazione transazione%ROWTYPE;
saldoIniziale numeric(8,2);
totale numeric(8,2);

BEGIN

    saldoIniziale = (SELECT saldo_iniziale FROM conto WHERE id = idConto AND id_utente = idUtente);
    totale = 0;

        FOR row_transazione IN SELECT * 
                               FROM transazione 
                               LEFT JOIN categoria ON id_categoria = categoria.id 
                               WHERE id_conto = idConto 
                               AND transazione.id_utente = idUtente 
                               AND id_categoria = ANY (categories) 
                               AND data <= end_date
        LOOP
            IF(row_transazione.tipo = 'entrata') THEN
                totale = totale + row_transazione.importo;      
            ELSE
                totale = totale - row_transazione.importo;  
            END IF;     
        END LOOP;

    RETURN (saldoIniziale + totale) AS saldo_corrente;

END;

$$ LANGUAGE 'plpgsql';

When I call it with for example with

SELECT get_saldo_conto('1','19','{1,2,4,5,6}', '20/01/2015');

gives me an error

ERROR: op ANY/ALL (array) requires array on right side

Am I doing something wrong passing the array? I tried also passing like '{1,2,4,5,6}'::int[] with no success.

CREATE TABLE transazione(
    id SERIAL PRIMARY KEY,
    tipo VARCHAR(7) NOT NULL CHECK(tipo IN('spesa', 'entrata')),
    importo NUMERIC(8,2) NOT NULL,
    descrizione VARCHAR(40),
    data DATE DEFAULT CURRENT_TIMESTAMP,
    id_conto INTEGER NOT NULL REFERENCES conto(id) ON UPDATE CASCADE ON DELETE CASCADE,
    id_utente INTEGER NOT NULL REFERENCES utente(id) ON UPDATE CASCADE ON DELETE CASCADE,
    id_categoria INTEGER REFERENCES categoria(id) ON UPDATE CASCADE ON DELETE SET NULL
);
  • 写回答

1条回答 默认 最新

  • duandang6111 2015-01-19 23:20
    关注

    Debug

    You defined the row variable

    row_transazione transazione%ROWTYPE;
    

    But then you assign SELECT * FROM transazione LEFT JOIN categoriato it, which obviously does not fit the type.

    The error message you display, however, does not make sense. The only case of ANY/ALL in your code looks correct. Are you sure you are calling the function you think you are calling? Investigate with:

    SELECT n.nspname, p.proname
         , pg_get_function_identity_arguments(p.oid) AS params
    FROM   pg_proc p
    JOIN   pg_namespace n ON n.oid = p.pronamespace
    WHERE  p.proname = 'get_saldo_conto';
    

    .. to find all functions with the given name. And

    SHOW search_path;
    

    .. to check if the search_path leads to the right one.

    Audited function

    Your function would work like this:

    CREATE OR REPLACE FUNCTION get_saldo_conto(_id_utente  integer
                                             , _id_conto   integer
                                             , _categories int[]
                                             , _end_date   date)
      RETURNS numeric(8,2) AS
    $func$
    DECLARE 
       row_trans     transazione;
       saldoIniziale numeric(8,2) := (SELECT saldo_iniziale
                                      FROM   conto
                                      WHERE  id_utente = _id_utente
                                      AND    id = _id_conto);
       totale        numeric(8,2) := 0;
    BEGIN
       FOR row_trans IN
          SELECT t.* 
          FROM   transazione t
       -- LEFT   JOIN categoria ON id_categoria = categoria.id  -- useless anyway
          WHERE  t.id_utente = _id_utente 
          AND    t.id_conto = _id_conto 
          AND    t.id_categoria = ANY (_categories) 
          AND    data <= _end_date
       LOOP
          IF row_trans.tipo = 'entrata' THEN
             totale := totale + row_trans.importo;      
          ELSE
             totale := totale - row_trans.importo;  
          END IF;     
       END LOOP;
    
       RETURN (saldoIniziale + totale);  -- AS saldo_corrente -- no alias here!
    END    
    $func$ LANGUAGE plpgsql;
    

    But that's just to showcase the syntax. The function is expensive nonsense.

    Superior simple query

    Replace with a simple SELECT:

    SELECT COALESCE((
              SELECT saldo_iniziale
              FROM   conto
              WHERE  id_utente = _id_utente 
              AND    id = _id_conto), 0)
         + COALESCE((
              SELECT sum(CASE WHEN tipo = 'entrata' THEN importo ELSE 0 END)
                   - sum(CASE WHEN tipo = 'spesa'   THEN importo ELSE 0 END)
              FROM   transazione 
              WHERE  id_conto = _id_conto
              AND    id_utente = _id_utente 
              AND    id_categoria = ANY (_categories) 
              AND    data <= _end_date), 0) AS saldo;
    

    Assuming rows in conto are unique on (id_utente,id).
    Depending on implementation details the best query can vary. I chose a variant that is safe against missing rows and NULL values. Either way, a plain query should be much faster than a loop over all rows.

    You can wrap this into a function (SQL or plpgsql) if you want.

    Aside:

    • transazione.tipo should rather be an enum type - or even just "char" or boolean. varchar(7) is a waste for tag with two possible values.

    • And data DATE DEFAULT CURRENT_TIMESTAMP should really be data DATE DEFAULT CURRENT_DATE.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 PFENet的预训练权重
  • ¥15 程序哪有错误怎么改?
  • ¥15 交换机和交换机之间的链路带宽以及主机带宽的理解
  • ¥15 ai创想家对战模式代码
  • ¥15 集合A由3个2行4列二维数组构成,从集合A中任意取一个二维数组元素、如果该二维数组元素的对应列位置的上、下两数都是奇数,而且仅有2个列是奇数/奇数,则该数组有意义,并放入集合B中打印输出。
  • ¥15 电信IPV6 无法外网访问吗
  • ¥15 有偿求效果比较好的遥感影像匹配的c++代码
  • ¥15 博主,你好,我下载了你的智能网联汽车辅助驾驶安全信息检测系统,现在不会运行,可以教我吗,
  • ¥15 怎么在excle输入下列公式
  • ¥15 Arduino,利用modbus的RS485协议,进行对外置的温湿度传感器进行数据读取