[SOLVED] PgSql Insert multiple records with multiple regexp_split_to

PgSql Insert multiple records with multiple regexp_split_to_table

I have a table with columns [FileId, FileName, FileUrl].

I get FileName and FileUrl as comma-separated string (v_FileName, v_FileUrl) in input params of my INSERT stored procedure, and FileId is auto increment.

Records are inserted successfully for FileName if I use regexp_split_to_table a single time as follows:

INSERT INTO MYTABLE
    SELECT 
        nextval('mytable_fileid_seq'),
        regexp_split_to_table,
        'testurl'
    FROM 
        regexp_split_to_table(v_FileName, E',')

This successfully inserts rows for all filenames in the v_FileName param and set 'testurl' for fileurl as in above test script.

My query is, is it possible to set FileUrl as well using regexp_split_to_table for v_FileUrl param also in such single go ?

I tried but I get an error

regexp_split_to_table is used more than once

(Maybe I am not using it properly)

Or suggest me any other way to insert records with such input params.

Thanks in advance.

Solution

One option is to use unnest with regexp_split_to_array:

SELECT * FROM unnest(
    regexp_split_to_array('a,b,c',','),
    regexp_split_to_array('1,2,3',',')
);

Result:

 unnest | unnest 
--------+--------
 a      | 1
 b      | 2
 c      | 3

So in your case:

INSERT INTO mytable (FileName, FileUrl)
SELECT *
FROM unnest(
    regexp_split_to_array(v_FileName,','),
    regexp_split_to_array(v_FileUrl,',')
);

(I'm assuming your FileId has a proper default value using the sequence).