在PL/pgSQL过程中使用临时表清理表 [英] Using temp table in PL/pgSQL procedure for cleaning tables

问题描述

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

I'm trying to delete all data related to a user id from a game database.

有一张桌子可容纳所有比赛(每场比赛由3名球员参加):

There is a table holding all games (each played by 3 players):

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

有一张桌子，上面列出了该游戏#321的玩家得分:

And there is a table holding players scores for that game #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语句时，它似乎按预期工作(并且关闭会话时，临时表消失了):

When I try the following SELECT INTO statement on the psql-prompt of PostgreSQL it seems to work as expected (and the temp table disappears when session is closed):

# 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过程时，出现错误:

But when I try to create my PL/pgSQL procedure I get error:

    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

为什么会这样(这里不允许使用临时表?)，以及将我要删除的女工的临时列表保存在哪里?

Why is that (temp tables not allowed here?) and where to save my temp list of the gid's to be deleted?

(而且我不希望使用在删除级联上"，因为我还不习惯，而且我的脚本/数据库还没有为此准备).

(And I'd prefer not to use "on delete cascade" because I'm not used to it yet and my scripts/database isn't prepared for that yet).

推荐答案

您可以创建临时表，然后执行通常的INSERT ... SELECT作为单独的操作:

You could create the temporary table and then do the usual INSERT ... SELECT as separate operations:

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;

如果您要创建表，还可以使用喜欢的选项要复制表的结构:

There's also a LIKE option to CREATE TABLE if you want to duplicate a table's structure:

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

LIKE parent_table [ like_option ... ]
The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

但是我认为您只需要一个临时表来保存一些ID，这样可能就算过分了.

But I think you just need a temporary table to hold some IDs so that's probably overkill.

SELECT INTO可以按您期望的方式运行 外部过程:

SELECT INTO works as you expect outside a procedure:

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

[...] this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently.

SELECT INTO用于将SELECT的结果存储在本地变量

SELECT INTO is used to store the result of a SELECT in a local variable inside a PostgreSQL procedure:

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

这篇关于在PL/pgSQL过程中使用临时表清理表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！