postgresql 在 PL/pgSQL 过程中使用临时表来清理表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7932962/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using temp table in PL/pgSQL procedure for cleaning tables
提问by Alexander Farber
I'm trying to delete all data related to a user id from a game database.
我正在尝试从游戏数据库中删除与用户 ID 相关的所有数据。
There is a table holding all games (each played by 3 players):
有一张桌子，上面放着所有游戏（每个游戏由 3 个玩家玩）：
# select * from pref_games where gid=321;
gid | rounds | finished
-----+--------+----------------------------
321 | 17 | 2011-10-26 17:16:04.074402
(1 row)
And there is a table holding players scores for that game #321:
并且有一张表格记录了该游戏#321 的玩家得分：
# select * from pref_scores where gid=321;
id | gid | money | quit
----------------+-----+-------+------
OK531282114947 | 321 | 218 | f
OK501857527071 | 321 | -156 | f
OK429671947957 | 321 | -62 | f
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):
当我在 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)
But when I try to create my PL/pgSQL procedure I get error:
但是当我尝试创建我的 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;
The error:
错误：
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?
为什么会这样（此处不允许使用临时表？）以及在哪里保存要删除的 gid 的临时列表？
(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).
（而且我不想使用“删除级联”，因为我还不习惯它，我的脚本/数据库还没有为此做好准备）。
回答by Erwin Brandstetter
Besides explicitly creating a temporary table and then inserting into it, there is another, simpler, rightway to do it: CREATE TEMP TABLE ASas recommended in the docs:
除了显式创建一个临时表然后插入其中之外，还有另一种更简单、正确的方法：CREATE TEMP TABLE AS如文档中所推荐：
This command is functionally similar to
SELECT INTO, but it is preferred since it is less likely to be confused with other uses of theSELECT INTOsyntax. Furthermore,CREATE TABLE ASoffers a superset of the functionality offered bySELECT INTO.
此命令在功能上类似于
SELECT INTO，但它是首选，因为它不太可能与SELECT INTO语法的其他用法混淆。此外，CREATE TABLE AS还提供了由SELECT INTO.
For Postgres 9.1 or latersee below.
对于 Postgres 9.1 或更高版本，请参见下文。
It would also be more efficient to use DELETE .. USING ..instead of a sub-select.
And yes, if you are not planning to keep using the temp table (in the same session) after the transaction is committed, add ON COMMIT DROP.
使用DELETE .. USING ..代替子选择也会更有效。
是的，如果您不打算在事务提交后继续使用临时表（在同一会话中），请添加ON COMMIT DROP.
Putting it all together, your function could look like this:
将它们放在一起，您的函数可能如下所示：
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;
Data modifying CTE
数据修改 CTE
In modern day Postgres the above only makes sense for complicated operations where you need an actualtemporary table to work with - for instance to create an index on it before you proceed.
在现代 Postgres 中，上述仅适用于需要实际临时表来处理的复杂操作- 例如在继续之前在其上创建索引。
In Postgres 9.1 or lateruse data-modifying CTEsfor simple cases like the one at hand:
在 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;
回答by mu is too short
You could create the temporary table and then do the usual INSERT ... SELECTas separate operations:
您可以创建临时表，然后执行通常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;
There's also a LIKE option to CREATE TABLEif you want to duplicate a table's structure:
如果要复制表的结构，还有一个LIKE 选项可以用于 CREATE TABLE：
LIKE parent_table [ like_option ... ]
TheLIKEclause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.
LIKE parent_table [ like_option ... ]
该LIKE子句指定一个表，新表从该表自动复制所有列名、它们的数据类型和它们的非空约束。
But I think you just need a temporary table to hold some IDs so that's probably overkill.
但我认为你只需要一个临时表来保存一些 ID，所以这可能有点过头了。
SELECT INTOworks as you expect outsidea procedure:
SELECT INTO在程序之外按您的预期工作：
[...] this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently.
[...] 这种形式的 SELECT INTO 在 ECPG 或 PL/pgSQL 中不可用，因为它们对 INTO 子句的解释不同。
SELECT INTOis used to store the result of a SELECT in a local variable inside a PostgreSQL procedure:
SELECT INTO用于将 SELECT 的结果存储在PostgreSQL 过程中的局部变量中：
The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an
INTOclause.
产生单行（可能是多列）的 SQL 命令的结果可以分配给记录变量、行类型变量或标量变量列表。这是通过编写基本 SQL 命令并添加
INTO子句来完成的。
回答by Michael Krelin - hacker
You can try
你可以试试
EXECUTE 'create temp table temp_gids AS select from pref_scores where id='
USING _id;
