How to create a temporary function in PostgreSQL?

Question

I have to execute a loop in database. This is only a one time requirement. After executing the function, I am dropping the function now.

Is there any good approach for creating temporary / disposable functions?

Answer 1

I needed to know how to do a many time use in a script I was writing. Turns out you can create a temporary function using the pg_temp schema. This is a schema that is created on demand for your connection and is where temporary tables are stored. When your connection is closed or expires this schema is dropped. Turns out if you create a function on this schema, the schema will be created automatically. Therefore,

create function pg_temp.testfunc() returns text as  $$ select 'hello'::text $$ language sql; 

will be a function that will stick around as long as your connection sticks around. No need to call a drop command.

Answer 2

A couple of additional notes to the smart trick in @crowmagnumb's answer:

• The function must be schema-qualified at all times, even if pg_temp is in the search_path (like it is by default), according to Tom Lane to prevent Trojan horses:

CREATE FUNCTION pg_temp.f_inc(int)   RETURNS int AS 'SELECT $1 + 1' LANGUAGE sql IMMUTABLE;  SELECT pg_temp.f_inc(42); f_inc ----- 43

• A function created in the temporary schema is only visible inside the same session (just like temp tables). It's invisible to all other sessions (even for the same role). You could access the function as a different role in the same session after SET ROLE.

• You could even create a functional index based on this "temp" function:

  CREATE INDEX foo_idx ON tbl (pg_temp.f_inc(id)); 

  Thereby creating a plain index using a temporary function on a non-temp table. Such an index would be visible to all sessions but still only valid for the creating session. The query planner will not use a functional index, where the expression is not repeated in the query. Still a bit of a dirty trick. It will be dropped automatically when the session is closed - as a depending object. Feels like this should not be allowed at all ...

---

If you just need to execute a function repeatedly and all you need is SQL, consider a prepared statement instead. It acts much like a temporary SQL function that dies at the end of the session. Not the same thing, though, and can only be used by itself with EXECUTE, not nested inside another query. Example:

PREPARE upd_tbl AS UPDATE tbl t SET set_name = $2 WHERE tbl_id = $1; 

Call:

EXECUTE upd_tbl(123, 'foo_name'); 

Details:

• Split given string and prepare case statement