plpgsql and arrays

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

Предыдущее
От: Shane Ambler
Дата:
Сообщение: Re: Select Query
Следующее
От: "Zeugswetter Andreas ADI SD"
Дата:
Сообщение: Re: [HACKERS] Checkpoint request failed on version 8.2.1.