Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4

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(at)gmail(dot)com>
wrote:

> On Wed, Jan 22, 2025 at 10:59 AM Rajni Bobal <rajnibobal(at)gmail(dot)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
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>