Postgres 9.6.9 on Linux RHEL 6.10
If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the
$$, and that obviously makes the command fail. So I switched the double- and single-quotes, but now it treats the
NOT IN string literals as column names, which also throws an error.
So, as mentioned in the title, what's the magic sauce for getting a
DO $$ command to work from the command line?
$ export PGHOST=10.x.y.z $ export PGUSER=postgres $ psql -c 'do $$ > declare rolename text; > begin > for rolename in select rolname > from pg_roles > where rolname not in ("postgres", > "pg_signal_backend", > "TAP") > loop > execute "DROP ROLE " || rolename; > end loop; > end $$ > ;' ERROR: column "postgres" does not exist LINE 3: where rolname not in ("postgres",... ^ QUERY: select rolname from pg_roles where rolname not in ("postgres", "pg_signal_backend", "TAP") CONTEXT: PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows