Create function using quote_literal issues

[Mohamed DIA]

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

Re: Create function using quote_literal issues

[Mohamed DIA]

I found the solution by defining r as record and using
 FOR r in EXECUTE v_select

Thanks

On Thu, May 23, 2019 at 9:49 AM Mohamed DIA  wrote:

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