CODEWITHSUNDEEP
sqlpostgresqlsql-function
Anand
Anand

Reputation: 1387

How to create a temporary function in PostgreSQL?

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?

Upvotes: 122

Views: 66930

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658452

A couple of additional notes to the smart trick in @crowmagnumb's answer:

The function must be schema-qualified at all times, even if pg_temp is in the search_path (like it is 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

A function created in the temporary schema is only visible inside the same session (like all temp objects). It's invisible to all other sessions (even for the same role). You can access the function as a different role in the same session after SET ROLE, though.

Works for a PROCEDURE in similar fashion. See:

To just execute a function repeatedly while all you need is SQL, consider a prepared statement instead. It 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 with EXECUTE, not nested inside 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:

Aside

You could even create a functional index based on this temporary function:

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

Thereby creating a non-temp index using a temp function on a non-temp table. Such an index would be visible to all sessions but still only usable for the creating session. The query planner will not use a functional index, where the functional expression is not repeated in the query. And other sessions cannot use the temp function because it's not visible to them. The expression index will still be kept up to date using the otherwise invisible temp function with writes, as usual. (!)

So one could (ab)use that for local testing in a session while no other session can use the same index (although it's visible to them). The index depends on the temp function, so both will be dropped automatically when the creating session is closed. Still a bit of a dirty trick. Feels like this should not be allowed at all ...

Upvotes: 97

user330315
user330315

Reputation:

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

With previous versions, you'll need to create the function, call it, and drop it again.

Upvotes: 31

crowmagnumb
crowmagnumb

Reputation: 7117

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.

Upvotes: 182

Byron Whitlock
Byron Whitlock

Reputation: 53921

For ad hock procedures, cursors aren't too bad. They are too inefficient for productino use however.

They will let you easily loop on sql results in the db.

Upvotes: -7

Related Questions