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