Create function using quote_literal issues

From: Mohamed DIA <macdia2002(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Create function using quote_literal issues
Date: 2019-05-23 09:49:52
Message-ID: CA+oNSn91iAaUbCOsmn5TbNJ3Kp94CfCfnqquW0XyPZNw27xTSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2019-05-23 10:02:52 Re: Excessive memory usage in multi-statement queries w/ partitioning
Previous Message Peter Eisentraut 2019-05-23 09:31:38 Re: "long" type is not appropriate for counting tuples

Browse pgsql-sql by date

  From Date Subject
Next Message Mohamed DIA 2019-05-23 10:09:52 Re: Create function using quote_literal issues
Previous Message Pavel Stehule 2019-05-21 07:13:46 Re: Table as argument in postgres function