Update columns for the first user inserted-CodePudding

I'm trying to create a trigger and a function to update some columns (roles and is_verified) for the first user created. Here is my function :

CREATE OR REPLACE FUNCTION public.first_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE 
    begin
        if(select count(*) from public.user = 1) then
        update new set new.is_verified = true and new.roles = ["ROLE_USER", "ROLE_ADMIN"]
        end if;
    return new;
    end;
$function$
;

and my trigger :

create trigger first_user
before insert
    on
    public.user for each row execute function first_user()

I'm working on Dbeaver and Dbeaver won't persist my function because of a syntax error near the "=". Any idea ?

CodePudding user response：

Quite a few things are wrong in your trigger function. Here it is revised w/o changing your business logic.
However this will affect the second user, not the first. Probably you shall compare the count to 0. Then the condition shall be if not exists (select from public.user) then

CREATE OR REPLACE FUNCTION public.first_user()
RETURNS trigger LANGUAGE plpgsql AS
$function$
begin
   if ((select count(*) from public.user) = 1) then
   -- probably if not exists (select from public.user) then
        new.is_verified := true;
        new.roles := array['ROLE_USER', 'ROLE_ADMIN'];
   end if;
   return new;
end;
$function$;