Библиотека полезных функций для PostgreSQL
Автор компиляции: Иван Золотухин
Данная статья содержит выборку PL/PgSQL и SQL функций, которые могут быть полезны разработчикам и администраторам PostgreSQL, а также всем желающим познакомиться с основными процедурными языками в СУБД PostgreSQL на работающих примерах. Большая часть этих функций обсуждалась в англоязычных рассылках и иных каналах общения членов сообщества PostgreSQL, некоторые функции написаны мною. Где это возможно, у функций приведены авторы и небольшие комментарии. Сам лист функций время от времени пополняется, так что следите за изменениями, если примеры представляют для вас интерес.
Генерация случайного пароля
Вариант 1
-- Author: Magnus Hagander
CREATE FUNCTION generate_random_password() RETURNS text
AS $$
DECLARE
j int4;
result text;
allowed text;
allowed_len int4;
BEGIN
allowed := '23456789abcdefghjkmnpqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ&#%@';
allowed_len := length(allowed);
result := '';
WHILE length(result) < 16 LOOP
j := int4(random() * allowed_len);
result := result || substr(allowed, j+1, 1);
END LOOP;
RETURN result;
END;
$$
LANGUAGE plpgsql;
Вариант 2
-- Author: Jeff Ross
CREATE OR REPLACE FUNCTION gen_password() RETURNS text AS $$
DECLARE
password text;
chars text;
BEGIN
password := '';
chars :=
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
FOR i IN 1..8 LOOP
password := password || SUBSTRING(chars,
ceil(random()*LENGTH(chars))::integer, 1);
END LOOP;
return password;
END;
$$
LANGUAGE plpgsql;
Работа с интервалами: сдвиг на N месяцев
Простая функция на языке SQL для добавления или вычитания из даты заданного количества месяцев.
-- Author: Martijn van Oosterhout CREATE OR REPLACE FUNCTION GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS $_$ SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE; $_$ LANGUAGE SQL
Найти всех пользователей данной роли
Рекурсивная функция поиска всех ролей, принадлежащих данной роли. То есть, если роль ID1 имеет членов ID2 и ID3, а в роль ID2 входят роли ID4 и ID5, то функция вернет список всех этих ролей.
-- Author: David Fetter
CREATE OR REPLACE FUNCTION get_roles_under(OID)
RETURNS SETOF OID
LANGUAGE sql
AS $$
SELECT
$1
UNION
SELECT
member
FROM
pg_catalog.pg_auth_members
WHERE
roleid = $1
UNION
SELECT
get_roles_under(roleid) AS "roleid"
FROM
pg_catalog.pg_auth_members
WHERE
roleid IN (
SELECT
member
FROM
pg_catalog.pg_auth_members
WHERE
roleid = $1
)
$$;
Kill бакенда и получение имени текущего пользователя
Довольно глупый, но тем не менее reference-пример прекращения работы бакенда и получения имени пользователя, вызывающего функцию:
CREATE FUNCTION kill_process(integer) RETURNS boolean
AS 'SELECT pg_cancel_backend(procpid)
FROM (SELECT procpid FROM pg_stat_activity WHERE procpid=$1 and usename=session_user)
AS kill;'
LANGUAGE SQL SECURITY DEFINER;
Начало и конец недели по ее номеру в году
Работа с функциями, связанными со временем:
-- Author: Andreas Kretschmer
create or replace function get_week(IN jahr int, IN kw int) returns text as $$
declare
datum date;
ret text;
begin
datum = (jahr || '-01-01')::date;
loop
exit when extract(dow from datum) = 4;
datum = datum + '1day'::interval;
end loop;
ret = to_char(datum+(7*(kw-1)-3||'days')::interval,'dd-mm-yyyy') || ' - ' || to_char(datum+(3+7*(kw-1)||'days')::interval,'dd-mm-yyyy');
return ret;
end;
$$ language plpgsql immutable strict;
-- Example:
test=# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
Манипуляции с хозяином/пермиссиями таблиц и функций в схеме
Таблицы
Функция для изменения прав доступа к таблицам в заданной схеме по заданной LIKE-маске. Администраторам должно быть удобно на основании этого прототипа также сделать функции revoke_on_tables() и change_owner_of_tables() -- они будут использовать абсолютно такой же цикл, только динамический SQL внутри него будет несколько иной.
-- Author: Ivan Zolotukhin
-- License: BSD
CREATE OR REPLACE FUNCTION grant_on_tables(role_name text, permission text, mask text, schema_name text)
RETURNS integer LANGUAGE plpgsql
AS $$
-- Function that grants given permissions to given role on tables with given LIKE mask within given schema
-- Example:
-- SELECT grant_on_tables('role_developer','SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCE, TRIGGER','%','public');
-- will grant all the maximum permissions on all tables within public schema to role_developer role
DECLARE
obj record;
num integer;
BEGIN
num := 0;
FOR obj IN
SELECT
relname
FROM
pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid)
WHERE
relkind in ('r','v','S')
AND nspname = schema_name
AND relname LIKE mask
ORDER BY
relname
LOOP
EXECUTE 'GRANT ' || permission || ' ON ' || obj.relname || ' TO ' || role_name;
RAISE NOTICE '%', 'Done: GRANT ' || permission || ' ON ' || obj.relname || ' TO ' || role_name;
num := num + 1;
END LOOP;
RETURN num;
END;
$$;
Функции
Эта функция является вспомогательной и как правило ее не нужно вызывать руками. Она нужна для получения необходимой информации об аргументах функций, так как в PostgreSQL для манипуляций с хозяином или пермиссиями функции требуется указывать fully-qualified имя со списком аргументов.
-- Author: Ivan Zolotukhin
-- License: BSD
CREATE OR REPLACE FUNCTION pg_get_function_args(
IN funcname character varying,
IN funcargs oidvector,
IN schema character varying,
OUT pos integer,
OUT direction character,
OUT argname character varying,
OUT datatype character varying) RETURNS SETOF RECORD
AS $$
-- For a function name and schema, this procedure selects for each argument the following data:
-- - position in the argument list (0 for the return value)
-- - direction 'i', 'o', or 'b'
-- - name (NULL if not defined)
-- - data type
DECLARE
rettype character varying;
argtypes oidvector;
allargtypes oid[];
argmodes "char"[];
argnames text[];
mini integer;
maxi integer;
BEGIN
/* get object ID of function */
SELECT INTO
rettype,
argtypes,
allargtypes,
argmodes,
argnames
CASE
WHEN
pg_proc.proretset
THEN
'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)
ELSE
pg_catalog.format_type(pg_proc.prorettype, NULL)
END,
pg_proc.proargtypes,
pg_proc.proallargtypes,
pg_proc.proargmodes,
pg_proc.proargnames
FROM
pg_catalog.pg_proc
JOIN pg_catalog.pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid)
WHERE
pg_proc.proname = funcname
AND pg_proc.proargtypes = funcargs
AND pg_namespace.nspname = schema
AND pg_catalog.pg_function_is_visible(pg_proc.oid);
/* bail out if not found */
IF NOT FOUND THEN
RETURN;
END IF;
/* return a row for the return value */
pos = 0;
direction = 'o'::char;
argname = 'RETURN VALUE';
datatype = rettype;
RETURN NEXT;
/* unfortunately allargtypes is NULL if there are no OUT parameters */
IF allargtypes IS NULL THEN
mini = array_lower(argtypes, 1);
maxi = array_upper(argtypes, 1);
ELSE
mini = array_lower(allargtypes, 1);
maxi = array_upper(allargtypes, 1);
END IF;
IF maxi < mini THEN
RETURN;
END IF;
/* loop all the arguments */
FOR i IN mini .. maxi
LOOP
pos = i - mini + 1;
IF argnames IS NULL THEN
argname = NULL;
ELSE
argname = argnames[i];
END IF;
IF allargtypes IS NULL THEN
direction = 'i'::char;
datatype = pg_catalog.format_type(argtypes[i], NULL);
ELSE
direction = argmodes[i];
datatype = pg_catalog.format_type(allargtypes[i], NULL);
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;$$
LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;
А этот пример представляет непосредственный интерес. Функция выдает необходимые пермиссии всем функциям с названиями, удовлетворяющим LIKE-маске в заданной схеме:
-- Author: Ivan Zolotukhin
-- License: BSD
CREATE OR REPLACE FUNCTION grant_on_functions(role_name text, permission text, mask text, schema_name text)
RETURNS integer LANGUAGE plpgsql
AS $$
-- Function that grants given permissions to given role on functions with given LIKE mask within given schema
-- Example:
-- SELECT grant_on_functions('role_developer','EXECUTE','%','public');
-- will grant the execute permission on all functions within public schema to role_developer role
DECLARE
func record;
func_argument record;
argument_list varchar;
num integer;
BEGIN
num := 0;
/* select all function's names in given schema */
FOR func IN
SELECT
proname, proargtypes
FROM
pg_proc AS pro
JOIN pg_namespace AS ns ON pro.pronamespace = ns.oid
WHERE
ns.nspname = schema_name
AND proname LIKE mask
AND pg_catalog.pg_function_is_visible(pro.oid)
ORDER BY
proname
LOOP
argument_list := '(';
-- get input arguments of the function we're granting permissions on
FOR func_argument IN
SELECT datatype FROM pg_get_function_args(func.proname::varchar, func.proargtypes::oidvector, schema_name) WHERE direction = 'i'
LOOP
argument_list := argument_list || func_argument.datatype || ', ';
END LOOP;
SELECT INTO argument_list rtrim(argument_list, ', ');
argument_list := argument_list || ')';
-- grant permissions when we know complete function designation like
-- my_best_function(varchar, text, integer, float)
EXECUTE 'GRANT ' || permission || ' ON FUNCTION ' || schema_name || '.' || func.proname || argument_list || ' TO ' || role_name;
RAISE NOTICE '%', 'Done: GRANT ' || permission || ' ON FUNCTION ' || schema_name || '.' || func.proname || argument_list || ' TO ' || role_name;
num := num + 1;
END LOOP;
RETURN num;
END;
$$;
Как и в случае с таблицами, администратору может быть удобно использовать функцию change_owner_of_functions(), которая практически не будет отличаться от приведенного выше примера.
Any feedback is welcome at iz at sai dot msu dot ru
Jun 2007