Обсуждение: 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