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

Solution 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.

Solution 2

Besides explicitly creating a temporary table and then inserting into it, there is another, simpler, right way to do it: CREATE TEMP TABLE AS as recommended in the docs:

This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.

For Postgres 9.1 or later see below.

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.

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

In modern day Postgres the above only makes sense for complicated operations where you need an actual temporary table to work with - for instance to create an index on it before you proceed.

In Postgres 9.1 or later use data-modifying CTEs for simple cases like the one at hand:

   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;

Solution 3

You can try

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

Comments

[Alexander Farber]

Thanks! Do I need ON COMMIT DROP when creating temp table here inside my procedure?

[mu is too short]

@Alexander: Couldn't hurt but you'd want to wrap the procedure's guts in a transaction (which you probably want to do anyway if you're doing a bunch of cleanup and don't want to leave the job half done).

[Erwin Brandstetter]

@muistooshort what do you mean by "wrap the procedure's guts in a transaction"?

[mu is too short]

@Erwin: Is a single procedure call a transaction? If not he could have a half finished clean up if one of the DELETEs failed.

[Erwin Brandstetter]

@muistooshort: The body of a function is always executed as a transaction: it all succeeds or it all fails. No need for an explicit transaction.

[Erwin Brandstetter]

@muistooshort: You can use dblink if you need changes to be permanent and not rolled back on an error. (That's not what you usually want - and not what you were talking about here. Just for the sake of completeness). See this entry in the PostgreSQL wiki.

[mu is too short]

@Erwin: Thanks, I've never needed to care for any of the procedures I've written so I've never needed to look it up. So ON COMMIT DROP is probably a good idea?

[mu is too short]

@Alexander: You might be interested in the back-and-forth I've just had with Erwin about transactions. I'd now say "yes, you probably do want ON COMMIT DROP on the temporary table".

[Thomas]

Just wanted to mention that I saw remarkable performance benefits on postgres 8.3 using an array of ids instead of a temp table. Something like deleter_ids = array(select id from ... where ...); delete from tbl where gid = ANY(deleter_ids);