CODEWITHSUNDEEP
postgresql
Léo Coletta
Léo Coletta

Reputation: 1269

Postgresql TRIGGER shows incorrect NEW value

I am making a trigger for a user table that to hash password before inserting or updating password.

However, for a reason I ignore, when I insert a new row, NEW.password is null for the trigger.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

DROP TABLE IF EXISTS public.user;
DROP TRIGGER IF EXISTS encrypt_user_password ON public.user;
DROP DOMAIN IF EXISTS public.email_adress;
DROP TYPE IF EXISTS public.user_role;
DROP FUNCTION IF EXISTS encrypt_password;

CREATE TYPE user_role AS ENUM ('user', 'admin');
CREATE DOMAIN public.email_adress AS TEXT CHECK (value ~* '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$');

CREATE TABLE public.user (
    id SERIAL NOT NULL PRIMARY KEY,
    email public.email_adress NOT NULL UNIQUE,
    password TEXT NOT NULL,
    salt TEXT NOT NULL DEFAULT gen_salt('bf'),
    role public.user_role NOT NULL DEFAULT 'user'
);

CREATE OR REPLACE FUNCTION encrypt_password() RETURNS TRIGGER AS
    $func$
    BEGIN
        IF NEW.password IS NULL THEN
            RAISE EXCEPTION 'Password cannot be null';
        END IF;

        NEW.salt := gen_salt('bf');
        NEW.password := crypt(NEW.password, NEW.salt);
        RETURN NEW;
    END
    $func$ LANGUAGE plpgsql;

CREATE TRIGGER encrypt_user_password BEFORE INSERT OR UPDATE ON public.user EXECUTE PROCEDURE encrypt_password();

INSERT INTO public.user (email, password) VALUES ('[email protected]', '123456789') ON CONFLICT DO NOTHING;

Can anyone see the problem ?

I am using PostgreSQL 12

Upvotes: 0

Views: 330

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

If you are going to reference the inserted or updated row from within your function, then you must add the FOR EACH ROW qualifier.

CREATE TRIGGER encrypt_user_password 
  BEFORE INSERT OR UPDATE ON public.user 
  FOR EACH ROW EXECUTE PROCEDURE encrypt_password();

Also, EXECUTE PROCEDURE is deprecated:

The use of the keyword PROCEDURE here is historical and deprecated

Upvotes: 2

Related Questions