Re: plpgsql and arrays

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
--
   Richard Huxton
   Archonet Ltd