如何在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?

是否有创建临时/一次性函数的好方法?

4 个解决方案

#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,

我需要知道如何在我正在编写的脚本中多次使用。您可以使用pg_temp模式创建一个临时函数。这是根据连接的需要创建的模式，是存储临时表的地方。当连接关闭或过期时，此模式将被删除。如果您在这个模式上创建一个函数，那么这个模式将被自动创建。因此,

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.

将是一个函数，只要你的连接保持在周围，它就会一直存在。不需要调用drop命令。

#2

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

@crowmagnumb的回答中还有几个聪明的技巧补充说明:

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 * horses:
该函数必须在任何时候都是模式限定的，即使pg_temp在search_path(默认情况下也是这样)，根据Tom Lane防止特洛伊木马:

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 (just like temp tables). It's invisible to all other sessions (even for the same role). You could access the function as a different role in the same session after SET ROLE.

在临时模式中创建的函数只在同一会话中可见(就像临时表一样)。它对所有其他会话都是不可见的(即使是相同的角色)。您可以在设置后的相同会话中以不同的角色访问该函数。
You could even create a functional index based on this "temp" function:

您甚至可以基于这个“临时”函数创建一个函数索引:
```
CREATE INDEX foo_idx ON tbl (pg_temp.f_inc(id));
```
Thereby creating a plain index using a temporary function on a non-temp table. Such an index would be visible to all sessions but still only valid for the creating session. The query planner will not use a functional index, where the expression is not repeated in the query. Still a bit of a dirty trick. It will be dropped automatically when the session is closed - as a depending object. Feels like this should not be allowed at all ...

因此，使用非临时表上的临时函数创建普通索引。这样的索引对所有会话都是可见的，但仍然只对创建会话有效。查询计划器不会使用函数索引，其中表达式在查询中不会重复。还是有点卑鄙的伎俩。当会话关闭时，它将作为一个依赖对象自动删除。感觉这根本不应该被允许……

If you just need to execute a function repeatedly and 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:

如果您只是需要重复执行一个函数，而您所需要的只是SQL，那么请考虑使用一个准备好的语句。它的作用非常类似于在会话结束时终止的临时SQL函数。不过，这不是同一件事，只能自己使用EXECUTE，而不能嵌套在另一个查询中。例子:

PREPARE upd_tbl AS
UPDATE tbl t SET set_name = $2 WHERE tbl_id = $1;

Call:

电话:

EXECUTE upd_tbl(123, 'foo_name');

Details:

细节:

Split given string and prepare case statement
分割给定字符串并准备case语句

#3

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

如果您使用的是9.0版本，可以使用新的do语句:

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.

在以前的版本中，您需要创建函数、调用它并再次删除它。

#4

-4

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

对于adhock过程，游标不是太坏。然而，它们对产品的使用效率太低。

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

它们可以让您轻松地在db中循环sql结果。

#1

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