plpgsql: No function matches the given name and argument types. You might need to add explicit type casts

Question

I've created a stored procedure in PostgreSQL using DBeaver. & I'm trying to insert data into table by calling the procedure from DBeaver. But it's giving me an error

SQL Error [42883]: ERROR: function public.proc_insert_test(integer, unknown, unknown, unknown, unknown, timestamp with time zone, integer, integer, integer, timestamp with time zone) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8

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());

What can be the issue?

I'm totally new to this.

Update:

Procedure calling:

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

Error:

SQL Error [42883]: ERROR: function public.proc_insert_test(integer, character varying, character varying, character, character, timestamp with time zone, integer, integer, integer, timestamp with time zone) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8

This problem is common when you use "varchar" in function - If you use value 'Test2' etc in call, postgresql interprets it as TEXT type. The same with "char" type. So you would need to specifically cast these types in call of function - like 'Test2'::varchar(100) etc. — JosMac
can you check you have only one function with the expected signature? it is possible that due to function overload PostgreSQL is referring to another function. — Arjun Vachhani
@JosMac tried your suggestion, still I'm getting the same error. Check I've updated my question — Shreyas Pednekar
Your function wants a date. now() returns a timestamp. Try using current_date instead. — Nick Barnes

Pavel Stehule Pavel Stehule · Accepted Answer · 2019-11-14T09:38:28

Postgres doesn't allow implicit conversion from timestamp to date data type. Attention - Postgres date type is different from Oracle's date type.

 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)

The conversion from timestamp (result type of now() function) to date is losing conversions. It is not allowed by default. So you should to enforce it (by explicit casting), or you should to use pseudo constant current_date that returns date type, and there is not necessary any conversion.

plpgsql: No function matches the given name and argument types. You might need to add explicit type casts

1 Answers