Conditionally execute create command in SQL

Question

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.

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 — user330315
– user330315, Commented Dec 15, 2022 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
– Frank Heikens, Commented Dec 15, 2022 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
– Rishabh Agarwal, Commented Dec 15, 2022 at 11:41
All supported Postgres versions provide that option. You shouldn't use such an outdated version anyway. — user330315
– user330315, Commented Dec 15, 2022 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
– Rishabh Agarwal, Commented Dec 15, 2022 at 11:44

Erwin Brandstetter · Accepted Answer · 2022-12-15 13:43:01Z

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.

edited Dec 15, 2022 at 13:43

answered Dec 15, 2022 at 12:22

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Rishabh Agarwal Over a year ago

Thanks for your answer @Erwin. Although, I still see the permission issue. Would you mind explaining why your solution should work?

Erwin Brandstetter Over a year ago

@Rishabh: Actually, yours should, too. I added a bit above.

Rishabh Agarwal Over a year ago

Ahhh... Is it so? It seems so. Certainly a misunderstanding on my part. Thanks for raising it Erwin!

Collectives™ on Stack Overflow

Conditionally execute create command in SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related