Search code examples
postgresqlfunctionparameters

postgresql function parameter interval

I want to create a function with a period (month, week, day...) as a parameter called 'myperiod'

Creating the function with this sql (not complete) is permitted (no syntax error) :

where *some_sql* = date_trunc(myperiod, "Table1"."Date") = date_trunc(myperiod, current_date - (interval '1' + myperiod))

But when invoking the function, I've the following error :

ERROR:  operator does not exist: interval + character varying
LINE 10: ...date_trunc(myperiod, current_date - (interval '1' + myperiod...
                                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Solution

  • You presumably meant to write

    date_trunc(myperiod, current_date - ('1 ' || myperiod)::interval)