Re: Create function using quote_literal issues

Hi,

I am trying to use a create function in order to update some values in a table (see below code).

However, when I run the function, it never enters into the following loop

FOR r IN SELECT * FROM immatriculationemployeursucctemp2 where  succursale = quote_literal(s.succursale) order by row_number

However, if I remove the condition where  succursale = quote_literal(s.succursale)   then it works

I need to filter on every value of succursale

Is there a way to achieve it without removing ?

Any help will be appreciated. I'm struggling with it for a while now

CREATE OR REPLACE FUNCTION create_new_emp_succ_numbers() RETURNS SETOF list_succursale AS
$BODY$
DECLARE
    r immatriculationemployeursucctemp2%rowtype;
    s list_succursale%rowtype;
       seq_priv INTEGER := 1;
      
BEGIN
  
    FOR s IN SELECT * FROM list_succursale where  succursale in('010100062D1','010102492S1')
  
    LOOP
             
              
    FOR r IN SELECT * FROM immatriculationemployeursucctemp2 where  succursale = quote_literal(s.succursale) order by row_number
   
   
    LOOP
   
        update immatriculationemployeursucctemp set no_employeur= '10' || lpad(seq_priv::text,6,'0') || '0' || r.row_number-1 where employer_type=10 and id=r.id;
           
         
   
    END LOOP;
    seq_priv := seq_priv + 1;
     RETURN NEXT s;
END LOOP;
   
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM create_new_emp_succ_numbers();