How to add "created_by" and "updated_by" columns to tables

[mbalslow]

It might not be clearly stated anywhere, but you can actually use the "auth.uid()" in your default values on columns and in DB functions similarly to how you would use them in RLS policies.

With this, you can quite easily setup logic to make "created_by" and "updated_by" columns:

[chris-lsn]

Update: The default value should be set to auth.uid() instead of uid()

[MathiasSchjoedt-Bavngaard]

how do you diffirentiat between created_by and updated by

[quebrandolink]

-- cria uma função de atualizar os campos (createdBy, updatedBy, createdAt, updatedAt)
CREATE OR REPLACE FUNCTION on_created()
RETURNS TRIGGER AS $$
BEGIN
  NEW."updatedBy" := auth.uid();
  NEW."createdBy" := auth.uid();
  NEW."createdAt" := NOW();
  NEW."updatedAt" := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- cria uma função de atualizar os campos (updatedBy, updatedAt)
create or replace function on_updated()
returns trigger as $$
  begin
    NEW."updatedBy" := auth.uid();
    NEW."updatedAt" := NOW();
    return new;
  end;
  $$ language plpgsql;

-- before update ->  only updatedBy
create trigger handle_updated_persons before update on persons
  for each row execute procedure on_updated ();

-- before insert -> alter (createdBy, updatedBy)
create trigger handle_insert_persons before insert on persons
  for each row execute procedure on_created();

A diferença está em before insert e before update