Hello, I am building an admin interface for an app, and I am currently working on a mass import page, where the user is supposed to browse a csv file and it will send the data to the wanted tables in the database while ensuring the relations between data. So I had this code that I execute in the database separately :
CREATE OR REPLACE PROCEDURE insert_data_from_csv(csv_text TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
row_data RECORD;
item_id INT;
characteristic_id INT;
characteristic_values_id INT;
BEGIN
FOR row_data IN SELECT * FROM unnest(string_to_array(csv_text, E'\n')) AS rows LOOP
item_id := 850 + ((row_data.row_number - 1) / 34)::INT;
characteristic_id := 11000 + (((row_data.row_number - 1) % 34) * 2)::INT;
characteristic_values_id := 16000 + row_data.row_number::INT;
INSERT INTO items (item_id, type_id)
VALUES (item_id, 1);
INSERT INTO characteristics (characteristic_id, item_id, key_id)
VALUES (characteristic_id, item_id, row_data.key_id::INT);
INSERT INTO characteristic_values (characteristic_values_id, characteristic_id, value, language_id)
VALUES (characteristic_values_id, characteristic_id, row_data.value, row_data.language_id::INT);
END LOOP;
END;
$$;
DROP FUNCTION IF EXISTS csv_to_table_v2(text);
CREATE OR REPLACE FUNCTION csv_to_table_v2(csv_text TEXT)
RETURNS TABLE (
name VARCHAR,
value VARCHAR,
language_id INT,
item_id INT,
row_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'CSV Text: %', csv_text;
RETURN QUERY
SELECT
trim(split_part(csv_text, ',', 1)) AS name,
trim(split_part(csv_text, ',', 2)) AS value, g
cast(trim(split_part(csv_text, ',', 3)) AS INT) AS language_id,
cast(trim(split_part(csv_text, ',', 4)) AS INT) AS item_id,
row_number() OVER () AS row_id
FROM unnest(string_to_array(csv_text, E'\n')) AS rows;
END;
$$;
and the one to call the function for the csv that is parsed in a file button :
CALL
import_csv_data_v2 ('{{fileButton1.parsedValue[0]}}');
but when I execute it, it says successfully but doesn’t import anything into the database, why ? The connexion with db is established and working.
