Make life easier

When developing projects, we use the PostgreSQL database, due to its openness, free of charge, and rather large functionality. According to the adopted architecture, we create views for tables (views) and applications already work with them. In many cases, one-to-one views copy tables, but each of them needs to create and write rules for updating, deleting and inserting records, which takes time.
And then one of the wonderful days, I got tired of it and I decided to automate this process. This is how the following function appeared:

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'||_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; 

At once I will make a reservation that it works only with tables, where there is one key field.
Example number 1. The function call for the users table in the main scheme:

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

at the output we get a view vusers with all the rules.

Example number 2. The function call for tables whose names begin with "gz_" in the main scheme:

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

at the output we get views for all specified tables with all the rules.
')
I also use it with this function:
1. For mass assignment of the owner to tables and views:

 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 call is similar to the previous function:
Example. The function call for tables whose names begin with "gz_" in the main scheme for the user umain:

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

for views created by the previous function

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

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

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

2. For mass assignment of privileges to tables and views: ( source of function )

 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 function pg_createview can be modified to fit your needs, for example
- create views without rules if there are no key fields
- if there are more than one key fields, then create rules for all of them, and not only for the first key field.