Thread: Pl/PgSql: Loop over Array

Ist it possible to loop over an array:

CREATE FUNCTION atest(INT[]) RETURNS TEXT AS '
    DECLARE
        myA ALIAS FOR $1;
        myI INT;
        myRet TEXT='''';
    BEGIN
        FOR myI IN myA LOOP
            myRet=myRet||''x''||myI;
        END LOOP;
        RETURN myRet;
    END;
' LANGUAGE 'plpgsql';

This Examle is not working ;-(

Thanks,
AXEL.

Axel Straschil wrote:
> Ist it possible to loop over an array:
>

If you are using Postgres 7.3.x the following works. If you are using
7.2.x or earlier, you may be able to modify the "high" and "low"
calculation lines using the substr and strpos functions.

CREATE OR REPLACE FUNCTION atest(INT[]) RETURNS TEXT AS '
DECLARE
   mya alias for $1;
   myret text = '''';
   low int;
   high int;
BEGIN
   low := replace(split_part(array_dims(mya),'':'',1),''['','''')::int;
   high := replace(split_part(array_dims(mya),'':'',2),'']'','''')::int;

   FOR i IN low..high LOOP
     myret := myret || ''|'' || mya[i];
   END LOOP;

   RETURN myret;
END;
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;

regression=# select atest('{1,2,3,4}'::int[]);
   atest
----------
  |1|2|3|4
(1 row)


HTH,

Joe