Using temp table in PL/pgSQL procedure for cleaning tables?

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.

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.

Thanks! Do I need ON COMMIT DROP when creating temp table here inside my procedure? – Alexander Farber Oct 28 at 20:33 @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).

– mu is too short Oct 28 at 23:00 @muistooshort what do you mean by "wrap the procedure's guts in a transaction"? – Erwin Brandstetter Oct 29 at 22:27 1 @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 Oct 29 at 22:56 1 @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". – mu is too short Oct 29 at 23:25.

You can try EXECUTE 'create temp table temp_gids AS select from pref_scores where id=$1' USING _id.

Besides explicitely 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. Another thing: it would be more efficient to use DELETE .. USING .. instead of a sub-select.

And yes, if do not want to use the temporary table outside the function, it is probably a good idea to add ON COMMIT DROP. So, putting it all together, your function could look like this: CREATE OR REPLACE FUNCTION pref_delete_user(_id varchar) RETURNS void AS $BODY$ BEGIN CREATE TMEP TABLE temp_gids ON COMMIT DROP AS SELECT gid FROM pref_scores WHERE id = _id; DELETE FROM pref_scores p USING temp_gids t WHERE p. Gid = t.

Gid; DELETE FROM pref_games p USING temp_gids t WHERE p. Gid = t. Gid; DELETE FROM pref_rep WHERE author = _id; DELETE FROM pref_rep WHERE id = _id; -- more deletes ... END; $BODY$ LANGUAGE plpgsql.

Great advice on DELETE ... USING, thank you – Alexander Farber Oct 30 at 12:44.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.