如何创建函数如果不存在?

Question

是否有一种简单的方法来执行CREATE FUNCTION IF NOT EXISTS？我有多个模式，我正在准备一个脚本，将在目标模式中创建丢失的对象。计划是运行一个脚本来检查对象是否存在，如果不存在则不做任何事情——它将创建对象。"CREATE SOMETHING IF NOT EXISTS"完美地与表序列和其他工作，但是不能找到函数的解决方案。我来自Tsql世界，它有这个检查。然而，看起来Postgres 9.6没有这个功能。有什么简单的方法可以绕过这个限制吗?

Answer 1

您可以用匿名块包装函数定义并处理重复名称的异常:

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"已存在且参数类型相同背景:SQL语句"创建函数f (int)返回int语言sql如select $1 "SQL语句

第3行inline_code_block

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

exception
when duplicate_function then
null;
end; $$

✓

db<此处小提琴>

Answer 2

您可以使用create or replace-但如果函数的签名发生变化，则无法工作。

另一种选择是使用drop function if exists和create function。在此之后，请确保再次设置所需的权限。

Answer 3

这就是我要做的

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;

Answer 4

我觉得这很直观:

如果你不关心函数重载，那么使用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')

但是如果你关心重载，它会变得更复杂:

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')

此函数计算给定表tbl TEXT的列col TEXT的min、max、mean和sd。

如果函数没有找到PERFORM proname...线,它创造了。

如果找到该函数，则跳过创建，直接运行它。

输出总是:

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