How to check current database inside pl\pgsql script?-CodePudding

It is possible to allow pl\pgsql script run for only specific database?

Example, I have the script, that deleting unnecessary columns in all database tables and I want to be sure that this script can only be run in a database named 'test':

DO
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE  data_type not like 'timestamp with time zone' 
        AND    data_type not like 'uuid'
        AND    data_type not like'date'
        AND    table_schema like 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I DROP COLUMN %I;',
            rec.table_schema, rec.table_name, rec.column_name);
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CodePudding user response：

The function current_database() will return that information. You could throw an exception to prevent further actions, if the current database isn't named test

if current_database() <> 'test' then
  raise exception 'This script should only run in the database "test"';
end if;