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