How do i get my PostgresSQL to perform the Call Succesfully?

Question

I've been wracking my brain around this for the past week, this is an insert stored procedure im going to use in an app to input inventory information. For some reason i keep getting the same error, even though i believe i'm following the documentation process fro doing this. What am i doing wrong?

My thought process for this procedure was, that i'll define the variables that the app will input, then asign the value of those variables into the columns in the table. This is my procedure:

CREATE OR REPLACE PROCEDURE public.add_inventory(
        _inventory_name character varying,
        _brand character varying,
        _category character varying,
        _color character varying,
        _inventory_size character varying,
        _image_link character varying,
        _sku character varying,
        _date_added date,
        _list_price money,
        _row_status integer DEFAULT 1)
    LANGUAGE 'plpgsql'
    AS $BODY$
    begin
    Insert into dbo.inventory_tb
    (inventory_name, brand, category, color, inventory_size, image_link, sku, date_added, list_price, row_status)
    values 
    (_inventory_name, _brand, _category, _color, _inventory_size, _image_link, _sku, _date_added, _list_price, _row_status);
    end;
    $BODY$;
    
    GRANT EXECUTE ON PROCEDURE public.add_inventory(character varying, character varying, character varying, character varying, character varying, character varying, character varying, date, money, integer) TO PUBLIC;

And the "Call" im trying unsuccesfully to submit through the query editor is as follows:

CALL public.add_inventory(
    'Specs',
    'Gracia1',
    'Shirt1',
    'Red1',
    'Small',
    'rootfile',
    '00001',
    '2021-03-31',
    60.00,
    1
)

The error im getting is:

LINE 1: CALL public.add_inventory(
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6

The table im inserting this information is like this:

CREATE TABLE public.inventory_tb
(
    inventory_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    inventory_name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    brand character varying(100) COLLATE pg_catalog."default" NOT NULL,
    category character varying(100) COLLATE pg_catalog."default" NOT NULL,
    color character varying(100) COLLATE pg_catalog."default" NOT NULL,
    date_added date NOT NULL,
    inventory_size character varying(50) COLLATE pg_catalog."default" NOT NULL,
    list_price money NOT NULL,
    image_link character varying(100) COLLATE pg_catalog."default" NOT NULL,
    sku character varying(100) COLLATE pg_catalog."default" NOT NULL,
    row_status integer NOT NULL,
    CONSTRAINT "inventory_tb_PK" PRIMARY KEY (inventory_id)
)

TABLESPACE pg_default;

ALTER TABLE public.inventory_tb
    OWNER to postgres;

Answer 1

Drop the old procedure and define it to use the preferred data types of the respective type category:

CREATE OR REPLACE PROCEDURE public.add_inventory(
    _inventory_name text,
    _brand text,
    _category text,
    _color text,
    _inventory_size text,
    _image_link text,
    _sku text,
    _date_added date,
    _list_price numeric,
    _row_status integer DEFAULT 1
) LANGUAGE plpgsql AS
$BODY$
BEGIN
    INSERT INTO dbo.inventory_tb
        (inventory_name, brand, category, color, inventory_size, image_link, sku, date_added, list_price, row_status)
    VALUES
        (_inventory_name, _brand, _category, _color, _inventory_size, _image_link, _sku, _date_added, _list_price, _row_status);
END;
$BODY$;

Then PostgreSQL will be able to implicitly convert the data types according to the type conversion rules, and you don't need explicit type casts.

It does not matter that the data types are slightly different from the ones in the table, as PostgreSQL will apply type casts on assignment.

The call works fine now:

CALL public.add_inventory(
    'Specs',
    'Gracia1',
    'Shirt1',
    'Red1',
    'Small',
    'rootfile',
    '00001',
    '2021-03-31',
    60.00,
    1
);
CALL