Home/Questions/How to fix the error: no function matches the given name and argument types. you might need to add explicit type casts.?
Next
Answered
Kennedy Koss
  • 3
Asked: 2022-07-15T11:45:42+00:00 In:

How to fix the error: no function matches the given name and argument types. you might need to add explicit type casts.?

  • 3

I used DBeaver to develop a stored procedure in PostgreSQL. And I’m attempting to invoke the method from DBeaver in order to enter data into the table.
However, I’m getting an error: no function matches the given name and argument types. you might need to add explicit type casts.

Procedure:

CREATE OR REPLACE FUNCTION public.proc_insert_test(p_brndcode integer,
p_brndname varchar(100),
p_brndsname varchar(100),
p_prdtype char(1),
p_discontinue char(1),
p_crddate date,
p_status integer,
p_recstat integer,
p_brndgrpseqno integer,
p_wefrom date)
RETURNS char
LANGUAGE plpgsql
AS $body$
BEGIN
Insert into arc_mmstbrndgroup(brndcode, brndname, brndsname, prdtype, discontinue, crddate, status, recstat, brndgrpseqno, wefrom)
values(p_brndcode, p_brndname, p_brndsname, p_prdtype, p_discontinue, p_crddate, p_status, p_recstat, p_brndgrpseqno, p_wefrom);
END;
$body$
;

Calling the procedure:

select public.proc_insert_test(123, 'Test2', 'Test2', 'T', 'T', now(), 1, 9, 1234, now());

How can I fix this problem? Thanks

Share

1 Answer

  1. Best Answer
    Expert

    The cause: Implicit translation from timestamp to date data type is not supported by Postgres. The date type in Postgres differs from the date type in Oracle. That is why the error “no function matches the given name and argument types. you might need to add explicit type casts.” occurs.

    Solution:

    CREATE OR REPLACE FUNCTION public.test(v date)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE '%', v;
END;
$function$

postgres=# SELECT test(now());
ERROR: function test(timestamp with time zone) does not exist
LINE 1: SELECT test(now());
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
postgres=# SELECT test(current_date);
NOTICE: 2019-11-14
+------+
| test |
+------+
| |
+------+
(1 row)

postgres=# SELECT test(now()::date);
NOTICE: 2019-11-14
+------+
| test |
+------+
| |
+------+
(1 row)

    date conversions from timestamps (the result type of the now() function) are failing. It is by default disallowed. As a result, you could either enforce it (by explicitly casting) or utilise the pseudo constant current_date, which gives back date type and eliminates the need for conversion.

    • 3

Leave an answer

You must login to add an answer.

Need An Account,