Как я могу вставить возврат DELETE в INSERT в postgresql?

Я пытаюсь удалить строку из одной таблицы и вставить ее с некоторыми дополнительными данными в другую. Я знаю, что это можно сделать в двух отдельных командах: один для удаления и другой для вставки в новую таблицу. Однако я пытаюсь объединить их, и он не работает, это мой запрос:

insert into b (one,two,num) values delete from a where id = 1 returning one, two, 5;

При запуске я получаю следующую ошибку:

ОШИБКА: синтаксическая ошибка на уровне или рядом с "delete"

Может ли кто-нибудь указать, как это сделать, или есть лучший способ? или это невозможно?

Ответ 1

До PostgreSQL 9.1 вы можете создать такую изменчивую функцию, как эта (непроверенная):

create function move_from_a_to_b(_id integer, _num integer)
returns void language plpgsql volatile as
$$
  declare
    _one integer;
    _two integer;
  begin
    delete from a where id = _id returning one, two into strict _one, _two;
    insert into b (one,two,num) values (_one, _two, _num);
  end;
$$

А затем просто используйте select move_from_a_to_b(1, 5). Функция имеет преимущество перед двумя операторами, которые всегда будут выполняться в одной транзакции - нет необходимости явно запускать и фиксировать транзакцию в клиентском коде.

Ответ 2

Вы не можете сделать это до PostgreSQL 9.1, который еще не выпущен. И тогда синтаксис будет

WITH foo AS (DELETE FROM a WHERE id = 1 RETURNING one, two, 5)
    INSERT INTO b (one, two, num) SELECT * FROM foo;

Ответ 3

Для всей версии PostgreSQL вы можете создать триггерную функцию для удаления строк из таблицы и вставки их в другую таблицу. Но это выглядит медленнее, чем объемная вставка, выпущенная в PostgreSQL 9.1. Вам просто нужно переместить старые данные в другую таблицу, прежде чем они будут удалены. Это делается с помощью типа данных OLD:

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
        INSERT INTO another_table VALUES(OLD.column1, OLD.column2,...);
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER moveDeleted
BEFORE DELETE ON table 
    FOR EACH ROW
        EXECUTE PROCEDURE moveDeleted();

Как и выше, после PostgreSQL 9.1 вы можете сделать это:

WITH tmp AS (DELETE FROM table RETURNING column1, column2, ...)
    INSERT INTO another_table (column1, column2, ...) SELECT * FROM tmp;

Ответ 4

Этот синтаксис, который у вас есть, недействителен. 2 - лучший способ сделать это. Самый интуитивный способ сделать это - сделать сначала вставку и удалить вторую.

Ответ 5

Как "AI W", два утверждения, безусловно, являются лучшим вариантом для вас, но вы также можете рассмотреть возможность создания триггера для этого. Каждый раз, когда что-то удаляется в вашей первой таблице, другая заполняется.