Yeah, my bad. I ran that casually, which was wrong. Thanks for correcting it.
postgres=# \dn
List of schemas
Name | Owner
-----------+----------
my Schema | postgres
public | postgres
(2 rows)
-- the problem with my original dynamic sql
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = 'GRANT USAGE ON SCHEMA ' || sch || ' TO postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE: GRANT USAGE ON SCHEMA public TO postgres
NOTICE: GRANT USAGE ON SCHEMA my Schema TO postgres
ERROR: syntax error at or near "Schema"
LINE 1: GRANT USAGE ON SCHEMA my Schema TO postgres
^
QUERY: GRANT USAGE ON SCHEMA my Schema TO postgres
CONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE
-- the solution
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = 'GRANT USAGE ON SCHEMA ' || quote_ident(sch) || ' TO postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE: GRANT USAGE ON SCHEMA public TO postgres
NOTICE: GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO
/*
-- OR using format
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = format('GRANT USAGE ON SCHEMA %I TO postgres', sch);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE: GRANT USAGE ON SCHEMA public TO postgres
NOTICE: GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO
*/