Re: arrays and pl/pgsql?

Brandon Craig Rhodes wrote:
> Richard Welty <rwelty(at)averillpark(dot)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();