wish json recordset insert api for postgrsql

[greatlord]

I was force find a way to upload big datarecords to postgrsql Frist I use Torgar buil insert for it. It was faster way doing it.

After a while I exaime if it does not exists anything better yes it does. PostgrSql accpect jsondata as in parms and you can popluate it to a recordsset now I manger incresss the PostgrSql speed alot more.

I am doing like this now productsProcessed = dataSource.TableFunction("public.importproducts", new { productsjson = productJson, categoriesjson = categoriesJson }).ToCollection().Execute();

now I got advacne sql function CREATE OR REPLACE FUNCTION public.importproducts(productsjson json, categoriesjson json) RETURNS TABLE(idproduct bigint, artno character varying, condition boolean, instock boolean, weight double precision, bookisbn character varying, sku character varying, eanorupc character varying, productname character varying, description character varying, stockqty bigint, imageurl character varying, producturl character varying, storeurl character varying, arycategory character varying[], price real, priceoriginal real, shippingcosts real, isnew boolean) LANGUAGE plpgsql AS $function$ BEGIN --INSERT NEW PRODUCT CATEGORIES INSERT INTO category(idlanguages, categoryname) SELECT DISTINCT cjson.idlanguages, cjson.categoryname FROM json_populate_recordset(null::productcategory_csv_row, categoriesjson) AS cjson --LEFT JOIN category C1 ON C1.idlanguages = C.idlanguages AND C1.categoryname = C.parentname WHERE NOT EXISTS(SELECT 1 FROM category cexisting WHERE cexisting.idlanguages = cjson.idlanguages AND cexisting.categoryname = cjson.categoryname);

--IUPDATE PRODUCT CATEGORIES PARENT
UPDATE category cupdate
    SET parentid = cparent.idcategory
FROM json_populate_recordset(null::productcategory_csv_row, categoriesjson) AS cjson
    LEFT JOIN category cparent ON cparent.idlanguages = cjson.idlanguages AND cparent.categoryname = cjson.parentname
WHERE cupdate.idlanguages = cjson.idlanguages AND cupdate.categoryname = cjson.categoryname;

--INSERT NEW PRODUCTS, PRODUCT DESC, PRODUCT PRICES 
RETURN QUERY
WITH 
pinsert AS
(
    INSERT INTO products(idstore, storeproductid, sku, stockqty, eanorupc, bookisbn, producturl, imageurl, artno, condition, inStock, weight)
    SELECT P.idstore, P.storeproductid, P.sku, P.stockqty, P.eanorupc, P.bookisbn, P.producturl, P.imageurl, P.artno, P.condition, P.inStock, P.weight
    FROM json_populate_recordset(null::product_csv_row, productsjson) AS P
    WHERE NOT EXISTS(SELECT 1 FROM products WHERE products.idstore = P.idstore AND products.storeproductid = P.storeproductid)
    RETURNING products.idproduct, products.idstore, products.storeproductid
),
pcategoryinsert AS
(
    INSERT INTO products_to_category(idproduct, idcategoryroot)
    SELECT pinsert.idproduct, C1.idcategory
    FROM pinsert 
        INNER JOIN json_populate_recordset(null::productcategory_csv_row, categoriesjson) C ON C.idstore = pinsert.idstore AND C.storeproductid = pinsert.storeproductid

        INNER JOIN category C1 ON  C1.idlanguages = C.idlanguages AND C1.categoryname = C.categoryname
    --WHERE C.storeproductid IS NOT NULL
),
pdescinsert AS 
(
    INSERT INTO products_desc(idproduct, idlanguages, productname, description) 
    SELECT pinsert.idproduct, P.idlanguages, P.productname, P.description
    FROM json_populate_recordset(null::product_csv_row, productsjson) P 
        INNER JOIN pinsert ON P.idstore = pinsert.idstore AND P.storeproductid = pinsert.storeproductid
),
ppricesinsert AS
(
    INSERT INTO products_prices(idproduct, idcurrency, price, priceoriginal, shippingcosts)
    SELECT pinsert.idproduct, P.idcurrency, P.price, P.priceoriginal, P.shippingcosts
    FROM json_populate_recordset(null::product_csv_row, productsjson) P 
        INNER JOIN pinsert ON P.idstore = pinsert.idstore AND P.storeproductid = pinsert.storeproductid
    RETURNING products_prices.idproduct, products_prices.price, products_prices.priceoriginal, products_prices.shippingcosts
),
pexisting AS(
    UPDATE products SET condition = P.condition, inStock = P.inStock
    FROM json_populate_recordset(null::product_csv_row, productsjson) AS P 
    WHERE products.idstore = P.idstore AND products.storeproductid = P.storeproductid
    RETURNING products.idproduct, P.condition, P.inStock
),
ppricesexistingproduct AS
(
    INSERT INTO products_prices(idproduct, idcurrency, price, priceoriginal, shippingcosts)
    SELECT products.idproduct, P.idcurrency, P.price, P.priceoriginal, P.shippingcosts
    FROM json_populate_recordset(null::product_csv_row, productsjson) AS P 
        INNER JOIN products ON products.idstore = P.idstore AND products.storeproductid = P.storeproductid
    WHERE NOT EXISTS(SELECT 1 FROM pinsert WHERE pinsert.idstore = P.idstore AND pinsert.storeproductid = P.storeproductid)
    RETURNING products_prices.idproduct, products_prices.price, products_prices.priceoriginal, products_prices.shippingcosts
)
SELECT ppricesinsert.idproduct, P.artno, P.condition, P.inStock, P.weight, P.bookisbn, P.sku, P.eanorupc, P.productname, P.description,
    P.stockqty, P.imageurl, P.producturl, P.storeurl, P.arycategory, P.price, P.priceoriginal, P.shippingcosts, true 
    FROM ppricesinsert 
        INNER JOIN pinsert ON pinsert.idproduct = ppricesinsert.idproduct
        INNER JOIN json_populate_recordset(null::product_csv_row, productsjson) AS P 
            ON P.idstore = pinsert.idstore AND P.storeproductid = pinsert.storeproductid            
UNION 
SELECT ppricesexistingproduct.idproduct, null, pexisting.condition, pexisting.inStock, null, null, null, null, null, null,
    null, null, null, null, null, ppricesexistingproduct.price, ppricesexistingproduct.priceoriginal, ppricesexistingproduct.shippingcosts, false 
    FROM ppricesexistingproduct LEFT JOIN pexisting on ppricesexistingproduct.idproduct = pexisting.idproduct;  

END;$function$ ;

the custom recrods set struct is call productcategory_csv_row so we create it

create TYPE product_csv_row AS (idstore bigint, storeproductid character varying(255),
sku character varying, eanorupc character varying, bookisbn character varying, producturl character varying, imageurl character varying, artno character varying, idlanguages bigint, productname character varying, description character varying, idcurrency bigint, stockstatus bigint, pricewithtax real, shippingcosts real, categoryname character varying(128), categorydesc character varying, parentid bigint, image_url character varying, arycategory character varying(128) [], storeurl character varying);

create TYPE productcategory_csv_row AS (idstore bigint, storeproductid character varying(255),
idlanguages bigint, categoryname character varying(128), parentname character varying(128));

now I can upload whole recordset with json instead for doing bulik insert to one table at time :) I hope you can create a api to create custom row and doing json insert :)

[Grauenwolf]

This looks really interesting. Unfortunately I don't have time to work on it right away, but this is definitely the kind of database-specific functionality that Chain was designed to support.

[greatlord]

THanks you think it is interesting. I hope you got time to implement it some day. At moment I got it working with the code above :)