Using replace() inside a loop

Question

I am trying to copy indexes from materialized views onto tables. This is the script I am trying to use:

DO $$ 

declare indexdefname record; 
a text;
b text;
c text;

begin for indexdefname in

select indexdef
from pg_indexes i
join pg_class c
    on schemaname = relnamespace::regnamespace::text 
    and tablename = relname
where relkind = 'm'

loop

a:= FORMAT ('do $check$ begin replace(%s, public., 
myschema. ); end $check$', indexdef.indexdefname);

execute a;

end loop;

end $$;

This is the error I am getting:

missing FROM-clause entry for table "indexdef".

All, I am trying to do is replace

CREATE INDEX test_index
ON public.test
( name )

with

CREATE INDEX test_index
ON myschema.test
( name )

Answer 1

The immediate cause for the error you report is a switcheroo:
You have indexdef.indexdefname where it should be indexdefname.indexdef.

But there is more. Don't build a dynamic nested DO statement just for the replacement. That's a simple function call which can be nested in the initial SELECT right away. Also, there are multiple syntax errors: missing quotes, function call without assignment, and the DDL statements are never executed. Just execute the CREATE INDEX commands. Like:

DO
$do$ 
DECLARE
   _ddl text;
BEGIN
   SELECT INTO _ddl
          string_agg(replace(i.indexdef, 'ON public.', 'ON myschema.'), E';\n')
   FROM   pg_indexes i
   JOIN   pg_class c ON c.relnamespace::regnamespace::text = i.schemaname
                    AND c.relname = i.tablename
   WHERE  c.relkind = 'm';

   IF _ddl IS NULL THEN
      RAISE EXCEPTION 'No indexes found!';
   ELSE
      EXECUTE _ddl;
   END IF;
END
$do$;

Careful! This uses all indexes of all materialized views. And the replacement - while looking reasonable - might go wrong for corner cases. You may want to be more selective, or go with a safer, non-dynamic two-step approach:

1. Generate DDL string with

SELECT string_agg(replace(i.indexdef, 'ON public.', 'ON myschema.'), E';\n')
FROM   pg_indexes i
JOIN   pg_class c ON c.relnamespace::regnamespace::text = i.schemaname
                 AND c.relname = i.tablename
WHERE  c.relkind = 'm';  -- more predicates?

2. Execute the string after checking it does what you want.