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".
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;
$$
;