CODEWITHSUNDEEP
postgresql
Septiana Fajrin
Septiana Fajrin

Reputation: 115

ERROR: type modifier is not allowed for type "date" (PostgreSQL)

I got an error while creating this function. can you solve it?

CREATE OR REPLACE FUNCTION my_schema.test($1"date") 
   RETURNS date(-1)
   LANGUAGE SQL
   IMMUTABLE
AS $$   
       SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$
;

ERROR: syntax error at or near "$1"

if I remove the $1 from parameter input the error is:

type modifier is not allowed for type "date".

Upvotes: 0

Views: 3394

Answers (1)

user330315
user330315

Reputation:

date(-1) is invalid. That needs to be just date.

The $1 is also not allowed as a parameter name in the function definition.

You either don't provide a name at all (e.g. test(date)- not recommended) or you provide a name that is a legal SQL identifier (recommended).

There is also no need to use $1 when referring to the parameter. 

CREATE OR REPLACE FUNCTION my_schema.test(p_input date) 
   RETURNS date
   LANGUAGE SQL
   IMMUTABLE
AS $$   
  SELECT (date_trunc('MONTH', p_input) + INTERVAL '1 MONTH - 1 day')::date;
$$
;

Upvotes: 1

Related Questions