PostgreSQL - How call a procedure with a varchar parameter

user_11996953 0
Viewed 6829

I trying to do a SP using postgresql 12

CREATE OR REPLACE PROCEDURE trans_buy(
        _name_client varchar(25),
        _id_product smallint,
        _mount smallint
    )
    LANGUAGE plpgsql
    AS
    $$

    BEGIN 
        INSERT INTO invoices (cliente) VALUES(_name_client);
        INSERT INTO invoices_details (id_invoice, id_product, mount) VALUES (1, _id_product, _mount, 100);
    END
    $$

but when i tried to call this sp using: CALL trans_buy('james', 3, 10) i got this message error:

doesnt exists the stored procedure << trans_buy(unknown, integer, integer) >>

HINT: No procedure matches the name and types of arguments. It may be necessary to add explicit type conversion.

sql-insert subquery postgresql stored-procedures sql
1 Answers

You probably need to explictly cast the integer values to smallint:

call trans_buy('james', 3::smallint, 10::smallint);

I assume that the target columns in invoice_details are smallint as well. An alternative is to have the procedure accept ints, and cast at insert time:

CREATE OR REPLACE PROCEDURE trans_buy(
    _name_client varchar(25),
    _id_product int,
    _mount int
) LANGUAGE plpgsql
AS $$
BEGIN 
    INSERT INTO invoices (cliente) VALUES(_name_client);
    INSERT INTO invoices_details (id_invoice, id_product, mount) VALUES (1, _id_product::smallint, _mount::smallint);
END
$$

Note that your second insert had 4 values for 3 columns. I (attempted to) adjust that.

One should also highlight that you don't really need a subquery do this in Postgres. You can run multiple DML operations in a single query, using common-tabe-expressions:

with 
    -- CTE: query parameters
    params(cliente, id_product, mount) as (values ('james', 3, 10)),
    
    -- CTE: insert to invoices
    inv as (insert into invoices (cliente) select cliente from params)

-- insert to invoice details
insert into invoice_details (id_invoice, id_product, mount) 
select 1, id_product, mount from params
user_106767160
Related