It's working now...
Final code:
ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000;
COMMIT TRANSACTION;
BEGIN;
-- Creating the function
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
-- if it's an insert, then we update the client_code_increment column value to +1
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.company_id;
END IF;
-- IF the customer didn't provide a code value, we insert the next available from companies.client_code_increment
IF NEW.code IS NULL THEN
SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.company_id ORDER BY client_code_increment DESC;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Creating the trigger
CREATE TRIGGER tf_users_code_seq
BEFORE INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE users_code_seq();
COMMIT TRANSACTION;
Thanks.
Lucas
