1

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

5

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 
  • "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? – RonJohn Sep 1 '18 at 22:12
  • 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. – Jacky Sep 2 '18 at 9:50
  • why not use .sql file? for multiline blocks - more easy. sql -d xxx < command.sql – a_vlad Sep 2 '18 at 10:15
  • @a_vlad: yes, I agree, that's easier. – Jacky Sep 2 '18 at 10:36
  • @a_vlad because I don't like lots of tiny little one-purpose scripts floating around. – RonJohn Sep 2 '18 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.