I have a pseudo stored procedure as given below:
x
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.
Advertisement
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;