CODEWITHSUNDEEP
databasepostgresqlpsql
iusting
iusting

Reputation: 8383

Add user to database in postgresql

I am currently trying to add a user to the database when a new comercial register is inserted to the comercial table.

CREATE OR REPLACE FUNCTION anade_usuario() RETURNS TRIGGER AS $$
DECLARE
nom TEXT;
BEGIN
    /* SELECT NEW.dni INTO nom;  */
    nom := NEW.dni;
    CREATE USER nom WITH PASSWORD 'pass';
    GRANT ALL PRIVILEGES ON DATABASE "fira" TO nom;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';


\echo Creating trigger number 3.
\echo --------------------------
CREATE TRIGGER trigger3 AFTER INSERT ON comercial
FOR EACH ROW EXECUTE PROCEDURE anade_usuario();

But the script seems to add the user 'nom' instead of the user with the id NEW.dni and so at the second insert I receive an error. What should I change?

Upvotes: 0

Views: 172

Answers (1)

Wolph
Wolph

Reputation: 80111

You need to execute it as a dynamic command: http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Something along the lines of this:

EXECUTE 'CREATE USER "' || nom || '" WITH ... '
INTO user
USING checked_user, checked_date;

Upvotes: 1

Related Questions