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@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
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>