public inbox for [email protected]
help / color / mirror / Atom feedNot able to restore generated columns due to a function
4+ messages / 2 participants
[nested] [flat]
* Not able to restore generated columns due to a function
@ 2021-01-26 15:15 Santosh Udupi <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Santosh Udupi @ 2021-01-26 15:15 UTC (permalink / raw)
To: [email protected]
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,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to restore generated columns due to a function
@ 2021-01-26 15:21 David G. Johnston <[email protected]>
parent: Santosh Udupi <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2021-01-26 15:21 UTC (permalink / raw)
To: Santosh Udupi <[email protected]>; +Cc: pgsql-novice <[email protected]>
On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <[email protected]> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to restore generated columns due to a function
@ 2021-01-26 15:42 Santosh Udupi <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Santosh Udupi @ 2021-01-26 15:42 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-novice <[email protected]>
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 <
[email protected]> wrote:
> On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <[email protected]> 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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Not able to restore generated columns due to a function
@ 2021-01-26 15:52 Santosh Udupi <[email protected]>
parent: Santosh Udupi <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Santosh Udupi @ 2021-01-26 15:52 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-novice <[email protected]>
I schema qualified the function and it worked!! Thanks a lot!!
On Tue, Jan 26, 2021 at 7:42 AM Santosh Udupi <[email protected]> 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 <
> [email protected]> wrote:
>
>> On Tue, Jan 26, 2021 at 8:16 AM Santosh Udupi <[email protected]> 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.
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-01-26 15:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-26 15:15 Not able to restore generated columns due to a function Santosh Udupi <[email protected]>
2021-01-26 15:21 ` David G. Johnston <[email protected]>
2021-01-26 15:42 ` Santosh Udupi <[email protected]>
2021-01-26 15:52 ` Santosh Udupi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox