Create indexes for tables with prefix in PostgreSQL

Question

I'm trying to achieve a way to create multiple indexes for multiple tables in Postgresql.

For now, i wrote something like this

do $$

declare temprow record;
declare idx_name character varying(200);

begin

for temprow in
    select table_name from information_schema.tables where table_schema = 'public' and table_name like 'prefix%' order by table_name
loop
    idx_name := 'index_name_prefix_' || temprow.table_name || '_idx';
    create index idx_name ON temprow.table_name (column1 asc, column2 desc);
end loop;

end$$;

Seems like this should work, but it doesn't with an error

ERROR:  schema "temprow" does not exist

I thought i would just schedule this sql once a week, as that's a proper way for my task

Can you help me to find error in this SQL or suggest a better way to create indexes for multiple tables?

Answer 1

Where you have create index idx_name ON temprow.table_name, the table name must be a real table, not a variable with a string containing the table name, or any other indirect way of dynamically/indirectly referencing a table's name.

• Where you have temprow.table_name the syntax expects schema.table
• So, you're telling PostgreSQL to create the index on the table table_name in the schema temprow

What you're looking for is Dynamic SQL; that is code that writes SQL as a string, and then separately executing that string.

DO $do$

DECLARE 
  temprow  RECORD;
  sql_stmt CHARACTER VARYING(1024);
BEGIN

  FOR temprow IN
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name like 'prefix%' ORDER BY table_name
  LOOP
    sql_stmt := FORMAT(
                  'create index index_name_prefix_%s_idx ON %s (column1 asc, column2 desc);',
                  temprow.table_name,
                  temprow.table_name
                );

    EXECUTE(sql_stmt);

  END LOOP;

END $do$;

Working example:

• https://dbfiddle.uk/?rdbms=postgres_13&fiddle=b50edb9de4db420b20e09e8221e4ce4a

Do be careful of using this pattern though.

• It begins to open you up to SQL Injection attacks.

Check out what happens if someone tries to hack you by abusing quoted table names...

• CREATE TABLE "prefix_z ON test(id); drop table prefix_test; --" (id INT);
• https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4a9abcf2ea8fa87148927b033bde1bd3