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(at)domain, user2(at)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