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

Поиск
Список
Период
Сортировка
От Greg Sabino Mullane
Тема Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
Дата
Msg-id CAKAnmmL9EP6BpVUfytjb32j_ZfO=tT63WOQy4dHSs8enaR=ziA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4  (Rajni Bobal <rajnibobal@gmail.com>)
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления: