Is there such thing as a "temp function"?

Question

There are temp views and temp tables - are there "temp functions"? I have a function that I'd like to use during a transaction, and then discard afterwards (similar use case to a temp table.) Also I'd like two concurrent transactions to be able to create this same function with the same name without lock contention.

Answer 1

There is no CREATE TEMP FUNCTION ... like the existing CREATE TEMP TABLE .... But there is the trick to create a function in the temporary schema pg_temp, like the linked answer provides. The function is only visible within the same session and can only be called by schema-qualified name:

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

I could trace back the idea to this post by Tom Lane on pgsql-general.

The closest thing without tricks would be a prepared statement. 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, not in the context of a bigger 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