Create function using quote_literal issues

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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();
Reply | Threaded
Open this post in threaded view
|

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 <[hidden email]> 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();