From: | "Peter Schonefeld" <peter(dot)schonefeld(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | looping multi-dimensional array |
Date: | 2007-04-04 01:36:40 |
Message-ID: | 9cc0d1180704031836q3d6c0938v1abd5903cbdd13f6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kelly | 2007-04-04 21:31:23 | Design advice needed. |
Previous Message | Tom Lane | 2007-04-04 00:18:00 | Re: What am I doing wrong with this comma-delimited copy? |