有没有像“临时功能”那样的东西？ [英] Is there such thing as a "temp function"?

问题描述

有临时视图和临时表 - 有没有临时功能？我有一个函数，我想在事务中使用，然后丢弃（类似的用例到临时表）。另外，我想两个并发事务能够创建相同的名称相同的功能没有锁定争用。

解决方案

没有 CREATE TEMP FUNCTION ... 像现有的 CREATE TEMP TABLE ... 。但是在临时模式 pg_temp 中创建一个函数是个诀窍，就像链接的答案提供了。该函数仅在同一个会话中可见，并且只能通过模式限定的名称调用：

  CREATE FUNCTION  pg_temp 。 f_inc（int）
 RETURNS int AS'SELECT $ 1 + 1'语言sql IMMUTABLE; 
 
 SELECT  pg_temp。 f_inc（42）; 
 f_inc 
 ----- 
 43  

我可以追溯这个想法 Tom Lane在pgsql-general上的这篇文章 。

没有招数的最接近的东西就是准备好的语句。行为很像在会话结束时死亡的临时SQL函数。但不是 same ，只能由其自身使用，而不能在更大的查询中使用。示例：

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

通话：

  EXECUTE upd_tbl（123，'foo_name'）; 
  

详情：

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

There are temp views and temp tables - are there "temp functions"? I have a function that I'd like to use during a transaction, and then discard afterwards (similar use case to a temp table.) Also I'd like two concurrent transactions to be able to create this same function with the same name without lock contention.

解决方案

There is no CREATE TEMP FUNCTION ... like the existing CREATE TEMP TABLE .... But there is the trick to create a function in the temporary schema pg_temp, like the linked answer provides. The function is only visible within the same session and can only be called by schema-qualified name:

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

I could trace back the idea to this post by Tom Lane on pgsql-general.

The closest thing without tricks would be a prepared statement. 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, not in the context of a bigger 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:

• Split given string and prepare case statement

这篇关于有没有像“临时功能”那样的东西？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！