--
-- This code is being placed in the public domain - R.Huxton 2008
--

CREATE OR REPLACE FUNCTION util.exec_all(objtype name, schname name, objname name, cmd text) RETURNS text
    AS $$
DECLARE
	obj_qry TEXT := '';
    r   RECORD;
    sql TEXT;
    out TEXT;
BEGIN
    out := cmd || ': ';

    -- Tables includes views
    IF objtype = 'tables' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_schema=$a$ || quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname);
    ELSIF objtype = 'tables-base' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema=$a$ || quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname);
    ELSIF objtype = 'views' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='VIEW' AND table_schema=$a$ || quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname);
    ELSIF objtype = 'sequences' THEN
		obj_qry := $a$SELECT sequence_name AS nm FROM information_schema.sequences WHERE sequence_schema=$a$ || quote_literal(schname) || $a$ AND sequence_name LIKE $a$ || quote_literal(objname);
    END IF;

	FOR r IN EXECUTE obj_qry LOOP
		sql := regexp_replace( cmd, E'\\?\\?', quote_ident(r.nm), 'g' );
		sql := regexp_replace( sql, E'\\?', quote_ident(schname) || '.' || quote_ident(r.nm), 'g' );
		EXECUTE sql;
		out := out || r.nm || ' ';
	END LOOP;
	
    RETURN out;
END;
$$
LANGUAGE plpgsql;

COMMENT ON FUNCTION util.exec_all(name,name,name,text) IS $$
util.exec_all(OBJECT-TYPE, SCHEMA, OBJECT-NAME, COMMAND-TEXT)

Runs COMMAND-TEXT over the database objects matched by the wildcarded pattern in OBJECT-NAME.

OBJECT-TYPE:  tables | tables-base | views | sequences
SCHEMA:       name of a single schema
OBJECT-NAME:  wildcarded name (e.g. 'sales_%')
COMMAND-TEXT: SQL statement(s) to execute, with object-names replaced by '?' or '??'
              ?? is replaced by the object-name
			  ?  is replaced by the full <schema-name>.<object-name>

EXAMPLE
=======
util.exec_all('tables', 'reports', '%', 'GRANT SELECT ON ? TO someuser');
$$;

