No procedure matches the given name and argument types. You might need to add explicit type casts. POSTGRESQL

Question

I apologize in advance for my English. I'm new to programming. There is a problem with stored procedures in PostgreSQL. I have a table:

CREATE TABLE user
(
   Id serial,
   idGroup INTEGER,
   firstname VARCHAR(45),
   lastname VARCHAR(45)
)

I want to create a procedure that adds a new user. The procedure itself has been created, but I can't add a new user to it.

CREATE PROCEDURE usernew
(
    c_Id INOUT INTEGER,
    c_idGroup INTEGER,
    c_firstname VARCHAR(45),
    c_lastname VARCHAR(45)
)

LANGUAGE 'plpgsql' AS
$$

BEGIN 
     INSERT INTO public.user (idGroup, firstname, lastname) values
     (c_idGroup,
          c_firstname,
          c_lastname
     ) RETURNING Id INTO c_Id;
END
$$;

call usernew(1, 'poppy', 'jacobs')

The following error occurs:

ERROR:  procedure usernew(integer, unknown, unknown) does not exist
LINE 1: call usernew(1, 'poppy', 'jacobs')
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts. 

SQL state: 42883
Character: 6

I tried to solve the problem by using CAST (.. AS ...), but it did not help.

Help me please. Thank you in advance for your cooperation!

Answer 1

You only need a simple SQL function rather than a procedure. Here it is.

CREATE or replace function usernew(c_idGroup INTEGER, c_firstname text, c_lastname text)
returns integer LANGUAGE sql AS
$$
  INSERT INTO public.user (idGroup, firstname, lastname)
  VALUES (c_idGroup, c_firstname, c_lastname)
  RETURNING id;
$$;

Use it like this

SELECT usernew (1, 'poppy', 'jacobs');

See the demo