Can I use INSERT inside CASE in postgres

Question

I am trying to create a function in postgres that would insert a row if it doesn't exist and return the row's id (newly created or existing).

I came up with this:

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                                                                         
$$;                    

It however raises a syntax error because of INSERT inside CASE. I managed to fix this problem by creating a separate function with this INSERT and using this function in CASE. It works as expected, but this fix seems a little strange. My question is - can I fix this without creating another function?

Answer 1

No, you can't use INSERT inside CASE in an sql function. You can, however:

• Use a PL/PgSQL function with IF ... ELSE ... END statements; or
• Use a writable common table expression (wCTE) or INSERT INTO ... SELECT query