Make it easy on yourself

In the development of projects use PostgreSQL, because of its openness, the free and quite great functionality. The adopted architecture for the tables creating a view (views) and applications work with them. In many cases, views are one to one copy of the table, but each of them need to create and write the rules for updating, deleting and inserting records, which takes time.
And in one day, I got bored and I decided to automate this process. So there is the following function:

the

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;
--delete the view

--SELECT INTO exist_view relname FROM pg_class WHERE relname=_schema||'.v'||_table;
INTO exist_view SELECT relname 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;

--create view
EXECUTE 'CREATE OR REPLACE VIEW '||_schema||'.v' || _table || 'as select * from' || $2 || '.' || _table;

--key field of the table (when the key field one. if several then the key field is taken first key field)
sqlclmkey=";
--SELECT column_name FROM INFORMATION_SCHEMA sqlclmkey into.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;

--create rule on insert
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;

--create rule on 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;

--create a rule to delete
sql='CREATE RULE "v'||_table||'_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;

Just specify that it only works with tables, where one key field.
Example No. 1. The function call to the users table in the schema main:
the

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

the output view vusers with all the rules.

Example No. 2. The function call for tables, whose name starts with "gz_" in the schema main:
the

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

the output of a view for all specified tables with all the rules.

Also by having this feature, use:
1. For mass assignment of the owner's tables and views:

the

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;

the challenge is similar to the previous function:
Example. The function call for tables, whose name starts with "gz_" in the main schema to user umain:
the

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

for a view generated by the previous function.
the

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

and if there are no contradictions in the names, then you can do one call for tables and views:
the

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

2. For mass assignment of privileges to tables and views: ( source )
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 function pg_createview can be modified to fit your needs, for example
— create the view without rules, if no key fields
— if more than one key field, then generate rules for all of them, not just on the first key field.

Article based on information from habrahabr.ru