Só atualizando a function tf_users_update_code_column..
Incluido o campo *AND NEW.code IS NULL , *para que o usuário possa incluir
o valor que quiser, e caso ele não inclua, a funcao insere os dados
necessário.
- Tinha esquecido dessa parte antes, por isto decidi postar todo o código
aqui novamente, para que fique melhor de entender.
IF NEW.company_id = 1 AND NEW.code IS NULL THEN
> NEW.code = NEXTVAL('c1_users_code_seq');
CODE:
1 - creating the trigger FUNCTION
CREATE OR REPLACE FUNCTION tf_users_update_code_column()
RETURNS trigger AS $$
BEGIN
IF NEW.company_id = 1 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c1_users_code_seq');
ELSEIF NEW.company_id = 2 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c2_users_code_seq');
ELSEIF NEW.company_id = 3 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c3_users_code_seq');
ELSEIF NEW.company_id = 4 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c4_users_code_seq');
ELSEIF NEW.company_id = 5 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c5_users_code_seq');
ELSEIF NEW.company_id = 6 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c6_users_code_seq');
ELSEIF NEW.company_id = 7 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c7_users_code_seq');
ELSEIF NEW.company_id = 8 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c8_users_code_seq');
ELSEIF NEW.company_id = 9 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c9_users_code_seq');
ELSEIF NEW.company_id = 10 AND NEW.code IS NULL THEN
NEW.code = NEXTVAL('c10_users_code_seq');
END IF;
return NEW;
END
$$ LANGUAGE plpgsql;
2 - Creating the sequences....
CREATE SEQUENCE c1_users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;
CREATE SEQUENCE c2_users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;
CREATE SEQUENCE c3_users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;
CREATE SEQUENCE c4_users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;
> ... [etc] ...
3 - Creating the TRIGGER
CREATE TRIGGER t_users_update_code_column
BEFORE INSERT
ON users
FOR EACH ROW
EXECUTE PROCEDURE tf_users_update_code_column();
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
