Re: plpgsql and arrays

Thanks a lot!
Normal upper/lower loop works fine:


CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT AS $$
DECLARE
   v_uid SMALLINT;
   v_low SMALLINT;
   v_upp SMALLINT;
   empty BOOLEAN := TRUE;
BEGIN
   SELECT INTO v_low array_lower($1,1);
   SELECT INTO v_upp array_upper($1,1);
   FOR i IN v_low .. v_upp LOOP
     IF ( empty = TRUE ) THEN
       INSERT INTO users2 VALUES (DEFAULT, $1[i]);
       empty = FALSE;
       SELECT INTO v_uid currval('users2_uid_seq');
     ELSE
       INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
$1[i]);
     END IF;
   END LOOP;

   RETURN v_uid;

END;
$$ LANGUAGE 'plpgsql';





Richard Huxton wrote:
> Artis Caune wrote:
>> I'm trying to write function which accept array and insert each
>> element in table:
>>
>>    =# SELECT * FROM add_user('{user1@domain, user2@domain}');
>>    ERROR:  column "email" is of type character varying but expression is
>>            of type record
>>    HINT:  You will need to rewrite or cast the expression.
>
>> function is like this:
>
>>   FOR v_rec IN SELECT $1[i] FROM generate_series(array_lower($1,1),
>> array_upper($1,1)) s(i)
> ...
>>       INSERT INTO users2 VALUES ((SELECT currval('users2_uid_seq')),
>> v_rec);
>
> Well, v_rec is a RECORD, just like the error says. You want the first
> attribute. Try this:
>
> FOR v_rec IN SELECT $1[i] AS username FROM ...
> INSERT INTO users2 VALUES (..., v_rec.username)
>
> However, I'd also just have a normal loop counting over the array
> upper/lower bounds. Then you could just use:
> INSERT INTO users2 VALUES (..., $1[i])
>
> HTH