CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Nash
Nash

Reputation: 23

PostgreSQL - Reference a variable value in select statement interval

I'm trying to calculate a value in a variable then use that value to drive a interval statement further on.

Running on PG 9.6. We can get to PG 13 if there is something added since then we can use.

Example:

CREATE OR REPLACE FUNCTION public.demofunc(
    int,
    int,
    date)
    returns date
    LANGUAGE plpgsql AS $$
    DECLARE 
       diffvalue text;
       returndate date;    
    BEGIN
    diffvalue:= ($2 - $1);
    returndate := (SELECT $3 - INTERVAL 'diffvalue days');

    return returndate;
    END$$;

This is a simplified version of what I want to achieve, the interval of the numbers of days to remove is based on a calculation that's done in the function. So its not a simple A - B but the end result is

I can't seem to get the function to resolve the "diffvalue" before running the select statement. I've tried using int and text and varchar and concat the string for anything.

Sorry for anything obvious i might be missing, only started this today.

Upvotes: 0

Views: 237

Answers (1)

user330315
user330315

Reputation:

If your diffvalue is in reality an integer, you can use the make_interval function to create an interval based on that number:

returndate := $3 + make_interval(days => diffvalue);

If diffvalue is a decimal that can represent fractional days, make_interval can't be used.

In that case you can multiply an interval of 1 day with that value:

returndate := $3 + interval '1 day' * diffvalue;

Upvotes: 1

Related Questions