Update columns for the first user inserted

Question

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 ?

This needs a [scalar subquery](https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES). `if ((select count(*) from public.user) = 1) then`. And `new.is_verified := true; new.roles := array['ROLE_USER', 'ROLE_ADMIN']` too. No`update` is needed, just assignments. — Stefanov.sm, Dec 03 '22 at 15:55
This seems to be a duplicate of [Store query result in a variable using in PL/pgSQL](https://stackoverflow.com/questions/12328198/store-query-result-in-a-variable-using-in-pl-pgsql) — Luuk, Dec 03 '22 at 16:09

Stefanov.sm · Accepted Answer · 2022-12-03T16:08:15.637

0

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$;

edited Dec 03 '22 at 16:08

answered Dec 03 '22 at 16:03

Stefanov.sm

11,215
2
21
21

I try with if not exists, Dbeaver is ok to persist but in fact when I insert the first user nothing change – Collembole Dec 03 '22 at 18:30
Was the table empty? Did you run `select * from public.user` to see what is there in it? – Stefanov.sm Dec 03 '22 at 19:47
My bad it's ok. I change new.roles because it's a json not an array. – Collembole Dec 04 '22 at 15:20

Update columns for the first user inserted

1 Answers1