Sorry, didn't get to see the last letter! On 23.12.2022 11:51, Michael Paquier wrote:
FWIW, I find the use of a FOR loop with a DO block much cleaner to follow in this context, so something like the attached would be able to group the two queries and address your point on O(N^2). Do you like that? -- Michael
The query: DO $$ DECLARE rec record; BEGIN FOR rec in SELECT oid::regclass::text as rel, attname as col FROM pg_class c, pg_attribute a WHERE c.relname !~ '^pg_' AND c.relkind IN ('r') AND a.attrelid = c.oid AND a.atttypid = 'aclitem'::regtype ORDER BY 1 LOOP EXECUTE 'ALTER TABLE ' || quote_ident(rec.rel) || ' ALTER COLUMN ' || quote_ident(rec.col) || ' SET DATA TYPE text'; END LOOP; END; $$; gives the average time of 36 ms at the same conditions. With the best wishes! -- Anton A. Melnikov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company