Использование временной таблицы в процедуре PL/pgSQL для очистки таблиц

Я пытаюсь удалить все данные, связанные с идентификатором пользователя, из базы данных игры.

Существует таблица со всеми играми (в каждую играют 3 игрока):

# select * from pref_games where gid=321;
 gid | rounds |          finished
-----+--------+----------------------------
 321 |     17 | 2011-10-26 17:16:04.074402
(1 row)

И есть таблица с очками игроков для этой игры № 321:

# select * from pref_scores where gid=321;
      id       | gid | money | quit
----------------+-----+-------+------
 OK531282114947 | 321 |   218 | f
 OK501857527071 | 321 |  -156 | f
 OK429671947957 | 321 |   -62 | f

Когда я пытаюсь выполнить следующую инструкцию SELECT INTO в приглашении psql PostgreSQL, кажется, что она работает так, как ожидалось (и временная таблица исчезает при закрытии сеанса):

# select gid into temp temp_gids from pref_scores where id='OK446163742289';
SELECT

# select * from temp_gids ;
 gid
------
 1895
 1946
 1998
 2094
 2177
 2215
(6 rows)

Но когда я пытаюсь создать свою процедуру PL/pgSQL, я получаю сообщение об ошибке:

    create or replace function pref_delete_user(_id varchar)
        returns void as $BODY$
            begin

            select gid into temp temp_gids from pref_scores where id=_id;
            delete from pref_scores where gid in
                (select gid from temp_gids);
            delete from pref_games where gid in
                (select gid from temp_gids);

            delete from pref_rep where author=_id;
            delete from pref_rep where id=_id;

            delete from pref_catch where id=_id;
            delete from pref_game where id=_id;
            delete from pref_hand where id=_id;
            delete from pref_luck where id=_id;
            delete from pref_match where id=_id;
            delete from pref_misere where id=_id;
            delete from pref_money where id=_id;
            delete from pref_pass where id=_id;
            delete from pref_status where id=_id;
            delete from pref_users where id=_id;

            end;
    $BODY$ language plpgsql;

Ошибка:

ERROR:  syntax error at "temp"
DETAIL:  Expected record variable, row variable, or list of scalar variables following INTO.
CONTEXT:  compilation of PL/pgSQL function "pref_delete_user" near line 3

Почему это (временные таблицы здесь запрещены?) и где сохранить мой временный список удаляемых gid?

(И я бы предпочел не использовать «каскад при удалении», потому что я еще не привык к этому, и мои сценарии/база данных еще не готовы к этому).


postgresql plpgsql select-into
person Alexander Farber    schedule 28.10.2011    source источник
comment
SELECT INTO имеет другое значение в plpgsql.   -  person Michael Krelin - hacker    schedule 28.10.2011
comment
Да, спасибо, так что здесь использовать вместо этого?   -  person Alexander Farber    schedule 28.10.2011

Ответы (3)


arrow_upward
12
arrow_downward

Вы можете создать временную таблицу, а затем выполнять обычные INSERT ... SELECT как отдельные операции:

create temporary table temp_gids (gid int not null) on commit drop;
insert into temp_gids (gid) select gid from pref_scores where id = _id;

Также есть LIKE для CREATE TABLE, если вы хотите дублировать структура таблицы:

LIKE parent_table [ like_option ... ]
Предложение LIKE указывает таблицу, из которой новая таблица автоматически копирует все имена столбцов, их типы данных и их ненулевые ограничения.

Но я думаю, что вам просто нужна временная таблица для хранения некоторых идентификаторов, так что это, вероятно, излишне.

SELECT INTO работает так, как вы ожидаете вне процедуры а>:

[...] эта форма SELECT INTO недоступна в ECPG или PL/pgSQL, поскольку они по-разному интерпретируют предложение INTO.

SELECT INTO используется для сохранения результата SELECT в локальной переменной внутри процедуры PostgreSQL:

Результат команды SQL, выдающий одну строку (возможно, из нескольких столбцов), может быть присвоен переменной записи, переменной типа строки или списку скалярных переменных. Это делается путем написания базовой команды SQL и добавления предложения INTO.

person mu is too short    schedule 28.10.2011
comment
Спасибо! Нужно ли мне ON COMMIT DROP при создании временной таблицы здесь внутри моей процедуры? - person Alexander Farber; 29.10.2011
comment
@Alexander: Не повредит, но вы хотели бы обернуть кишки процедуры в транзакцию (что вы, вероятно, захотите сделать в любом случае, если вы делаете кучу очистки и не хотите оставлять работу наполовину). - person mu is too short; 29.10.2011
comment
@muistooshort, что вы имеете в виду, когда говорите о заключении процедуры в транзакцию? - person Erwin Brandstetter; 30.10.2011
comment
@Erwin: Является ли вызов одной процедуры транзакцией? В противном случае он мог бы наполовину закончить очистку, если одно из DELETE не удалось. - person mu is too short; 30.10.2011
comment
@muistooshort: тело функции всегда выполняется как транзакция: все выполняется успешно или все терпит неудачу. Нет необходимости в явной транзакции. - person Erwin Brandstetter; 30.10.2011
comment
@muistooshort: вы можете использовать dblink, если вам нужны постоянные изменения, а не откат при ошибке. (Это не то, что вам обычно нужно, и не то, о чем вы здесь говорили. Просто для полноты картины). См. эту запись в Вики PostgreSQL. - person Erwin Brandstetter; 30.10.2011
comment
@Erwin: Спасибо, мне никогда не приходилось заботиться о каких-либо процедурах, которые я написал, поэтому мне никогда не приходилось их искать. Итак, ON COMMIT DROP, вероятно, хорошая идея? - person mu is too short; 30.10.2011
comment
@Alexander: Вас могут заинтересовать разговоры о транзакциях, которые я только что имел с Эрвином. Теперь я бы сказал да, вы, вероятно, хотите ON COMMIT DROP для временной таблицы. - person mu is too short; 30.10.2011

arrow_upward
14
arrow_downward

Помимо явного создания временной таблицы и последующей вставки в нее, есть еще один, более простой и правильный способ сделать это: CREATE TEMP TABLE AS как рекомендуется в документации:

Эта команда функционально похожа на SELECT INTO, но предпочтительнее, так как ее меньше путают с другими вариантами использования синтаксиса SELECT INTO. Кроме того, CREATE TABLE AS предлагает расширенный набор функций, предлагаемых SELECT INTO.

Для Postgres 9.1 или более поздней версии см. ниже.

Также было бы более эффективно использовать DELETE .. USING .. вместо вложенного выбора.
И да, если вы не планируете продолжать использовать временную таблицу (в том же сеансе) после фиксации транзакции, добавьте ON COMMIT DROP.

Собрав все вместе, ваша функция может выглядеть так:

CREATE OR REPLACE FUNCTION pref_delete_user(_id varchar)
  RETURNS void AS
$func$
BEGIN    
   CREATE TEMP TABLE tmp_gids ON COMMIT DROP AS
   SELECT gid FROM pref_scores WHERE id = _id;

   DELETE FROM pref_scores p
   USING  tmp_gids t
   WHERE  p.gid = t.gid;

   DELETE FROM pref_games p
   USING  tmp_gids t
   WHERE  p.gid = t.gid;

   -- more deletes ...    
END
$func$ LANGUAGE plpgsql;

Изменение данных CTE

В современном Postgres вышеприведенное имеет смысл только для сложных операций, где вам нужна фактическая временная таблица для работы, например, для создания индекса для нее, прежде чем продолжить.

В Postgres 9.1 или новее используйте изменяющие данные CTE для простых случаев, таких как тот, что под рукой:

   WITH gids AS (SELECT gid FROM pref_scores WHERE id = _id)
      , d1   AS (  
      DELETE FROM pref_scores p
      USING  gids t
      WHERE  p.gid = t.gid
      (
      -- more work using gids?
   DELETE FROM pref_games p
   USING  gids t
   WHERE  p.gid = t.gid;
person Erwin Brandstetter    schedule 29.10.2011
comment
Просто хотел упомянуть, что я увидел значительный прирост производительности в postgres 8.3, используя массив идентификаторов вместо временной таблицы. Что-то вроде deleter_ids = array(select id from ... where ...); delete from tbl where gid = ANY(deleter_ids); - person Thomas; 14.11.2014

arrow_upward
1
arrow_downward

Ты можешь попробовать

EXECUTE 'create temp table temp_gids AS select from pref_scores where id=$1'
    USING _id;
person Michael Krelin - hacker    schedule 28.10.2011