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

Question

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
);

Answer 1

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.