What is the correct way to use arguments in a function?

Question

PostgreSQL

I want to create a function that will accept a username and role as input

CREATE OR REPLACE FUNCTION create_user(user_name varchar, role_name varchar)
RETURNS SETOF varchar AS 
$BODY$ 
BEGIN
CREATE USER user_name PASSWORD (SELECT random_string((SELECT random_int_between(5,15))));
GRANT role_name TO user_name;
RETURN QUERY SELECT * FROM all_users;
END; 
$BODY$ LANGUAGE plpgsql;

when used, it says that the role "role_name" does not exist, i.e. the parameter was not revealed - why?
and with the password, too, it’s not right here, how to write it correctly so that the password is assigned to the one that returns SELECT?

Answer 1

CREATE USER user_name PASSWORD (SELECT random_string((SELECT random_int_between(5,15))));

I do not think that such a perversion is possible. At least the documentation on CREATE ROLE explicitly allows only PASSWORD NULL or PASSWORD 'qwerty', without expressions, let alone SQL queries.
Further

Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution of PL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed from PL/pgSQL, you must build the utility command as a string and then EXECUTE it, as discussed in Section 43.5.4.

https ://www.postgresql.org/docs/current/plpgsql-st... you cannot insert a plpgsql variable into the CREATE USER command. Your command is simply interpreted as if you want to create a user named user_name. GRANT behaves similarly.
It is better to generate the password into a variable, and these commands must be executed via EXECUTE, something like this:

SELECT random_string(SELECT random_int_between(5,15)) INTO pass;
EXECUTE format('CREATE USER %I PASSWORD %L', user_name, pass);
EXECUTE format('GRANT %I TO %I', role_name, user_name);