Conditionally execute create command in SQL-CodePudding

I want to create a SQL function that can perform the following task - It should check whether a schema exists and if it doesn't, the function should create one.

Here is an example of such a function -

CREATE OR REPLACE FUNCTION create_schema_if_not_exists() RETURNS void AS $body$
DECLARE schema_count INT;
BEGIN
  SELECT count(*) into schema_count FROM information_schema.schemata WHERE schema_name = 'my_schema';
  if schema_count = 0 THEN
    CREATE SCHEMA my_schema;
  END IF;
END;
$body$
language 'plpgsql';

Now there are two different kinds of users which this function can have - (1) Permitted to create schema, and (2) Not permitted to create schema. I want this function to error out only when a user, not permitted to creating schema, tries to create a schema when one does not exists.

The problem with this approach is that the user of kind (2) is not able to call function create_schema_if_not_exists.

How should I approach this problem now?

Edit

So here is what I want -

If a user of kind (2) tries to create schema when one already exists then nothing should happen.
If a user of kind (1) tries to create schema when one already exists then nothing should happen.
If a user of kind (1) tries to create schema when one doesn't already exists then the schema should be created.
If a user of kind (2) tries to create schema when one doesn't already exists then permission error should be thrown.

CodePudding user response：

In Postgres 9.3 or later simply use:

CREATE SCHEMA IF NOT EXISTS my_schema;

Besides being simpler, it also avoids race conditions in the below workaround.

To make it work without raising an exception when the schema already exists, and for outdated versions of Postgres, too:

CREATE OR REPLACE FUNCTION create_schema_if_not_exists()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_namespace WHERE nspname = 'my_schema') THEN
      CREATE SCHEMA my_schema;
   END IF;
END
$func$;

See:

You still get an exception for for missing privileges if (and only if) control actually reaches the CREATE SCHEMA statement, i.e. the schema does not exist already, as requested. Notably, that's also true for your original version. I just made it simpler and faster. Your statement in the question seems to be a misunderstanding:

The problem with this approach is that the user of kind (2) is not able to call function create_schema_if_not_exists.

A missing CREATE privilege on the database would not prevent a role from calling either function.