SQL 如何在 PostgreSQL 中创建临时函数？

How to create a temporary function in PostgreSQL?

提问by Anand

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?

创建临时/一次性功能有什么好的方法吗？

回答by crowmagnumb

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 命令。

回答by Erwin Brandstetter

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

@crowmagnumb 的回答中对巧妙技巧的一些额外说明：

• The function must be schema-qualifiedat all times, even if pg_tempis in the search_path(like it is by default), according to Tom Laneto prevent Trojan horses:

• 该函数必须始终是模式限定的，即使pg_temp在search_path（就像默认情况下一样），根据 Tom Lane 的说法，以防止特洛伊木马：

CREATE FUNCTION pg_temp.f_inc(int)
  RETURNS int AS 'SELECT  + 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 couldaccess 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 ...

• 在临时模式中创建的函数仅在同一个会话中可见（就像临时表一样）。它对所有其他会话是不可见的（即使是相同的角色）。之后，您可以在同一会话中以不同角色访问该函数SET ROLE。

• 你甚至可以基于这个“临时”函数创建一个函数索引：

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

  从而在非临时表上使用临时函数创建普通索引。这样的索引对所有会话都是可见的，但仍然只对创建会话有效。查询计划器不会使用功能索引，其中表达式不会在查询中重复。仍然有点肮脏的把戏。当会话关闭时，它将自动删除 - 作为依赖对象。感觉这样的事情根本不应该被允许......

---

---

If you just need to execute a function repeatedly and all you need is SQL, consider a prepared statementinstead. It acts much like a temporary SQL functionthat dies at the end of the session. Not the samething, 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 =  WHERE tbl_id = ;

Call:

称呼：

EXECUTE upd_tbl(123, 'foo_name');

Details:

细节：

• Split given string and prepare case statement

• 拆分给定的字符串并准备 case 语句

回答by a_horse_with_no_name

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

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.

对于以前的版本，您需要创建函数，调用它，然后再次删除它。

回答by Byron Whitlock

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

对于临时过程，游标还不错。然而，它们对于生产用途来说效率太低。

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

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