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.

Unrelated, but: you only need one DECLARE block for all variables. Not one block for each variable. — user330315
– user330315, Commented Apr 24, 2020 at 14:37

score 2 · Accepted Answer · 2020-04-24 14:42:12Z

2

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.

edited Apr 24, 2020 at 14:42

answered Apr 24, 2020 at 14:35

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a variable on a PostgreSQL function to drop a schema

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related