1

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.
Rishabh Agarwal
  • 149
  • 1
  • 10
  • 3
    Why not simply use `create schema if not exists ...`? The privilege to create a schema should not be controlled in a function but by granting (or revoking) the `create` privilege on the database –  Dec 15 '22 at 11:35
  • This doesn't make sense to me: You now have to create a function when you actually want to create a schema. Why don't you just create the schema? – Frank Heikens Dec 15 '22 at 11:41
  • @a_horse_with_no_name In my use case, I can't enforce the postgres version. AFAIK `create schema if not exists` do not work well with postgres 9.1 or under. – Rishabh Agarwal Dec 15 '22 at 11:41
  • 1
    All supported Postgres versions provide that option. You shouldn't use such an outdated version anyway. –  Dec 15 '22 at 11:44
  • For the other part of your comment @a_horse_with_no_name, I do have proper privileges set up for users of the database. I just want to write a function (or something similar) that should try to create only when schema does not exists. – Rishabh Agarwal Dec 15 '22 at 11:44
  • Totally agree with you @a_horse_with_no_name . But this is something which I do not have control of. Users of my code can have any older versions of postgresql. This is why I am wary of using `create schema is not exists`! – Rishabh Agarwal Dec 15 '22 at 11:46
  • Additionaly, `create schema if not exists` would error out when an under-privileged user tries to run it while database already has that schema. This is not the desired behaviour. – Rishabh Agarwal Dec 15 '22 at 11:51

1 Answers1

1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228