Resolve the Error “no function matches the given name and argument types. you might need to add explicit type casts”

PostgreSQL is an open-source and powerful object-relational database management system. It is used to store data or data warehouse for mobile, web, analytics, and geospatial applications. It is a free, enterprise-class, and advanced system that supports non-relational (JSON), as well as relational (SQL), queries. It has many features and also supports some features as PostgreSQL is an advanced version that is frequently used by programmers to store and manipulate data. It is also known as Postgres, which is used extensively. DBeaver is a database administration tool that can manage all types of NoSQL and relational databases. When working with PostgreSQL, you may land up with the error “no function matches the given name and argument types. you might need to add explicit type casts”.

You don’t need to worry when you encounter this error as we are here to assist you. It is our motto to help everyone who comes to us for solving the error. We leave no stone unturned when it comes to providing you with effective solutions. Let’s discuss how the error message appears

How the error occurs

When you use DBeaver to create a store procedure in PostgreSQL while trying to add data into a table using DBeaver to call the process, you end up with an error warning. Here is what you get

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

It happens when you use the following script:

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

The procedure you call:

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

How To Handle the Error “no function matches the given name and argument types. you might need to add explicit type casts”

The error shows up when PostgreSQL is not supported the data type date of timestamp by implicit translation. The data type in oracle and Postgres is not similar, which is the reason you get the error warning.  We have an amazing solution to handle the error.

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)

The date conversion losing by timestamp (now()function as result type) conversion, which is default not permitted. You can either enforce it using explicit casting or pseudo constant current-date that results in the date type. Conversion is not required.

Conclusion

We shed light on the simplest possible solution to eliminate the error warning “no function matches the given name and argument types. you might need to add explicit type casts”.

I hope it helps! I wish you luck!