I need to drop some sequences from my db, there are a number of such sequences, so I want to write a SQL statement to drop them all at once, so I did:
do
$$
declare rec RECORD;
begin
FOR rec in
select relname from pg_class where relkind = 'S' and relname like 'XXX_%_seq'
loop
drop sequence rec.relname; # HERE is the problem, I cannot directly use rec.relname here
end loop;
end;
$$
How can I use rec.relname with drop sequence?
>Solution :
To drop sequences dynamically using the rec.relname in a PL/pgSQL block, you can’t directly use DROP SEQUENCE with a record attribute. Instead, you’ll need to use dynamic SQL with the EXECUTE statement. Here’s how you can modify your code to do this:
DO
$$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT relname FROM pg_class WHERE relkind = 'S' AND relname LIKE 'XXX_%_seq'
LOOP
EXECUTE 'DROP SEQUENCE ' || quote_ident(rec.relname);
END LOOP;
END;
$$
The quote_ident function is used to safely quote identifiers if needed, for example, if your sequence names contain special characters or reserved words.