Search Postgresql Archives

Re: plpgsql and arrays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux