looping multi-dimensional array

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;

Browse pgsql-novice by date

  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?