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:
Show quoted text
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