Using temp table in PL/pgSQL procedure for cleaning tables
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 theSELECT INTO
syntax. Furthermore,CREATE TABLE AS
offers a superset of the functionality offered bySELECT 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;
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 ... ]
TheLIKE
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.
You can try
EXECUTE 'create temp table temp_gids AS select from pref_scores where id=$1' USING _id;