How to create a variable which stores a function result within another function in postgresql?

Question

I’m a big noob.

Function B is part of the body of function A.

Inside a function A, I would like to create a variable to store the function B result in postgresql, and to use that result as a comparison argument just after, still inside function A.

I rode documentation and tried 2 approaches, but can’t get what I would like:

The variable I want to create is named remaining_time in the example below.

1st try:

CREATE OR REPLACE FUNCTION remaining_period(start DATE, months INT) RETURNS INTERVAL AS
$$
DECLARE
  remaining_time = SELECT AGE((start + (months || ' months')::INTERVAL)::DATE, NOW()::DATE);
BEGIN
  CASE WHEN remaining_time > INTERVAL '0 day' THEN remaining_time
       ELSE INTERVAL '0 day'
END
$$
LANGUAGE sql IMMUTABLE;

2nd try:

CREATE OR REPLACE FUNCTION remaining_period(start DATE, months INT) RETURNS INTERVAL AS
$$
  SELECT AGE((start + (months || ' months')::INTERVAL)::DATE, NOW()::DATE) AS remaining_time;
  CASE WHEN remaining_time > INTERVAL '0 day' THEN remaining_time
       ELSE INTERVAL '0 day'
$$
LANGUAGE sql IMMUTABLE;

Answer 1

As indicated you cannot use named variable within a SQL function. However, it is not necessary. You have 2 options: define the "variable" within SQL through a cte or sub-query or just use the calculation directly. So (See demo)

create or replace function remaining_period(start date, months int)
   returns interval 
  language sql 
  immutable strict
as $$
  with remaining(tm) as 
       (select age((start + (months || ' months')::interval)::date, now()::date) as remaining_time)
  select case when tm > interval '0 day' 
              then tm
              else interval '0 day'
         end
    from remaining;
$$;

create or replace function remaining_period2(start date, months int)
   returns interval 
  language sql 
  immutable strict
as $$
    select greatest( age((start + (months || ' months')::interval)::date, now()::date)
                   , interval '0 day'
                   ); 
$$;