Библиотека полезных функций для 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