Search Postgresql Archives

plpgsql and arrays

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

 



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.



table is with values (uid, email):
   uid - smallint - not null default nextval('users2_uid_seq'::regclass)
   email - character varying(128) -



function is like this:

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

  RETURN v_uid;

END;
$$ LANGUAGE 'plpgsql';




I'm trying to cast it to varchar, but it doesn't help.



[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