有一张桌子可容纳所有游戏(每个游戏由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临时列表的位置？
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 option to 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.
但我认为你只需要一个临时表来保存一些ID,这样就可能有些过分了.
SELECT INTO按预期工作outside a procedure：
[…] this form of SELECT INTO is not available in ECPG or PL/pgsql,because they interpret the INTO clause differently.
SELECT INTO用于将SELECT的结果存储在局部变量inside a PostgreSQL procedure中：
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.
