Postgresql – How to get Postgres DO $$ to work from the bash prompt

postgresqlpostgresql-9.6scripting

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

Best Answer

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
Related Question