0

I am trying the change ownership of tables owned by postgres user to another role based on an answer in this thread.

concrete

do
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname in ('public')
                 and tableowner = 'postgres'
  loop
    l_sql := format('alter table %I.%I owner to new_role', l_rec.schemaname, l_rec.tablename);
    raise notice 'Running %: ', l_sql;
    execute l_sql;
  end loop;
end;

running this however results into nothing more then changing the prompt from [db_name]=# to [db_name]$#. I could not figure out yet, wat the $ stands for though. It's not PROMPT2 (which is -#).

I do not understand the code above in detail but would suspect the %I.%I in the alter table section maybe not resolving to to the desired content, or maybe the varialbe declaration not doing what it should.

Can anyone point me towards a solution for this?

3
  • or is it that you have to close the begin with a commit rather then end?
    – vrms
    Dec 7, 2022 at 16:26
  • 1
    You started the block with $$ but forgot to end it.
    – mustaccio
    Dec 7, 2022 at 17:25
  • I do not understand the code above in detail -- this is not a very good position to be in, when you run code copied from the internets in your database.
    – mustaccio
    Dec 7, 2022 at 17:32

1 Answer 1

0

based on the comment of @mustaccio 's above this is how it works

do
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname in ('public')
                 and tableowner = 'postgres'
  loop
    l_sql := format('alter table %I.%I owner to new_role', l_rec.schemaname, l_rec.tablename);
    raise notice 'Running %: ', l_sql;
    execute l_sql;
  end loop;
end
$$
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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