| From: | Artis Caune <Artis(dot)Caune(at)latnet(dot)lv> |
|---|---|
| To: | pgsql-general(at)PostgreSQL(dot)org |
| Subject: | plpgsql and arrays |
| Date: | 2007-01-12 09:03:42 |
| Message-ID: | 45A74EEE.7060108@latnet.lv |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I'm trying to write function which accept array and insert each element
in table:
=# SELECT * FROM add_user('{user1(at)domain, user2(at)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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Zeugswetter Andreas ADI SD | 2007-01-12 09:49:53 | Re: [HACKERS] Checkpoint request failed on version 8.2.1. |
| Previous Message | Shane Ambler | 2007-01-12 07:05:22 | Re: Select Query |