Só para deixar registrado.. consegui fazer funcionar, segue código para futuras pesquisas:
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;
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
