Thread: Not able to restore generated columns due to a function
Hi,
------------------------------------------ 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
exception when others then
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
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 thenreturndate_convert_date_time_string_to_timestamptz(check_out_date_string, check_out_time_string);elsereturn 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
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 thenreturn null;END ;
$$
LANGUAGE plpgsql immutable;
------------------------------------------------------------
LANGUAGE plpgsql immutable;
------------------------------------------------------------
Thank you,
On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <email@hitha.net> wrote:
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?
Didn't look too deeply but normally failures of this nature mean you didn't schema-qualify your function names, either in the code body or with a SET attached to the create function command.
To compound matters you are ignoring all errors by "exception when others". You should avoid "exception" if possible, including by testing for valid data first instead of letting the called function fail.
Your functions also are not immutable due to being sensitive to timezone settings.
David J.
Thank you David for your suggestions. I will fix the function and try.
On Tue, Jan 26, 2021 at 7:21 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <email@hitha.net> wrote: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?Didn't look too deeply but normally failures of this nature mean you didn't schema-qualify your function names, either in the code body or with a SET attached to the create function command.To compound matters you are ignoring all errors by "exception when others". You should avoid "exception" if possible, including by testing for valid data first instead of letting the called function fail.Your functions also are not immutable due to being sensitive to timezone settings.David J.
I schema qualified the function and it worked!! Thanks a lot!!
On Tue, Jan 26, 2021 at 7:42 AM Santosh Udupi <email@hitha.net> wrote:
Thank you David for your suggestions. I will fix the function and try.On Tue, Jan 26, 2021 at 7:21 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <email@hitha.net> wrote: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?Didn't look too deeply but normally failures of this nature mean you didn't schema-qualify your function names, either in the code body or with a SET attached to the create function command.To compound matters you are ignoring all errors by "exception when others". You should avoid "exception" if possible, including by testing for valid data first instead of letting the called function fail.Your functions also are not immutable due to being sensitive to timezone settings.David J.