Re: arrays and pl/pgsql?

Brandon Craig Rhodes wrote:
> Richard Welty <rwelty@averillpark.net> writes:
>
>
>>given the lack of response, i'm going to presume that there is no published
>>material on arrays and pl/pgsql
>>
>>can someone please 1) clearly state on whether arrays work in pl/pgsql and
>>2) if they do, please explain the syntax?
>
>
> In PL/pgSQL you can declare arrays, set the value of arrays, and
> reference array members; I have not discovered any way of setting
> individual array members without having to re-set the entire array.
>

Does ;

array[2] := 5;

work?

In psql ;

update array_table set array[2] = '5' ;

does work.

> An example procedure follows:
>
> CREATE FUNCTION try_array() RETURNS INTEGER AS '
> DECLARE
>   array INTEGER[];
>   number INTEGER;
> BEGIN
>   array := ''{3,4,6}'';
>   number := array[1];
>   RAISE NOTICE ''First element is %'', number;
>   number := array[2];
>   RAISE NOTICE ''Second element is %'', number;
>   number := array[3];
>   RAISE NOTICE ''Third element is %'', number;
>   array := ''{3,4,12}'';
>   number := array[3];
>   RAISE NOTICE ''Third element is now %'', number;
>   RETURN NULL;
> END;
> ' LANGUAGE 'plpgsql';
>

You could determine the number of elements in the array then use a loop to
itterate your array to display the values as well.


Here is a collection of stuff that does something similar.

CREATE TYPE mail_aliases_list_type AS (a_mailbox text,
         a_destination_el text,
         a_dest_el_id integer,
         a_dest_total integer);

CREATE FUNCTION mail_aliases_list () RETURNS SETOF mail_aliases_list_type
     AS '
DECLARE
   rec record;
   retrec record;
   low int;
   high int;
BEGIN
  FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
   SELECT INTO low
    replace(split_part(array_dims(rec.a_destination),'':'',1),''['','''')::int;

     IF low IS NULL THEN
       low := 1;
       high := 1;
     ELSE
       SELECT INTO high

replace(split_part(array_dims(rec.a_destination),'':'',2),'']'','''')::int;
       IF high IS NULL THEN
         high := 1;
       END IF;
     END IF;

   FOR i IN low..high LOOP
    SELECT INTO retrec rec.a_mailbox,
        rec.a_destination[i],
        i::int,
        high ;
    RETURN NEXT retrec;
   END LOOP;
  END LOOP;
  RETURN;
END;
'
     LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE VIEW mail_alias_list AS
     SELECT mail_aliases_list.a_mailbox,
         mail_aliases_list.a_destination_el,
         mail_aliases_list.a_dest_el_id,
         mail_aliases_list.a_dest_total
     FROM mail_aliases_list();