PostgreSQL中可以在CASE语句内使用INSERT吗？

Question

我正在尝试在Postgres中创建一个函数，如果行不存在就插入一行并返回该行的ID（新创建的或已存在的）。

我想到了以下代码：

CREATE OR REPLACE FUNCTION                                                      
get_enttype(name character varying, module character varying)               
RETURNS integer LANGUAGE SQL STABLE AS $$                                   
    SELECT typeid FROM enttypes WHERE name = $1 AND module = $2             
$$;                                                                             

CREATE OR REPLACE FUNCTION                                                      
    ensure_enttype(name character varying, module character varying)            
    RETURNS integer LANGUAGE SQL AS $$                                          
    SELECT CASE WHEN get_enttype($1, $2) IS NULL                                
    THEN                                                                        
        INSERT INTO enttypes(name, module) VALUES ($1, $2) RETURNING typeid                                                 
    ELSE                                                                        
        get_enttype($1, $2)                                                     
    END                                                                         
$$;                    

然而，由于CASE中包含INSERT，所以它引发了一个语法错误。我成功解决了这个问题，通过创建一个带有INSERT的单独函数，并在CASE中使用此函数。它按预期工作，但这种修复方式似乎有些奇怪。我的问题是 - 我能否在不创建另一个函数的情况下解决这个问题？

- zefciu

Answer 1

不，你不能在一个SQL函数中使用INSERT语句。但是你可以：

• 使用带有IF ... ELSE ... END语句的PL/PgSQL函数；或者
• 使用可写公共表达式（wCTE）或INSERT INTO ... SELECT查询

- Craig Ringer

Answer 2

纯SQL可以做到这一点：

create or replace function  ensure_enttype(
    name character varying, module character varying
) returns integer language sql as $$

    insert into enttypes(name, module)
    select $1, $2
    where get_enttype($1, $2) is null
    ;
    select get_enttype($1, $2);
$$;

- Clodoaldo Neto

---

但是如果记录已经存在，你的代码会返回typeid吗？ - zefciu