在 PL/pgSQL 过程中使用临时表来清理表

Question

我正在尝试从游戏数据库中删除与用户 ID 相关的所有数据。

有一个表保存所有游戏（每个游戏由 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

当我在 PostgreSQL 的 psql-prompt 上尝试以下 SELECT INTO 语句时，它似乎按预期工作（会话关闭时临时表消失）：

# 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 的目的被删除？

（而且我不想使用“删除级联”，因为我还不习惯它，而且我的脚本/数据库还没有为此做好准备）。

Answer 1

数据修改 CTE

自 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;

临时表

在大多数情况下，您不需要实际临时表。例外情况是临时表上的索引有助于提高性能，或者您需要相同的临时表用于更多用途。

有一种更简单的方法：CREATE TEMP TABLE AS
正如手册中推荐的：

该命令在功能上与SELECT INTO类似，但它是
首选，因为它不太可能与其他用途混淆
SELECT INTO 语法。此外，CREATE TABLE AS 提供了一个超集
SELECT INTO 提供的功能。

使用 DELETE .. USING 也会更有效.. 而不是子选择。
是的，除非您计划在提交事务后在同一会话中使用临时表，否则请添加 ON COMMIT DROP。

将它们放在一起，您的函数可能如下所示：

CREATE OR REPLACE FUNCTION pref_delete_user(_id varchar)
  RETURNS void
  LANGUAGE plpgsql 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$;

注意：这不能是普通的 SQL 函数，因为它不能引用仅在同一函数中创建的对象。

Answer 2

您可以创建临时表，然后将通常的 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 选项来创建表 如果你想复制表的结构：

LIKE Parent_table [ like_option ... ]
LIKE 子句指定一个表，新表将自动从该表复制所有列名称、数据类型及其非空约束。

但我认为你只需要一个临时表来保存一些 ID，所以这可能有点矫枉过正。

SELECT INTO 按照您的预期工作外部一个过程：

[...]这种形式的 SELECT INTO 在 ECPG 或 PL/pgSQL 中不可用，因为它们对 INTO 子句的解释不同。

SELECT INTO 用于将 SELECT 的结果存储在局部变量中 在 PostgreSQL 过程内：

生成单行（可能是多列）的 SQL 命令的结果可以分配给记录变量、行类型变量或标量变量列表。这是通过编写基本 SQL 命令并添加 INTO 子句来完成的。

Answer 3

你可以尝试

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