1

PostgreSQL 14

Here is a function :

CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE 
 tab integer[];
 periodes text;
 i integer;
 BEGIN
  tab := Array[[0,100, 1], [100,200,20], [200,300,3]];
  i := 1;
  WHILE datedebut < tab[i][2]::integer
    LOOP
        periodes := concat(periodes, tab[i][3]);
        i := i+1;
    END LOOP;
  return periodes;
END;
$$;

The aim is to concatenate The third element of each tab element in the periodes variable.
exemple :

  • select test(50) gives periodes = 1 20 3
  • select test(150) should give periodes = 20 3
    but it returns '' because of the i in the while condition which is not updated obviously despite of i+1.

I don't know what to do, or where, to update the while condition so it would test each element of the tab array in order until the condition is false.

(next step will be a second arg in the function to create an interval and test(50, 150) would return periodes = 1 20)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.