datediff function in postgressql

Question

i am new in PostgreSQL i try to create function

this is code

    CREATE OR REPLACE FUNCTION diff(date Timestamp) RETURNS void
    As
    $$
    declare
    CURRENT_DATE Timestamp;
    number numeric;
    
    begin
            CURRENT_DATE=(SELECT CURRENT_DATE) ;
            
            number=(SELECT DATE_PART('day', 'date'::Timestamp - 'CURRENT_DATE'::Timestamp));
            
    end
   $$ LANGUAGE plpgsql;

select  diff('2020-08-18');

when i select diff it error = invalid input syntax for type timestamp: "date"

What is that function supposed to do? Especially because it doesn't actually return anything (`returns void`). Why not simply do `return "date" - current_date`? — , Aug 19 '20 at 07:48
it first time i create function i try to create this function i want to pass date and function calculate datediff i have to change returns "date"-current_date ? — non srithong, Aug 19 '20 at 08:00
You don't need to write a function for that, you can simply subtract one date from the other, e.g. `current_date - date '2020-08-18'` — , Aug 19 '20 at 08:15

score 3 · Accepted Answer · 2020-08-19T08:32:23.980

If you want to write a wrapper around the - operator for dates that always subtracts the passed date from the current date, you will need to change your function to return a result, e.g. returns integer.

A PL/pgSQL function also needs a return statement to actually return a value.

current_date is a built-in function, you can not use it as a variable name - and the assignment of current_date to itself makes not sense to begin with. Just use current_date wherever you need it without (re)declaring it as a variable or assigning a value to it.

As documented in the manual subtracting a date value from another date value yields the difference in days. There is no need to use date_part() to get that (and you can't use date_part() on an integer to begin with).

You also don't need PL/pgSQL for wrapping a simple expression like that into a function:

Without knowing more details, it seems you are looking for this:

create function diff(p_input date)
  returns integer
as
$$
  select p_input - current_date;
$$
language sql;

The equivalent implementation in PL/pgSQL using an (not needed) intermediate variable to store the result would look like this:

create function diff(p_input date)
  returns integer
as
$$
declare
  l_result integer;
begin
  l_result := p_input - current_date;
  return l_result;
end;
$$
language plpgsql;

Then you can use it like this:

select diff(date '2020-08-18');

datediff function in postgressql

1 Answers1