Postgresql – Tipps and Tricks

Using PLSQL for setting Grants:

 

CREATE OR REPLACE FUNCTION set_all_sq_grants() RETURNS SETOF TEXT AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN
SELECT sequence_name, sequence_schema FROM information_schema.sequences WHERE sequence_schema = 'xxx'
LOOP
-- can do some processing here
EXECUTE ' Grant all on ' || r.sequence_schema || '.' || r.sequence_name || ' to userXY' ;
--RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;

— und ausführen
select xxx.set_all_sq_grants();

more about Postgres PLSQL: Fun with Functions.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *