Not able to restore generated columns due to a function

Поиск
Список
Период
Сортировка
От Santosh Udupi
Тема Not able to restore generated columns due to a function
Дата
Msg-id CACLRvHbmJ=JVnWDHUUN9r=EY1_gZx-cnAPhAViLZGGW-PePNkw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Not able to restore generated columns due to a function
Список pgsql-novice
Дерево обсуждения
Hi,

I am trying to restore a database with a generated column. The generated column is created using the function below. This column doesn't get restored. Can you tell me what I am doing wrong in the function?

I am calling a function within a function. Could this be the issue? 

After the restore, if I update the base column "info" then the generated column - "completed_date_time" gets populated:

update jobs set info = info || jsonb_build_object('dq', info->>'dq')  .


------------------------------------
column definition is as below:

completed_date_time timestamptz GENERATED ALWAYS AS  
(task_completed_date(info->>'dd', info->>'qq', info->>'dp', info->>'ej', info->>'dq', info->>'ek') ) stored

-- 'info' is a jsonb column in the same table

------------------------------------------ My function --------------

-- task_completed_date() is defined as 'immutable', but calls another function:

;create or replace FUNCTION task_completed_date(completed_date_string text, completed_time_string text default null,
check_in_date_string text default null, check_in_time_string text default null,
check_out_date_string text default null, check_out_time_string text default null)

RETURNS timestamptz as
$$
begin

if check_out_date_string is not null and length(check_out_date_string)> 0 then
return
date_convert_date_time_string_to_timestamptz(check_out_date_string, check_out_time_string);

else

return date_convert_date_time_string_to_timestamptz(completed_date_string, completed_time_string);
 
end if;

exception when others then
return null;
 END ;
$$
LANGUAGE plpgsql immutable;
-----------------------------------------------------------

-- function date_convert_date_time_string_to_timestamptz is also defined as 'immutable':

create or replace FUNCTION date_convert_date_time_string_to_timestamptz(VARIADIC params text[])

RETURNS timestamptz as
$$
begin

 return array_to_string($1 , ' ')::timestamptz ;
exception when others then
return null;
 END ;
$$
LANGUAGE plpgsql immutable;
------------------------------------------------------------

Thank you,


В списке pgsql-novice по дате отправления: