postgresqlmultiple-columnssql-insertbulkinsert

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).