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;

You can't have variables in plain SQL functions - they are just SQL. You probably want to read the manual section covering pl/pgsql postgresql.org/docs/current/plpgsql.html — Richard Huxton
– Richard Huxton, Commented Feb 1, 2024 at 13:02

Belayer · Accepted Answer · 2024-02-08 01:41:28Z

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'
                   ); 
$$;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related