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?

MatBailie · Accepted Answer · 2021-02-07 15:16:21Z

3

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

edited Feb 7, 2021 at 15:16

user330315

answered Feb 7, 2021 at 12:25

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

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

1 Comment

user330315 Over a year ago

If you use %I instead of %s in format() then this is no longer open to SQL injection: dbfiddle.uk/…

Collectives™ on Stack Overflow

Create indexes for tables with prefix in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related