Thread: arrays and pl/pgsql?

i'm trying to find some useful documentation on use of SQL arrays in
pl/pgsql procedures, and not coming up with. i've found a note (tossed off
rather casually) in _PostgreSQL: Essential Reference_ on page 252, stating
that it's possible and giving what appears to be incorrect syntax for an
array declartion. other than that, i've not found much.

can anyone point me at anything that covers this?

thanks,
  richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
              Unix, Linux, IP Network Engineering, Security

On Mon, 10 Feb 2003 12:56:16 -0500 (EST) Richard Welty <rwelty@averillpark.net> wrote:

> i'm trying to find some useful documentation on use of SQL arrays in
> pl/pgsql procedures, and not coming up with. i've found a note (tossed
> off
> rather casually) in _PostgreSQL: Essential Reference_ on page 252,
> stating
> that it's possible and giving what appears to be incorrect syntax for an
> array declartion. other than that, i've not found much.

> can anyone point me at anything that covers this?

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?

thanks,
  richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
              Unix, Linux, IP Network Engineering, Security

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.

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';

--
Brandon Craig Rhodes                         http://www.rhodesmill.org/brandon
Georgia Tech                                            brandon@oit.gatech.edu

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();