I have to change cache for all sequences in schema and database. I can not change one by one. Is there some script or query which can change cache size for all sequences?
closed as unclear what you're asking by mustaccio, kevinsky, hot2use, Marcello Miorelli, Vérace Jun 2 at 12:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
You can use a
DO statement so that you can run a PL/pgSQL block in a single SQL statement:
DO $$DECLARE v_schema text; v_sequence text; BEGIN FOR v_schema, v_sequence IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = 'myschema' LOOP EXECUTE format('ALTER SEQUENCE %I.%I CACHE 20', v_schema, v_sequence); END LOOP; END;$$;
A simple approach which is often adequate is to generate all the statements in a query and then just copy and paste them to run them. This way you can customize the statements for each occasion. The queries are simple enough that you don't even need to save the code for future use.
SELECT 'alter sequence ' || sequence_schema || '.' || sequence_name || ' cache 20;' FROM information_schema.sequences;
It should be obvious you can use this "trick" for other object types too.