Блог

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

Существует таблица, на которой хранятся все игры (в каждую играют 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, подлежащих удалению?

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

Помимо явного создания временной таблицы и последующей вставки в нее, есть другой, более простой и правильный способ сделать это: 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;
 

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

 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 для СОЗДАНИЯ ТАБЛИЦЫ, если вы хотите дублировать структуру таблицы:

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

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

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

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

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

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

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

 EXECUTE 'create temp table temp_gids AS select from pref_scores where id=$1'
    USING _id;