Thread: plpgsql and arrays
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.
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
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@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
Artis Caune wrote: > Thanks a lot! > Normal upper/lower loop works fine: > > > CREATE OR REPLACE FUNCTION add_user( VARCHAR(128)[] ) RETURNS SMALLINT I'd rename the function "add_users()" > 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); You can also write this: v_low := array_lower($1,1); -- Richard Huxton Archonet Ltd