Облегчаем себе жизнь

При разработке проектов используем БД PostgreSQL, благодаря ее открытости, бесплатности и довольно большого функционала. По принятой архитектуре, для таблиц создаем вьюшки (представления) и приложения работают уже с ними. Во многих случаях вьюшки один в один копируют таблицы, но каждую из них надо создавать и писать правила для обновления, удаления и вставки записи, что занимает время.
И вот в один из прекрасных дней, мне это надоело и я решил автоматизировать этот процесс. Вот так появилась следующая функция:

CREATE OR REPLACE FUNCTION pg_createview(table_ text, schema_ text)
RETURNS integer AS 
$BODY$
DECLARE obj record;
num integer;
_schema alias for $2;
_tablelike alias for $1;
_table character varying;
sql character varying;
sqlclm1 character varying;
sqlclm2 character varying;
sqlclmkey character varying;
_col text;
exist_view character varying;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind ='r' AND
nspname = $2 AND
relname LIKE $1
LOOP
_table=obj.relname;
--удаление вьюшки

--SELECT relname INTO exist_view FROM pg_class WHERE relname=_schema||'.v'||_table;
SELECT relname INTO exist_view FROM pg_class c JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
nspname = _schema AND relname='v'||_table;

IF exist_view IS NOT NULL THEN
  EXECUTE 'DROP VIEW '||_schema||'.v' || _table;
END IF;

--создание вьюшки
EXECUTE 'CREATE OR REPLACE VIEW '||_schema||'.v' || _table || ' as select * from ' || $2 || '.' || _table;

--ключевое поле таблицы (случай, когда ключевое поле одно. если несколько то за ключевое поле берется первое ключевое поле)
sqlclmkey='';
--SELECT column_name into sqlclmkey FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_schema=_schema and table_name=_table and ordinal_position=1;

SELECT pg_attribute.attname into sqlclmkey 
FROM pg_index, pg_class, pg_attribute 
WHERE 
  pg_class.oid = (_schema||'.'||_table)::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary;

--создание правила на вставку
sqlclm1='';
sqlclm2='';
FOR _col IN execute 'select column_name from information_schema.columns where table_schema='||quote_literal(_schema)||' and table_name='||quote_literal(_table) Loop
 sqlclm1=sqlclm1||_col||',';
 sqlclm2=sqlclm2||'new.'||_col||',';
end loop;   
sqlclm1=substring(sqlclm1 from 1 for (length(sqlclm1)-1) );
sqlclm2=substring(sqlclm2 from 1 for (length(sqlclm2)-1) );
sql='CREATE RULE "v'||_table||'_ins"  AS ON INSERT TO "'||_schema||'"."v'||_table||'" DO INSTEAD (';
sql=sql||'INSERT INTO '||_schema||'.'||_table||'('||sqlclm1||') VALUES ('||sqlclm2||'););';
EXECUTE sql;

--создание правила на update
sqlclm1='';
sqlclm2='';

FOR _col IN execute 'select column_name from information_schema.columns where table_schema='||quote_literal(_schema)||' and table_name='||quote_literal(_table) Loop
 sqlclm1=sqlclm1||_col||'=new.'||_col||',';
end loop;   
sqlclm1=substring(sqlclm1 from 1 for (length(sqlclm1)-1) );
sql='CREATE RULE "v'||_table||'_upd" AS ON UPDATE TO "'||_schema||'"."v'||_table||'" DO INSTEAD (';
sql=sql||' UPDATE '||_schema||'.'||_table||' SET '||sqlclm1||' WHERE '||sqlclmkey||'=old.'||sqlclmkey||';);';
EXECUTE sql;

--создание правила на delete
sql='CREATE RULE "v'||_schema||'_del" AS ON DELETE TO "'||_schema||'"."v'||_table||'" DO INSTEAD (';
sql=sql||'DELETE FROM '||_schema||'.'||_table||' WHERE '||sqlclmkey||'=old.'||sqlclmkey||';);';
EXECUTE sql;

num := num + 1;

END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION pg_createview(text, text) OWNER TO postgres;

Сразу оговорюсь, что она работает только с таблицами, где одно ключевое поле.
Пример №1. Вызов функции для таблицы users в схеме main:

select pg_createview( 'users', 'main');

на выходе получаем вьюшку vusers со всеми правилами.

Пример №2. Вызов функции для таблиц, наименование которых начинается с «gz_» в схеме main:

select pg_createview( 'gz_%', 'main');
<source>
на выходе получаем вьюшки для всех указанных таблиц со всеми правилами.

Так же вместе с этой функцией использую еще:
  1. Для массового назначения владельца таблицам и вьюшкам:

<source lang="sql">
CREATE OR REPLACE FUNCTION pg_owner(user_ text, table_ text, schema_ text)
  RETURNS integer AS
$BODY$
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') AND
nspname = $3 AND
relname LIKE $2
LOOP

EXECUTE 'ALTER TABLE ' || $3 || '.' || obj.relname || ' OWNER TO '  || $1;

num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION pg_owner(text, text, text) OWNER TO postgres;

вызов аналогичен предыдущей функции:
Пример. Вызов функции для таблиц, наименование которых начинается с «gz_» в схеме main для пользователя umain:

select pg_createview( 'umain', 'gz_%', 'main');

для вьюшек созданных предыдущей функцией

select pg_createview( 'umain', 'vgz_%', 'main');

и если нет противоречий в названиях, то можно обойтись одним вызовом для таблиц и вьюшек:

select pg_createview( 'umain', '%gz_%', 'main');

2. Для массового назначения привилегий таблицам и вьюшкам: ( источник функции ^[1])

CREATE OR REPLACE FUNCTION pg_grant(user_ text, action_ text, table_ text, schema_ text)
  RETURNS integer AS
$BODY$
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 = $4 AND
relname LIKE $3
LOOP
EXECUTE 'GRANT ' || $2 || ' ON ' || $4 || '.' || obj.relname || ' TO ' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION pg_grant(text, text, text, text) OWNER TO postgres;

P|S функцию pg_createview можно модифицировать под свои нужды, например
— создавать вьюшки без правил, если нет ключевых полей
— если ключевых полей более одного, то создавать правила по всем ним, а не только по первому ключевому полю.

Автор: Radli007

Источник ^[2]