Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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;

pgsql-novice by date

Next:From: Mark KellyDate: 2007-04-04 21:31:23
Subject: Design advice needed.
Previous:From: Tom LaneDate: 2007-04-04 00:18:00
Subject: Re: What am I doing wrong with this comma-delimited copy?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group