1

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.

Improve this question
2

1 Answer 1

Reset to default
6

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, only for a single DML statement, and can only be used by itself, not nested in 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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.