plpgsql and arrays
От | Artis Caune |
---|---|
Тема | plpgsql and arrays |
Дата | |
Msg-id | 45A74EEE.7060108@latnet.lv обсуждение исходный текст |
Ответы |
Re: plpgsql and arrays
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: