sql - 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.