SQL functions

From Newroco tech docs
Jump to: navigation, search
  • Find out how much storage space a database uses
SELECT pg_size_pretty(pg_database_size('<my database name>'));
  • Find out all privileges of a certain user
select * from information_schema.role_table_grants where grantee='user name';
  • pg_grant(text role,text permissions,text relation_filter,text schema) - grants the specified permissions to the specified user on all tables, views and sequences of the specified schema matching the specified filter, for all, use filter '%'.

Note that sequences don't have insert and delete permissions, but have the 'usage' permission that tables and views don't have.

CREATE OR REPLACE FUNCTION pg_grant(text, text, text, 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 SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_grant(text, text, text, text) OWNER TO postgres;
  • pg_revoke(text role,text permissions,text relation_filter,text schema) - the revoke equivalent of the pg_grant function. It revokes the specified permissions from the specified user on all tables, views and sequences of the specified schema matching the specified filter, for all, use filter '%'.

Note that sequences don't have insert and delete permissions, but have the 'usage' permission that tables and views don't have.

CREATE OR REPLACE FUNCTION pg_revoke(text, text, text, 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 'REVOKE ' || $2 || ' ON ' || $4 || '."' || obj.relname || '" FROM ' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_revoke(text, text, text, text) OWNER TO postgres;
  • pg_makeblank(text schema,text filter) - makes a database blank keeping the schema intact. It deletes all the data from the tables of the specified schema matching the filter given. Then makes the matching sequences value 1.
CREATE OR REPLACE FUNCTION pg_makeblank(text,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 = 'r' AND nspname = $1 AND relname LIKE $2
LOOP
EXECUTE 'DELETE FROM ' || $1 || '."' || obj.relname || '" ';
num := num + 1;
END LOOP;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE relkind = 'S' AND nspname = $1 AND relname LIKE $2
LOOP
EXECUTE 'SELECT setval(''' || $1 || '."' || obj.relname || '"'',1) ';
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_makeblank(text, text) OWNER TO postgres;
  • pg_assignowner(text role,text relation_filter,text schema) - assigns owner to the specified role on all tables and views of the specified schema matching the specified filter. For all, use filter '%'.
CREATE OR REPLACE FUNCTION pg_assignowner(text, text, 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 SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_assignowner(text, text, text) OWNER TO postgres;
  • create_audit_tables(varchar(50)[],boolean) - This is a super function!

Example of call create_audit_tables(ARRAY['tbl_skeleton'],true); It creates for the tables specified in the parameter "tables" the associated audit table, with an associated sequence, audit function and trigger. If the second parameter of type boolean is true, then the function will first delete the associated entities before creating them.

DROP FUNCTION create_audit_tables(varchar(50)[],boolean);

CREATE OR REPLACE FUNCTION create_audit_tables(IN tables varchar(50)[],IN clean boolean)
  RETURNS boolean AS
$BODY$
DECLARE    
    i int;
    name varchar(50);
    columns varchar(1000);
    obj record;
BEGIN

    FOR i IN 1..array_upper(tables,1)
    LOOP
	name := tables[i];
	name := substring(name from (position('tbl_' in name)+ 4));

        IF clean IS NOT NULL AND clean = TRUE THEN 
		EXECUTE 'DROP TRIGGER IF EXISTS audit_' || name || ' ON ' || tables[i];
		EXECUTE 'DROP FUNCTION IF EXISTS audit_' || name || '()';
		EXECUTE 'DROP TABLE IF EXISTS audit_' || name;
		EXECUTE 'DROP SEQUENCE IF EXISTS audit_' || name || '_id_seq';
	END IF;	

	EXECUTE 'CREATE SEQUENCE audit_' || name || '_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1';
	EXECUTE 'create table audit_' || name || ' AS (select nextval(''audit_' || name || '_id_seq''::regclass) AS audit_id,null::timestamp without time zone AS last_modified_at,''INSERT''::varchar(10) AS action,* from ' || tables[i] ||')';	
	EXECUTE 'alter table audit_' || name || ' ALTER COLUMN audit_id SET DEFAULT nextval(''audit_' || name || '_id_seq''::regclass)';
	EXECUTE 'ALTER TABLE audit_' || name || ' ADD CONSTRAINT audit_' || name || '_pkey PRIMARY KEY (audit_id)';
	EXECUTE 'ALTER TABLE audit_' || name || ' ADD FOREIGN KEY (last_modified_by) REFERENCES tbl_staff_lookup (refid) ON UPDATE CASCADE ON DELETE RESTRICT';
	EXECUTE 'GRANT INSERT ON audit_' || name || ' TO osteology_data_entry ';
	EXECUTE 'GRANT USAGE,UPDATE ON audit_' || name || '_id_seq TO osteology_data_entry ';

	-- creating the specific trigger for this table
	columns := '';
	FOR obj IN select column_name,character_maximum_length,numeric_precision,UPPER(is_nullable) as is_nullable
		   from information_schema.columns where table_name = tables[i] order by ordinal_position ASC
	LOOP
		columns := columns || ',NEW."' || obj.column_name || '"';
	END LOOP;
	EXECUTE 'CREATE OR REPLACE FUNCTION audit_' || name || '() RETURNS trigger AS  
		$$
		BEGIN
		INSERT INTO audit_' || name || ' VALUES (DEFAULT,now(),TG_OP' || columns || ');			
		RETURN NULL;
		END;	
		$$	
		LANGUAGE ''plpgsql'' VOLATILE';
	
	EXECUTE 'CREATE TRIGGER audit_' || name || ' AFTER INSERT OR UPDATE ON '|| tables[i] || ' FOR EACH ROW EXECUTE PROCEDURE audit_' || name || '()';

    END LOOP;
    return true;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION create_audit_tables(varchar(50)[],boolean) OWNER TO postgres;