How to CREATE FUNCTION IF NOT EXISTS?

user_1824969 0
Viewed 4888

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?

postgres-9.6 psql postgresql sql
4 Answers

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

user_27787100

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.

user_3303150

This is what I have to do

IF EXISTS (SELECT FROM information_schema.routines 
                 WHERE routine_schema = 'schema_name'
                   AND routine_name = 'vw_cmp') 
            THEN
                raise notice 'Routine vw_cmp EXISTS';
            ELSE
              --  create proc SQL
END IF;
user_18249690

I find this pretty intuitive:

If you dont care about function overloading, then use this.

DO $$
begin  
  PERFORM proname "name" FROM pg_proc WHERE proname LIKE 'summary_stats';
  IF NOT FOUND THEN
    CREATE OR REPLACE FUNCTION summary_stats(col TEXT, tbl TEXT) 
        RETURNS TABLE (
            "name" TEXT,
            "min" NUMERIC,
            "max" NUMERIC,
            "mean" NUMERIC,
            "sd" NUMERIC
        ) 
    AS $func$
    BEGIN
        RETURN QUERY EXECUTE FORMAT('SELECT
            ''%1$I''::text AS "name",
            ROUND(MIN(%1$I),5) "min",
            ROUND(MAX(%1$I),5) "max",
            ROUND(AVG(%1$I), 5) "mean",
            ROUND(stddev(%1$I), 5) "sd"
        FROM %2$I', col, tbl);
    END
    $func$
    LANGUAGE 'plpgsql';
  END IF;
END $$;

SELECT * FROM summary_stats('dividend', 'company_ratings')

But if you care about overloading, it gets more complicated:

DO $$
DECLARE
    this_func_name TEXT := 'summary_stats';
    this_func_nargs SMALLINT := 2;
    this_func_arg_names text[] := ARRAY['col', 'tbl'];
    
    same_arg_count BOOLEAN;
    same_arg_names BOOLEAN;
    found_funcs INTEGER;
BEGIN
    CREATE TEMP TABLE procs AS with tbl AS (
        SELECT
            oid id,
            proname "name",
            pronargs "nargs",
            unnest(proargnames) "argnames"
        FROM 
            pg_proc
        WHERE proname LIKE 'summary_stats'
        ORDER BY "id", "nargs"
    )
    SELECT 
        "name",
        (array_agg(DISTINCT "nargs"))[1] "nargs",
        (array_agg("argnames"))[1:"nargs"] "argnames"
    FROM tbl
    GROUP BY "id", "name", "nargs"
    ORDER BY "id", "nargs";
    
    found_funcs := (SELECT COUNT(*)::INTEGER FROM procs);
    same_arg_count := EXISTS(SELECT name FROM procs WHERE "nargs" = this_func_nargs);
    same_arg_names := EXISTS(SELECT name FROM procs WHERE "argnames" = this_func_arg_names);
  

  
  IF found_funcs = 0 OR same_arg_count = false OR same_arg_names = false THEN
    RAISE NOTICE 'CREATED FUNCTION ''%''', this_func_name;
    CREATE OR REPLACE FUNCTION summary_stats(col TEXT, tbl TEXT) 
        RETURNS TABLE (
            "name" TEXT,
            "min" NUMERIC,
            "max" NUMERIC,
            "mean" NUMERIC,
            "sd" NUMERIC
        ) 
    AS $func$
    BEGIN
        RETURN QUERY EXECUTE FORMAT('SELECT
            ''%1$I''::text AS "name",
            ROUND(MIN(%1$I),5) "min",
            ROUND(MAX(%1$I),5) "max",
            ROUND(AVG(%1$I), 5) "mean",
            ROUND(stddev(%1$I), 5) "sd"
        FROM %2$I', col, tbl);
    END
    $func$
    LANGUAGE 'plpgsql';
  END IF;
  DROP TABLE procs;
END $$;

SELECT * FROM summary_stats('dividend', 'company_ratings')

This function calculates the min, max, mean and sd of a column: col TEXT of a given table tbl TEXT.

If the function is not found in the PERFORM proname... line, it creates.

If the function is found, it skips the creation and simply runs it.

The output will always be:

+-----------+----------+---------+---------+---------+
|   name    |   min    |   max   |  mean   |   sd    |
+-----------+----------+---------+---------+---------+
| "dividend | -0.59737 | 0.72279 | 0.00374 | 0.12645 |
+-----------+----------+---------+---------+---------+
user_111275410
Related