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

При разработке проектов используем БД 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. Для массового назначения привилегий таблицам и вьюшкам: ( источник функции )

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 можно модифицировать под свои нужды, например
— создавать вьюшки без правил, если нет ключевых полей
— если ключевых полей более одного, то создавать правила по всем ним, а не только по первому ключевому полю.