From: | Santosh Udupi <email(at)hitha(dot)net> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Not able to restore generated columns due to a function |
Date: | 2021-01-26 15:15:54 |
Message-ID: | CACLRvHbmJ=JVnWDHUUN9r=EY1_gZx-cnAPhAViLZGGW-PePNkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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,
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-01-26 15:21:31 | Re: Not able to restore generated columns due to a function |
Previous Message | David G. Johnston | 2021-01-23 16:21:14 | Re: LEFT or RIGHT JOIN - can't see where I'm going wrong? |