Using temp table in PL/pgSQL procedure for cleaning tables

Question

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

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)

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

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)

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;

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?

(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).

Answer 1

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 ... ]
The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

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

SELECT INTO works as you expect 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 is used to store the result of a SELECT in a local variable 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 INTO clause.