Using a temporary table in PL / pgSQL to clean tables

Question

I am trying to delete all data related to user id from game database.

There is a table containing all the games (each of which consists of 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 in which players play for 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 to execute the following SELECT INTO statement at the PostgreSQL psql prompt, it works as expected (and the temp table disappears when the session closes):

 # 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 an 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; 

Mistake:

 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 this (temporary tables not allowed here?) And where to save my list of topics from the gid to be deleted?

(And I would prefer not to use "to remove the cascade", because I'm not used to it yet, and my scripts / database is not prepared for this yet).

Answer 1

You can create a temporary table and then do the usual INSERT ... SELECT as a separate operation:

 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 is also a LIKE option for CREATE TABLE if you want to duplicate the table structure:

LIKE parent_table [ like_option ... ]
The LIKE indicates a table from which the new table automatically copies all column names, their data types, and their non-zero constraints.

But I think you just need a temporary table to hold some identifiers in order to probably survive.

SELECT INTO works as you would expect outside the procedure :

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

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

The result of an SQL command giving one row (possibly several columns) can be assigned to a record variable, a row type variable or a list of scalar variables. This is done by writing a basic SQL command and adding an INTO clause.

Answer 2

In addition, by explicitly creating a temporary table and then inserting into it, there is another, easier way to right : CREATE TEMP TABLE AS as recommended in the docs :

This command is functionally similar to SELECT INTO , but it is preferable because it is less likely to be confused with other uses of the SELECT INTO syntax. In addition, 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 subselect.
And yes, if you are not going to use the temp table outside the function, add ON COMMIT DROP .

Combining all this, your function might 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; 

Change CTE Data

In modern Postgres, the foregoing makes sense only for complex operations in which you need an actual temporary table to work with - for example, to create an index on it before continuing.

In Postgres 9.1 or later, use CTE modifying data for simple cases, such as 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; 

Answer 3

You can try

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