How to create a temporary function in PostgreSQL?

sql postgresql sql-function
Anand picture Anand · Feb 14, 2011 · Viewed 33.2k times · Source

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

crowmagnumb picture crowmagnumb · Apr 2, 2012

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.

Related questions

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which? In this very simple example, do they work the same, …

Read More
SQL function return-type: TABLE vs SETOF records

What's the difference between a function that returns TABLE vs SETOF records, all else equal. CREATE FUNCTION events_by_type_1(text) RETURNS TABLE(id bigint, name text) AS $$ SELECT id, name FROM events WHERE type = $1; $$ LANGUAGE SQL STABLE; CREATE FUNCTION …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More