Re: Create function using quote_literal issues

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 <macdia2002(at)gmail(dot)com> 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();
>