Блог

Главная - Вопросы по программированию - Как можно выполнить математику переноса при переполнении в Postgres?

Как можно выполнить математику переноса при переполнении в Postgres?

#sql #postgresql #plpgsql

#sql #postgresql #plpgsql

Вопрос:

  • Учитывая следующий пример функции:
 CREATE OR REPLACE FUNCTION add_max_value(_x BIGINT)
    RETURNS BIGINT
    LANGUAGE sql
    AS $$
        SELECT 9223372036854775807   _x;
    $$;
  • Если эта функция вызывается с любым положительным значением, возвращается следующая ошибка:
 SELECT add_max_value(1); -- Expecting -9223372036854775808 if math wrapped

-- SQL Error [22003]: ERROR: bigint out of range
  • Как я могу выполнить математику с переносом при переполнении целых чисел в Postgres?
    • Пожалуйста, обратите внимание:
      1. Я хочу сделать это в базе данных, а не в приложении
      2. Я не хочу, чтобы он повышал значение до целого числа произвольной точности ( NUMERIC )
      3. Хотя в примере выполняется только сложение, на практике меня интересуют и другие операции

Комментарии:

1. Действительно ли функция выдает ошибку даже при отрицательном вводе?

2. @a_horse_with_no_name Во втором блоке кода есть ожидаемый результат. Повторяю, такое же поведение переноса, которое предлагают многие основные языки (C, Java).

3. @jarlh Вы правы, хороший вызов; положительные целые числа являются проблемой в примере. Вопрос обновлен.

4. Я не вижу другого способа, кроме как использовать промежуточную numeric переменную в PL / pgSQL и «переносить» вручную

Ответ №1:

В качестве функции SQL нет способа. Функции SQL не могут обрабатывать исключения. Но plpgsql функция может:

 CREATE OR REPLACE FUNCTION add_max_value(_x BIGINT)
    RETURNS BIGINT
    LANGUAGE plpgsql
    AS $$
    declare 
        bigx bigint; 
    begin 
        bigx = 9223372036854775807   _x; 
        return bigx;
    exception
        when sqlstate '22003' then
             return (9223372036854775807::numeric   _x - 2^64)::bigint;
    end;
    $$;

Комментарии:

1. Вы бы знали намного лучше, чем я. Но да, вы также могли бы просто «вернуть 1».

2. Никаких проблем. Специфика не имеет значения, это концепция обработки исключения, и при этом особенно специфического исключения.

3. Было бы эффективнее сначала присвоить результат добавления numeric переменной, а затем проверить, больше ли она 9223372036854775807 ? Обработка исключений довольно дорогая

4. Да, за надуманный пример, где представлены исключение составляют преднамеренно вызванные, но я думаю, что не для общего случая арифметическое переполнение. Я не думаю, что проблема с OP связана с конкретным представленным примером, но с общим случаем. Если проблема в конкретном примере, то более эффективно просто перейти прямо к сброшенному значению с помощью SQL-функции оператора «select (9223372036854775807::numeric _x — 2 ^ 64)::bigint;»

Ответ №2:

Это легко входит в пятерку самых расточительных SQL, которые я когда-либо писал:

 create or replace function add_max_value(_x bigint)
  returns bigint
  language sql
as $$
with recursive inputs as (
  select s.rn, r.a::int, s.b::int, (r.a::int   s.b::int) % 2 as sumbit, 
        (r.a::bit amp; s.b::bit)::int as carry  
    from regexp_split_to_table((9223372036854775807::bit(64))::text, '') with ordinality as r(a, rn)
         join regexp_split_to_table((_x::bit(64))::text, '') with ordinality as s(b, rn)
           on s.rn = r.rn
), addition as (
  select rn, sumbit, sumbit as s2, carry, carry as upcarry
    from inputs
   where rn = 64
  union 
  select i.rn, i.sumbit, (i.sumbit   a.upcarry) % 2, i.carry, 
         (i.carry::bit | a.upcarry::bit)::int
    from addition a
    join inputs i on i.rn = a.rn - 1
)
select (string_agg(s2::text, '' order by rn)::bit(64))::bigint
  from addition
$$;