Use variable in postgres loop

Question

I'm having trouble when using the variable arrow in my loop. Postgres ignores the declared variable and reads it as text. How can I solve that?

I have the following error: ERROR: relation "arrow" does not exist Where: SQL statement "GRANT SELECT ON arrow TO test" PL/pgSQL function inline_code_block line 8 at SQL statement

do $$
declare
    arrow record;
BEGIN
FOR arrow IN
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
LOOP
   GRANT SELECT ON arrow TO test;
END LOOP;
END;
$$ LANGUAGE plpgsql;

user330315 · Accepted Answer · 2020-07-14 05:28:18Z

2

You need dynamic SQL for this.

do $$
declare
    arrow record;
BEGIN
FOR arrow IN
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
LOOP
   execute format('GRANT SELECT ON %I TO test', arrow.table_name);
END LOOP;
END;
$$ LANGUAGE plpgsql;

But you can do that much easier without any looping or PL/pgSQL:

grant select on all tables in schema public to test;

answered Jul 14, 2020 at 5:28

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use variable in postgres loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related