How to create a temporary function in PostgreSQL?

Question

I need to execute a loop in a database. This is just a one-time requirement. After executing the function, I now discard the function.

Is there a good approach for creating temporary / one-time functions?

Answer 1

I needed to know how to use the script I wrote many times. Turns out you can create a temporary function using the pg_temp scheme. This is a schema that is created on demand for your connection and where temporary tables are stored. When your connection is closed or expires, this circuit will be deleted. It turns out that if you create a function in this scheme, the scheme will be created automatically. Hence,

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

will be a function that will stick as long as your connection closes. There is no need to call the drop command.

Answer 2

A few additional clever trick notes in @crowmagnumb answer :

• This function should always be qualified on the circuit , even if pg_temp is in the search_path (for example, 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 

• The function created in the timeline is visible only within one session (like temp tables). It is invisible to all other sessions (even for the same role). You can access the function as another role in the same session after SET ROLE .

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

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

  Thus, a simple index is created using a temporary function in a table other than temp. Such an index will be displayed for all sessions, but still valid only for the creation session. The query planner will not use a functional index in which the expression will not be repeated in the query. Still a bit of a dirty trick. It will be automatically discarded when the session is closed - as a dependent object. Such a feeling should not be allowed at all ...

---

If you just need to execute the function again, and all you need is SQL, consider the prepared statement . It acts as a temporary SQL function that dies at the end of a session. However, it is not the same thing, and it can only be used with EXECUTE , not nested in 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 the given string and prepare the register

Answer 3

If you are using version 9.0, you can do this with the new DO statement:

http://www.postgresql.org/docs/current/static/sql-do.html

In previous versions, you would need to create a function, call it, and add it again.

Answer 4

For ad hock cursors procedures are not so bad. However, they are too inefficient to use the product.

They will allow you to easily loop on sql results in db.