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 Postgres 9.6 doesn't have it. Is there any easy way around this limitation?
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
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.
This is what I have to do
IF EXISTS (SELECT FROM information_schema.routines
WHERE routine_schema = 'schema_name'
AND rouitine_name = 'vw_cmp')
THEN
raise notice 'Routine vw_cmp EXISTS';
ELSE
-- create proc SQL
END IF;