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