How to CREATE FUNCTION IF NOT EXISTS?-CodePudding

Is there are an easy way to do CREATE FUNCTION IF NOT EXISTS? I have multiple schemas and I am preparing a script that will create missing objects in the target schema. The plan is to run a script to check if the object exists, do nothing if it doesn't it will create it. 'CREATE SOMETHING IF NOT EXISTS' perfectly working with tables sequences and others, however cannot find the solution for functions. I am from Tsql world and it has this checking. However, looks like in Postgres 9.6 it doesn't work Is there any esy way around this limitation?

CodePudding user response：

You can use create or replace - but that doesn't work if the function's signature changes.

The other alternative is to use drop function if exists followed by a create function. Just make sure you set needed privileges after that again.

CodePudding user response：

You may wrap function definitions with anonymous block and handle duplicate name exception:

create function f(int)
returns int
language sql
as 'select $1';

✓

do $$
begin
  create function f (int)
  returns int
  language sql
  as 'select $1';
end; $$

ERROR:  function "f" already exists with same argument types
CONTEXT:  SQL statement "create function f (int)
 returns int
 language sql
 as 'select $1'"
PL/pgSQL function inline_code_block line 3 at SQL statement

do $$
begin
    create function f (int)
    returns int
    language sql
    as 'select $1';
    
  exception
    when duplicate_function then
    null;
end; $$

✓

db<>fiddle here