I have to build two functions to use in the creation of roles and in assigning a role to a group according to its category in postgresql.
Here's the code: The first function is to create a user and call my second function that will assign that user to a group according to their category.
CREATE OR REPLACE FUNCTION sp_addlogin(
IN _usuario character varying(30),
IN _contraseña character varying(4),
IN _categoria integer)
RETURNS text AS
$BODY$
declare mensaje text;
BEGIN
IF(select count(rolname) FROM pg_roles where rolname = _usuario) = 0 THEN
EXECUTE FORMAT ('CREATE USER %I', _usuario, 'LOGIN PASSWORD %I', _contraseña);
PERFORM sp_adduser(_usuario, _categoria);
mensaje = 'Usuario de BD agregado con exito';
ELSE
RAISE 'Ya existe un rol con el mismo nombre, especifique';
END IF;
return mensaje;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION sp_adduser(
IN _usuario character varying(30),
IN _categoria integer)
RETURNS text AS
$BODY$
declare
mensaje text;
BEGIN
IF(SELECT count(rolname) FROM pg_roles WHERE rolname = _usuario) = 1 THEN
IF(_categoria = 1 or _categoria = 2)THEN
EXECUTE FORMAT ('GRANT', gestores, 'TO', _usuario);
mensaje = 'Usuario añadido al grupo gestores';
END IF;
IF(_categoria = 3)THEN
EXECUTE FORMAT ('GRANT', administradores, 'TO', _usuarrio);
mensaje = 'Usuario añadido al grupo administradores';
END IF;
ELSE
RAISE 'El usuario indicado no existe, reintente';
END IF;
return mensaje;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
When executing the first function, it generates this error:
ERROR: column "gestores" does not exist
LINE 1: SELECT FORMAT ('GRANT', gestores, 'TO', _usuario)
^
QUERY: SELECT FORMAT ('GRANT', gestores, 'TO', _usuario)
CONTEXT: PL/pgSQL function sp_adduser(character varying,integer) line 7 at EXECUTE statement
SQL statement "SELECT sp_adduser(_usuario, _categoria)"
PL/pgSQL function sp_addlogin(character varying,character varying,integer) line 6 at PERFORM
********** Error **********
ERROR: column "gestores" does not exist
SQL state: 42703
Context: PL/pgSQL function sp_adduser(character varying,integer) line 7 at EXECUTE statement
SQL statement "SELECT sp_adduser(_usuario, _categoria)"
PL/pgSQL function sp_addlogin(character varying,character varying,integer) line 6 at PERFORM
I've been looking for a solution for a couple of hours, but frankly I do not know what I'm doing wrong. I am a beginner in SQL and postgres, I hope you can help me. Greetings.