SQL functions

From Newroco Tech Docs
Revision as of 12:04, 7 April 2023 by Lucian.pricop (talk | contribs)
Jump to navigationJump to 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_grant2(text role,text permissions,text relation_filter) - grants the specified permissions to the specified user on all tables, views and sequences on all schemas. 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_grant2(text, text, text)
  RETURNS integer AS
$BODY$
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname,nspname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in ('r','v','S') AND
relname LIKE $3
LOOP
EXECUTE 'GRANT ' || $2 || ' ON ' || obj.nspname || '."' || 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_drop_tables(text schema,text filter) - drops all tables in selected schema that match the filter.
CREATE OR REPLACE FUNCTION pg_drop_tables(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 'DROP TABLE ' || $1 || '."' || obj.relname || '"';
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_drop_tables(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;


  • pg_setval_max(text schema,table_name DEFAULT NULL,raise_notice boolean DEFAULT true) - Sets all the sequences in the schema "schema_name" to the max(id) of every table (or a specific table, if name is supplied)
CREATE OR REPLACE FUNCTION pg_setval_max
(
    schema_name name,
    table_name name DEFAULT NULL::name,
    raise_notice boolean DEFAULT true
)
RETURNS void AS
$BODY$

DECLARE
    row_data RECORD;
    sql_code TEXT;

BEGIN
    IF ((SELECT COUNT(*) FROM pg_namespace WHERE nspname = schema_name) = 0) THEN
        RAISE EXCEPTION 'The schema "%" does not exist', schema_name;
    END IF;

    FOR sql_code IN
        SELECT 'SELECT SETVAL(' ||quote_literal(N.nspname || '.' || S.relname)|| ', MAX(' ||quote_ident(C.attname)|| ') ) FROM ' || quote_ident(N.nspname) || '.' || quote_ident(T.relname)|| ';' AS sql_code
            FROM pg_class AS S
            INNER JOIN pg_depend AS D ON S.oid = D.objid
            INNER JOIN pg_class AS T ON D.refobjid = T.oid
            INNER JOIN pg_attribute AS C ON D.refobjid = C.attrelid AND D.refobjsubid = C.attnum
            INNER JOIN pg_namespace N ON N.oid = S.relnamespace
            WHERE S.relkind = 'S' AND N.nspname = schema_name AND (table_name IS NULL OR T.relname = table_name)
            ORDER BY S.relname
    LOOP
        IF (raise_notice) THEN
            RAISE NOTICE 'sql_code: %', sql_code;
        END IF;
        EXECUTE sql_code;
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
  • 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 IF EXISTS 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 text;
	obj record;
BEGIN
	FOR i IN 1..array_upper(tables,1)
	LOOP
	name := tables[i];
	IF position('tbl_' in name) > 0 THEN
		name := substring(name from (position('tbl_' in name)+ 4));
	END IF;

	IF clean IS NOT NULL AND clean = TRUE THEN
		EXECUTE 'DROP TRIGGER IF EXISTS audit_' || name || ' ON ' || tables[i];
		EXECUTE 'DROP TRIGGER IF EXISTS audit_' || name || '_delete ON ' || tables[i];
		EXECUTE 'DROP FUNCTION IF EXISTS audit_' || name || '()';
		EXECUTE 'DROP FUNCTION IF EXISTS audit_' || name || '_delete()';
		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 lkp_staff (refid) ON UPDATE CASCADE ON DELETE RESTRICT';
	EXECUTE 'GRANT INSERT ON audit_' || name || ' TO digital_recording_system ';
	EXECUTE 'GRANT USAGE,UPDATE ON audit_' || name || '_id_seq TO digital_recording_system ';

	-- 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, NEW.*);
		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 || '()';

	EXECUTE 'CREATE OR REPLACE FUNCTION audit_' || name || '_delete() RETURNS trigger AS
		$$
		BEGIN
			BEGIN
				OLD."last_modified_by" := current_setting(''digital_recording_system.staffrefid'');
				EXCEPTION WHEN OTHERS THEN
					OLD."last_modified_by" := null;
			END;

			INSERT INTO audit_' || name || ' VALUES (DEFAULT,now(),TG_OP,OLD.*);
			RETURN OLD;
		END;
		$$
		LANGUAGE ''plpgsql'' VOLATILE';

	EXECUTE 'CREATE TRIGGER audit_' || name || '_delete BEFORE DELETE ON '|| tables[i] || ' FOR EACH ROW EXECUTE PROCEDURE audit_' || name || '_delete()';

	END LOOP;
	return true;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION create_audit_tables(varchar(50)[],boolean) OWNER TO digital_recording_system;
  • pg_export_all_tables_csv(text schema,text filter,text path) - exports as CSV all tables in selected schema that match the filter in files saved in the given path.
select pg_export_all_tables_csv('public','lkp_%','/tmp');

CREATE OR REPLACE FUNCTION pg_export_all_tables_csv(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 = 'r' AND nspname = $1 AND relname LIKE $2
LOOP
EXECUTE 'COPY ' || $1 || '."' || obj.relname || '" TO ''' || $3 || '/' || obj.relname || '.csv'' CSV HEADER';
num := num + 1;
END LOOP;

RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION pg_export_all_tables_csv(text,text,text) OWNER TO postgres;
Retrieved from "https://docswiki-failover.newro.co/index.php?title=SQL_functions&oldid=4584"