Hi All,
I'm trying to add a batch of records to a table in one go rather than make a call from the application for each record. To do this i'd like to pass in an array as a text eg. the param looks like:
{ {'bob','myns'} , {'sally','anotherns'} }
Calling the below function doesn't add any row to the empty "user" table and I'm not getting any errors. Can anyone see what i'm doing wrong?
TIA
Pete
CREATE OR REPLACE FUNCTION user_batch_update(text)
DECLARE
properties text[][] := $1;
iloop integer = 1;
myid integer;
BEGIN
WHILE properties[iloop] IS NOT NULL LOOP
SELECT id INTO myid FROM user WHERE role = properties[iloop][1] AND ns = properties[iloop][2];
IF NOT FOUND THEN
INSERT INTO user (role,ns) VALUES (properties[iloop][1],propertie
s[iloop][2]);
END IF;
iloop := iloop + 1;
END LOOP;
RETURN 0;
END;