plpgsql and arrays

Поиск
Список
Период
Сортировка
Искать

plpgsql and arrays

От:
Artis Caune <Artis.Caune@latnet.lv>
Дата:
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.

Re: plpgsql and arrays

От:
Richard Huxton <dev@archonet.com>
Дата:
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

Re: plpgsql and arrays

От:
Artis Caune <Artis.Caune@latnet.lv>
Дата:
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

Re: plpgsql and arrays

От:
Richard Huxton <dev@archonet.com>
Дата:
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
FAQ