'Error while call prostresql procedure nestjs/typeorm
I have this stored procedure to save a purchase with a token relate. But I'm facing an error when I try to call it in Nestjs/typeorm.
I checked and I'm passing the params except for the INOUT, because I just need the output.
Please, can someone explain what I'm doing wrong?
create or replace procedure grab_token_and_create_purchase(
stripeId VARCHAR,
clientId INTEGER,
purchaseData VARCHAR,
price REAL,
productId INTEGER,
INOUT client_purchase refcursor = 'client_purchase')
language plpgsql
as $$
declare
product_company_name public.company.name%TYPE;
product_token public.tag%ROWTYPE;
purchase_id INTEGER;
begin
SELECT name INTO product_company_name
TARGET FROM public.company
WHERE id = (SELECT company FROM public.ad WHERE id = productId );
IF NOT FOUND THEN
RAISE EXCEPTION 'Product''s company not found %', productId;
END IF;
SELECT * INTO product_token
FROM public.tokens
WHERE product_id = productId
AND taken = FALSE;
IF NOT FOUND THEN
RAISE EXCEPTION 'No more tokens avaliable %', productId;
END IF;
UPDATE public.tokens
SET taken = TRUE
WHERE id = product_token;
SELECT INTO purchase_id nextval('purchase_id_seq');
INSERT INTO public.purchase (
id
, "stripeId"
, "clientId"
, "purchaseData"
, price
, "productId"
, token
, used
, expired
, active
, refunded
, "companyName"
) VALUES (
purchase_id
, stripeId
, clientId
, purchaseData
, price
, productId
, product_token
, FALSE
, FALSE
, TRUE
, FALSE
, product_company_name
);
SELECT id
, "stripeId"
, "clientId"
, "purchaseData"
, price
, "productId"
, token
, used
, expired
, active
, refunded
, "companyName"
INTO client_purchase
FROM public.purchase
WHERE id = purchase_id;
end; $$
I call the procedure in a function using the @nestjs/typeorm. I'm using typescript.
async grabTokenAndCreatePurchase(purchase: PurchaseInterface): Promise<Purchase> {
try {
const {clientId, stripeId, purchaseData, productId, price} = purchase;
return await this.purchasesRepository.query(
`grab_token_and_create_purchase @stripeid='${stripeId}', @clientId= ${clientId}, @purchaseData='${purchaseData}', @price=${price}, @productId=${productId}`);
} catch (err) {
throw new Error (
`ERROR while saving the purchase - failed to call procedure. ${err}` );
};
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|