Thread: Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
From
Rajni Bobal
Date:
Hello Sir,
Please clarify the use of %I will not be substituted by the string literal 'colname' rather its value will be substituted dynamically.
regards,
Rajni bobal
On Thu, Jan 23, 2025 at 8:33 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
On Wed, Jan 22, 2025 at 10:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:SELECT delete_from_table1('commandlog','commandlog','cmdid','cmdtime < extract(epoch FROM (current_date - 15))*1000',500);(putting aside many other issues for now)It would help to see the exact output you are getting. The query as written above cannot work with the functions you gave, which have the column name as the FOURTH parameter, and the where clause as the THIRD.EXECUTE 'DELETE from ' || quote_ident(tblname) ||
' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
' where ' || wherecon || ' limit $2 ) '
USING colname, batchsize;Use FORMAT instead:EXECUTE FORMAT(
'DELETE from %I WHERE %I IN (SELECT %I FROM %I WHERE %s LIMIT %s)',
tblname, colname, colname, sel_tblname, wherecon, batchsize
);Cheers,Greg
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
From
Álvaro Herrera
Date:
On 2025-Feb-03, Rajni Bobal wrote:
> Please clarify the use of %I will not be substituted by the string literal
> 'colname' rather its value will be substituted dynamically.
Yes, it is expanded dynamically to the _value_ of the variable
colname, not to the literal 'colname'. If you wanted to expand a
string literal there, you would use something like
SELECT format('some text .. %I ... more', 'colname');
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/