Postgres function with nested IF

Question

I have a function that I'm trying to get to create a user, insert the user into a table and IF provided add the user to a role. I can create the user and add them to the table I've created with the first function easy enough but I can't conditionally add them to a group. I want the option to NOT add the user to a role or to include the role and grant that role to the user. Here is my function to create the user that works

CREATE OR REPLACE FUNCTION create_user(
    new_user character varying,
    temp_password character varying,
    grant_role text default NULL)
  RETURNS text AS
$BODY$
BEGIN
   IF NOT EXISTS (SELECT usename FROM pg_catalog.pg_user
        Where  usename not in ('postgres','repl','pgpool')
        and usename = new_user) THEN
        EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
        EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''',
        date(now()),
        date(now() + interval ''1 days''));');
        RETURN 'CREATED ROLE';
   ELSE
        RETURN format('ROLE ''%I'' ALREADY EXISTS', new_user);
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION create_user(character varying, character varying)
  OWNER TO postgres;

I want to do something like this pseudo code

    BEGIN
               IF NOT EXISTS (SELECT usename FROM pg_catalog.pg_user
                    Where  usename not in ('postgres','repl','pgpool')
                    and usename = new_user) 
           && IF NOT EXISTS (SELECT rolname FROM pg_roles WHERE rolname = grant_role and rolname <> 'postgres') 
           && grant_role IS NOT NULL THEN
               EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
               EXECUTE format('GRANT ' || grant_role || ' to ' || new_user ||';');
               EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()), date(now() + interval ''1 days''));');
   RETURN 'CREATED USER WITH ROLE';
               ELSE 
           IF NOT EXISTS (SELECT usename FROM pg_catalog.pg_user
        Where  usename not in ('postgres','repl','pgpool')
        and usename = new_user)
                EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
                EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()),date(now() + interval ''1 days''));');
    RETURN 'CREATED USER ONLY';
                  ELSE
    RETURN 'NO USER CREATED';  
            END IF;
    END IF;
        END;

Any help is much appreciated.

Kamil Gosciminski · Accepted Answer · 2016-09-30 17:13:03Z

You are missing conditions for your nested ELSEIF. Fill it where I've added the comment line and it should work as expected with nested

IF ... THEN ... [ELSEIF ...] [ELSE ...] END IF;

Code:

BEGIN
 IF NOT EXISTS (
  SELECT usename 
  FROM pg_catalog.pg_user
  WHERE usename not in ('postgres','repl','pgpool')
    AND usename = new_user
  ) 
  THEN
    IF NOT EXISTS (
      SELECT rolname 
      FROM pg_roles 
      WHERE rolname = grant_role
        AND rolname <> 'postgres'
    ) AND grant_role IS NOT NULL 
    THEN
      EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
      EXECUTE format('GRANT ' || grant_role || ' to ' || new_user ||';');
      EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()), date(now() + interval ''1 days''));');
      RETURN 'CREATED USER WITH ROLE';
    ELSEIF -- you forgot to specify the condition
      EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
      EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()),date(now() + interval ''1 days''));');
      RETURN 'CREATED USER ONLY';
    ELSE
      RETURN 'NO USER CREATED';  
    END IF;
  ELSE
    RETURN format('ROLE ''%I'' ALREADY EXISTS', new_user);
  END IF;
END;

Pistol Pete · Accepted Answer · 2016-09-30 23:15:14Z

I ended up modifying the answer from @Kamil G. to the following function. His answer got me to where I needed to be.

CREATE OR REPLACE FUNCTION create_user(
    new_user character varying,
    temp_password character varying,
    grant_role text default NULL)
  RETURNS text AS
$BODY$
BEGIN
    IF NOT EXISTS (
        SELECT usename
        FROM pg_catalog.pg_user
        WHERE usename not in ('postgres','repl','pgpool')
        AND usename = new_user
        ) AND grant_role IS NOT NULL
    THEN
        IF EXISTS (
            SELECT rolname
            FROM pg_roles
            WHERE rolname = grant_role
            AND rolname <> 'postgres'
        )
        THEN
            EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
            EXECUTE format('GRANT ' || grant_role || ' to ' || new_user ||';');
            EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()), date(now() + interval ''1 days''));');
            RETURN 'CREATED USER WITH ROLE';
        ELSE
            RETURN 'NO USER CREATED';
        END IF;
    ELSEIF NOT EXISTS (
            SELECT rolname
            FROM pg_roles
            WHERE rolname = grant_role
            AND rolname <> 'postgres'
        ) AND grant_role IS NULL
    THEN
        EXECUTE format('CREATE USER ' || new_user || ' with password ''' || temp_password || ''';');
        EXECUTE format('insert into open_sesame (user_name, last_change_date, next_change_date) VALUES( '''  || new_user || ''', date(now()),date(now() + interval ''1 days''));');
        RETURN 'CREATED USER ONLY';
    ELSE
        RETURN 'NO USER CREATED';
    END IF;
END;

Collectives™ on Stack Overflow

Postgres function with nested IF

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related