Ошибка передачи массива в хранимую процедуру PL / pgSQL

Question

Ошибка передачи массива в хранимую процедуру PL / pgSQL

У меня есть эта процедура:

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

Когда я называю это, например, с

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

дает мне ошибку

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

Я делаю что-то неправильно, передавая массив?
Я также попытался передать как ‘{1,2,4,5,6}’ :: int [] безуспешно.

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

php plpgsql postgresql prepared-statement sql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

отлаживать

Вы определили переменную строки

row_transazione transazione%ROWTYPE;

Но тогда вы назначаете SELECT * FROM transazione LEFT JOIN categoriato это, что, очевидно, не соответствует типу.

Однако отображаемое сообщение об ошибке не имеет смысла. Единственный случай ANY/ALL в вашем коде выглядит правильно. Вы уверены, что вызываете функцию, которую, как вы думаете, вызываете? Расследовать с:

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

.. найти все функции с заданным именем. А также

SHOW search_path;

.. проверить, если search_path приводит к правильному.

Проверяемая функция

Ваша функция будет работать так:

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;

Но это только для демонстрации синтаксиса. Функция дорогая ерунда.

Улучшенный простой запрос

Заменить на простой 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;

Предполагая строки в conto уникальны на (id_utente,id),
В зависимости от деталей реализации, лучший запрос может варьироваться. Я выбрал вариант, который безопасен от пропущенных строк и значений NULL. В любом случае простой запрос должен быть намного быстрее, чем цикл по всем строкам.

Вы можете обернуть это в функцию (SQL или plpgsql), если хотите.

В сторону:

transazione.tipo скорее должен быть enum типа — или даже просто "char" или же boolean, varchar(7) это пустая трата тега с двумя возможными значениями.
А также ~~data DATE DEFAULT CURRENT_TIMESTAMP~~ должно быть data DATE DEFAULT CURRENT_DATE,

2