Create function using quote_literal issues

Started by Mohamed DIAalmost 7 years ago2 messageshackers
Jump to latest
#1Mohamed DIA
macdia2002@gmail.com

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

#2Mohamed DIA
macdia2002@gmail.com
In reply to: Mohamed DIA (#1)
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@gmail.com> wrote:

Show quoted text

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