Using a variable on a PostgreSQL function to drop a schema

Question

I'm trying to create a function on PostgreSQL, and I have some problem to use a local variable. Here's my code :

DECLARE query RECORD;
DECLARE schema_name TEXT;
BEGIN
        FOR query IN SELECT * FROM context WHERE created_at + make_interval(days => duration) <= CURRENT_TIMESTAMP LOOP
            SELECT lower(quote_ident(query.title)) INTO schema_name;
            DROP SCHEMA schema_name CASCADE;
            DELETE FROM context WHERE id = query.id;  
        END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

The select and delete queries work fine, and I've made a test returning the value of schema_name variable, and it's OK.

My problem is with this line :

DROP SCHEMA schema_name CASCADE;

I get an error as "the schema 'schema_name' doesn't exist". I'd really appreciate any ideas for how to use this variable to do the drop query.

Answer 1

You need dynamic SQL for this:

DECLARE 
  query RECORD;
BEGIN
  FOR query IN SELECT id, lower(title) as title 
               FROM context 
               WHERE created_at + make_interval(days => duration) <= CURRENT_TIMESTAMP 
  LOOP
    execute format('DROP SCHEMA %I CASCADE', query.title);
    DELETE FROM context WHERE id = query.id;  
   END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

---

I also removed the unnecessary SELECT statement to make the title lower case, this is better done in the query directly.

Also: variable assignment is faster with := then with select, so:

schema_name := lower(quote_ident(query.title));

would be better if the variable was needed.