postgres interval add variable current_setting not work-CodePudding

set session myconstants.test =  '10';
set session myconstants.testb =  '10 min';
SELECT now()::time - interval concat (current_setting('myconstants.selfName')::varchar,' min');
SELECT now()::time - INTERVAL '10 min';
set session myconstants.test =  '10';
SELECT now()::time - interval current_setting('myconstants.testb')::varchar;
SELECT now()::time - INTERVAL '10 min';

i want add variable in interval function,but current_setting not work..how could i solve it?i use postgres

CodePudding user response：

i found this solution,operator does not exist: timestamp with time zone integer in PostgreSql.

create function addit(timestamptz,int) returns timestamptz immutable language sql as $$
    select $1  interval '1 hour'*$2
$$;
create operator   (leftarg =timestamptz, rightarg =int, procedure=addit);

create function minusit(timestamptz,int) returns timestamptz immutable language sql as $$
    select $1  interval '-1 hour'*$2
$$;
create operator - (leftarg =timestamptz, rightarg =int, procedure=minusit);

create and - operator ,and execute

set session myconstants.testb =  -1;
select start_time ,start_time - current_setting('myconstants.testb')::integer  from besoccer_team bt ;
select start_time ,start_time - current_setting('myconstants.testb')::integer  from besoccer_team bt ;

start_time is timestamp type

set session myconstants.testb =  -1;
select  now()::timestamp  , now()::timestamp  - current_setting('myconstants.testb')::integer  from besoccer_team bt ;
select  now()::timestamp  , now()::timestamp  - current_setting('myconstants.testb')::integer  from besoccer_team bt ;

CodePudding user response：

You need to cast the variable value to an interval:

SELECT now()::time - current_setting('myconstants.testb')::interval

The prefix notation interval '....' only works with constants following it.

CodePudding user response：

You can also try make_interval.

SET session myconstants.test = 10;

SELECT
    now()   make_interval(mins => current_setting('myconstants.test')::int);