I have several tables with the exact same structure and I need to update a value in all tables.
In order to do so I tried to build the following script:
DO $do$ DECLARE i pg_tables%rowtype; BEGIN FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations' LOOP UPDATE i.tablename SET name = replace(name, '.js', '.ts'); END LOOP; END $do$;
I could see that
i.tablename has the correct value (I inserted in a tmp table to check), but the update fails.
name: error length: 223 severity: ERROR code: 42P01 internalPosition: 8 internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts') where: PL/pgSQL function inline_code_block line 7 at SQL statement file: parse_relation.c line: 965 routine: parserOpenTable
i.tablename on the
UPDATE statement doesn't work.
Is there a way to make it work? Or an easier way to update all tables at once?