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
Best Answer
Postgres doesn't allow implicit conversion from
timestamp
todate
data type. Attention - Postgresdate
type is different from Oracle'sdate
type.The conversion from
timestamp
(result type ofnow()
function) todate
is losing conversions. It is not allowed by default. So you should to enforce it (by explicit casting), or you should to use pseudo constantcurrent_date
that returnsdate
type, and there is not necessary any conversion.