postgres doesn't recognize temp table in function
Postgres runs some simple checks on the function you are trying to create, and it finds (correctly) that the table work_list does not (yet) exist. I see two options:
1. "Fake it till you make it"
Obviously, you'd have to drop that table before you run the function in the same session to avoid a conflict. Better, though: use
CREATE TEMP TABLE IF NOT EXISTS in your function (Postgres 9.1+). You may want to truncate the table if it already exists ...However (see comments below), quoting the manual
The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g.,
CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example,CREATE TABLE foo (...); INSERT INTO foo VALUES(...);will not work as desired if packaged up into a single SQL function, since foo won't exist yet when theINSERTcommand is parsed. It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.
Bold emphasis mine.
2. Use PL/pgSQL instead
Checks are less thorough in plpgsql. If Postgres still complains (which it doesn't in this case), you can also execute SQL dynamically with EXECUTE.
Aside: In many cases, there is a more performant solution without temp table around the corner ...
combine the two statements. Create the temp table by doing a "select into" type syntax. In that way you can do it. CREATE TEMP TABLE SomeTable AS SELECT * FROM OtherTable ;