CODEWITHSUNDEEP
postgresqlcasesql-insert
zefciu
zefciu

Reputation: 2047

Can I use INSERT inside CASE in postgres

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?

Upvotes: 3

Views: 4062

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

Pure SQL can do it:

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

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324771

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

Upvotes: 2

Related Questions