How do I get Postgres DO $$ to work from the bash prompt?

Question

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

Thanks

Answer 1

Using \ to escape the dollar $

echo \$PATH
$PATH

Reference to psql doc, in order to execute multi commands, they also provide 3 ways to work around as below

1) Repeats -c option

psql -c 'SELECT now()' -c 'SELECT * FROM foo;'

2) Combines echo and psql

echo 'SELECT now() ; SELECT * FROM foo;' | psql

3) Uses psql and EOF

psql <<EOF
\x 
SELECT now(); 
SELECT * FROM foo;
EOF

Besides, considering changing rolename text to rolename RECORD to avoid ERROR: missing FROM-clause entry for table (record type)

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command

In terms of your case, please careful with DROP ROLE due to dependencies (refer: dropping role)

If DROP ROLE is attempted while dependent objects still remain, it will issue messages identifying which objects need to be reassigned or dropped.

Last but not least, here's my example

#### Using psql and EOF
psql << EOF
DO \$$
DECLARE
 v_role record; 
BEGIN
 for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
 loop
 raise notice '%', v_role.rolname;
 execute 'DROP ROLE ' || v_role.rolname; 
 end loop;
END
\$$;
EOF
#### Using echo and psql
echo "
DO \$$
DECLARE
 v_role record; 
BEGIN
 for v_role in select rolname from pg_roles where rolname not in ('postgres', 'pg_signal_backend', 'TAP')
 loop
 raise notice '%', v_role.rolname;
 execute 'DROP ROLE ' || v_role.rolname; 
 end loop;
END
\$$;
" | psql 

• RonJohn: "Thus you cannot mix SQL and psql meta-commands" Is do $$ a meta-command? Or does escaping the $ make psql think that it's a meta-command?
• Jacky: Anything you enter in psql that begins with an unquoted backslash is a psql meta-command. So, do $$ is not a psql meta-command. I will clarify my post a bit.
• a_vlad: why not use .sql file? for multiline blocks - more easy. sql -d xxx < command.sql
• Jacky: @a_vlad: yes, I agree, that's easier.
• RonJohn: @a_vlad because I don't like lots of tiny little one-purpose scripts floating around.

Answer 2

For me, the syntax mentioned in the other answers didn't work. How I was able to do it:

Run script in bash shell:

psql -h remotehost -p 5432 -U username dbname < ./scripts/sqlscript.sql

This will prompt you for the password and then run sqlscript.sql. For the script itself, I used the pattern like in the

Example sqlscript.sql:

DO
$do$
BEGIN
 delete
 from django_migrations
 where app like '%myapi%';
 RAISE NOTICE 'Deleted API related migration info';
END
$do$;

• RonJohn: Note that a .pgpass file obviates the need for a password prompt.