How to insert data into a String array in postgres stored procedure

I have a pseudo stored procedure as given below:

CREATE OR REPLACE FUNCTION get_data()
 RETURNS void AS $$
DECLARE
    rec_new   RECORD;
    querystring TEXT[];

    cursor_file CURSOR FOR
        select * from tableA;
BEGIN
    --open the file cursor
    OPEN cursor_file;
        LOOP
        FETCH cursor_file into rec_new;
        EXIT WHEN NOT FOUND;    

            querystring='{insert into tableB(fileid) values %}',rec_new.fileid;

        END LOOP;
    CLOSE cursor_file;
END; $$
LANGUAGE plpgsql;

I want to create multiple insert queries with dynamic fileId’s being iterated over a loop and put them in a string array ('querstring') seperated by a comma delimiter. The code above is not giving me the exact result. What is the correct way to achieve this?

The expected output is like:

{insert into tableB(fileid) values ('fileA'),
 insert into tableB(fileid) values ('fileB'),
 insert into tableB(fileid) values ('fileC')}

Then this querystring array needs to be executed also.

Answer

You are probably looking for the “array append” operator ||.

But that is an overly complicated way to solve the problem. You can do it simpler and more efficiently with something like

SELECT array_agg(
          format(
             'insert into tableB(fileid) values (%L)',
             fileid
          )
       ) INTO querystring
FROM tablea;

Advertisement

Answer