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