Блог

Postgresql 9.6.x Я получаю ошибку с функцией postgresql, в которой я записываю журнал при каждом изменении таблицы. Все это отлично работало, пока я не добавил эту функцию, в которой я записываю текущий идентификатор пользователя, используя функцию current_setting в postgresql. Я настраиваю текущего пользователя на транзакции в фоновом режиме следующим образом:

  select  set_config('myvars.active_user_id', '2123', true)
 

Вся эта функциональность отлично работает, за исключением случаев, когда пользователь не настроен. Это происходит, когда таблицы обновляются системными запросами серверной части, и в этом случае значение «myvars.active_user_id» равно null.
Я хочу, чтобы он был равен нулю, когда он не установлен. Поле Идентификатор пользователя в журнале может быть заполнено со значением null.
Похоже, он пытается преобразовать null в пустую строку и поместить ее в целочисленную переменную, которая ему не нравится.
Это, похоже, какая-то странная проблема, характерная для функций с триггерами. Я, должно быть, делаю что-то не так, потому что, насколько я знаю, присвоение нулевого значения через выберите…в» — это не проблема.
Ошибка, которую я получаю в этом случае, такова:

 PSQLException: ERROR: invalid input syntax for integer: ""
 

Я добавил инструкции трассировки, и они находятся в этой строке:

 EXECUTE 'select current_setting(''myvars.active_user_id'', true)  ' into log_user_id;
 

Я не понимаю, почему в этой настройке он расстраивается из-за нулевого значения. Но, похоже, он ограничен этим типом триггерной функции. Ниже, по сути, приведена функция, которую я использую

 CREATE OR REPLACE FUNCTION update_log() RETURNS TRIGGER AS $update_log$
DECLARE
    logid int;
   log_user_id int;
BEGIN
EXECUTE 'select current_setting(''myvars.active_user_id'', true)  ' into log_user_id;

  IF (TG_OP='DELETE') THEN
            EXECUTE 'select nextval(''seq_log'') ' into logid;
--            INSERT INTO log ....
            RETURN NULL;
  ELSIF (TG_OP='INSERT') THEN
            EXECUTE 'select nextval(''seq_log'') ' into logid;
--            INSERT INTO log ....
            RETURN NEW;

  ELSIF (TG_OP='UPDATE') THEN
--            INSERT INTO log ....
        END IF;
  END IF;
        RETURN NULL; 
    END;
$log$ LANGUAGE plpgsql;
 

Есть какие-нибудь мысли?

Переменные GUC (Глобальная настройка пользователя), такие как ваши myvars.active_user_id , внутренне не могут быть обнулены. Он содержит текст или пустой текст. Эти переменные не могут хранить значение NULL. Поэтому, когда вы сохраняете значение NULL, сохраняется пустая строка, и эта пустая строка возвращается из функции current_setting .

В Postgres (и любой базе данных без Oracle) значение NULL не является пустой строкой, а пустая строка не является нулем.

Таким образом, эта ошибка ожидается:

 postgres=# do $
declare x int;
begin
  perform set_config('x.xx', null, false);
  execute $_$ select current_setting('x.xx', true) $_$ into x;
end;
$;
ERROR:  invalid input syntax for integer: ""
CONTEXT:  PL/pgSQL function inline_code_block line 5 at EXECUTE
 

Сначала вам нужно проверить результат и заменить пустую строку на NULL:

 create or replace function nullable(anyelement)
returns anyelement as $
  select case when $1 = '' then NULL else $1 end;
$ language sql;

do $
declare x int;
begin
  perform set_config('x.xx', null, false);
  execute $_$ select nullable(current_setting('x.xx', true)) $_$ into x;
end;
$;
DO
 

У @Laurenz Albe есть большая правда в вашем комментарии. Используйте динамический SQL ( execute команда) только тогда, когда это необходимо. Это не тот случай. Таким образом, ваш код должен выглядеть следующим образом:

 do $
declare x int;
begin
  perform set_config('x.xx', null, false);
  x := nullable(current_setting('x.xx', true)); 
end;
$;
DO
 

Примечание: Существует функция сборки nullif , поэтому ваш код может выглядеть так (и, конечно, функциональность сборки должна быть предпочтительнее):

 do $
declare x int;
begin
  perform set_config('x.xx', null, false);
  x := nullif(current_setting('x.xx', true), ''); 
end;
$;
DO