Acabei enviando o e-mail errado e faltou informacões...
*Vamos lá novamente:*
Possuo duas tabelas:
*- users*
- code (Coluna)
*- companies*
- client_code_increment (Coluna)
A coluna users.code, recebe um código pelo cliente. Mas ele pode escolher
em não por nenhum dado lá.
Caso o cliente escolhe por não inserir nada na coluna, eu tenho que dar
para ele um número... um value....
O default é 1000
Por que adicionar um valor DEFAULT a users.code não é uma solucão?
>
Porque o cliente pode escolher em por algum outro dado nesta coluna. Então
um valor DEFAULT não é a saída
Qual é o default code?
>
O default é 1000.
O cliente pode por qualquer coisa no users.code, porém se lá estiver NULL,
se o cliente não puser nada, nós irá setar a próxima value... 1001, 1002,
1003 etc
>
> O que é increment_client_code?
>
É uma coluna onde armazena o último client_code usado:
ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT
1000;
*table users:*
> CREATE TABLE
> users
> (
> id INTEGER DEFAULT nextval('users_id_seq'::regclass) NOT NULL,
> email CHARACTER VARYING DEFAULT ''::CHARACTER VARYING NOT NULL,
> encrypted_password CHARACTER VARYING DEFAULT ''::CHARACTER VARYING
> NOT NULL,
> reset_password_token CHARACTER VARYING,
> reset_password_sent_at TIMESTAMP(6) WITHOUT TIME ZONE,
> remember_created_at TIMESTAMP(6) WITHOUT TIME ZONE,
> sign_in_count INTEGER DEFAULT 0 NOT NULL,
> current_sign_in_at TIMESTAMP(6) WITHOUT TIME ZONE,
> last_sign_in_at TIMESTAMP(6) WITHOUT TIME ZONE,
> current_sign_in_ip INET,
> last_sign_in_ip INET,
> created_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
> updated_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
> name CHARACTER VARYING,
> confirmation_token CHARACTER VARYING,
> confirmed_at TIMESTAMP(6) WITHOUT TIME ZONE,
> confirmation_sent_at TIMESTAMP(6) WITHOUT TIME ZONE,
> company_name CHARACTER VARYING,
> country CHARACTER VARYING,
> mobile_number CHARACTER VARYING,
> landline_number CHARACTER VARYING,
> staff_colour CHARACTER VARYING,
> company_id INTEGER,
> role_id INTEGER,
> active BOOLEAN DEFAULT false,
> deleted BOOLEAN DEFAULT false,
> avatar_file_name CHARACTER VARYING,
> avatar_content_type CHARACTER VARYING,
> avatar_file_size INTEGER,
> avatar_updated_at TIMESTAMP(6) WITHOUT TIME ZONE,
> fax CHARACTER VARYING,
> website CHARACTER VARYING,
> business_type CHARACTER VARYING,
> lead_source CHARACTER VARYING,
> code CHARACTER VARYING,
> notes TEXT,
> status CHARACTER VARYING,
> tsv TSVECTOR,
> origin CHARACTER VARYING,
> origin_id CHARACTER VARYING,
> first_name CHARACTER VARYING,
> last_name CHARACTER VARYING,
> billed_client_id INTEGER,
> username CHARACTER VARYING,
> is_client BOOLEAN DEFAULT false,
> job_share BOOLEAN DEFAULT true
> );
*Table companies:*
> CREATE TABLE
> companies
> (
> id INTEGER DEFAULT nextval('companies_id_seq'::regclass) NOT NULL,
> name CHARACTER VARYING,
> country CHARACTER VARYING,
> timezone CHARACTER VARYING,
> mobile_number CHARACTER VARYING,
> email CHARACTER VARYING,
> website CHARACTER VARYING,
> phone CHARACTER VARYING,
> created_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
> updated_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
> reference_increment INTEGER DEFAULT 1000,
> activated BOOLEAN DEFAULT true,
> enable_quotes BOOLEAN DEFAULT false,
> allow_billing_client BOOLEAN DEFAULT true,
> allow_templates_recurrence BOOLEAN DEFAULT true,
> recurrence_limit INTEGER DEFAULT 30,
> job_title_dropdown BOOLEAN DEFAULT false,
> default_reference_prefix CHARACTER VARYING,
> default_reference_increment INTEGER,
> default_visit_start_day INTEGER,
> default_visit_start_hour INTEGER,
> default_visit_start_min INTEGER,
> job_date_entry_duration BOOLEAN DEFAULT true,
> default_visit_duration_hour INTEGER DEFAULT 0,
> default_visit_duration_min INTEGER DEFAULT 30,
> date_entry_short BOOLEAN DEFAULT true,
> time_entry_24 BOOLEAN DEFAULT true,
> time_field_increment INTEGER DEFAULT 10,
> enable_job_share BOOLEAN DEFAULT true,
> token CHARACTER VARYING
> );
Como posso fazer isto acontecer? Como posso criar a funcao e o trigger para
tal?
Aqui está o que fiz até agora: (Mas não consigo seguir)
DROP FUNCTION IF EXISTS client_code_increment_count();
> CREATE OR REPLACE FUNCTION "public"."client_code_increment_count" ()
> RETURNS TABLE("code" INT) AS
> $BODY$
> SELECT MAX(CAST(users.code AS INT)) FROM users WHERE users.code ~ '^\d+$'
> AND company_id = 2
> $BODY$
> LANGUAGE sql;
> SELECT * FROM "client_code_increment_count"();
> CREATE OR REPLACE FUNCTION "public"."auto_generate_client_code_if_empty"
> () RETURNS "trigger"
> VOLATILE
> AS $dbvis$
> BEGIN
> END;
> $dbvis$ LANGUAGE plpgsql;
>
> CREATE TRIGGER "increment_client_code"
> BEFORE INSERT OR UPDATE ON users
> FOR EACH ROW
> EXECUTE PROCEDURE "auto_generate_client_code_if_empty"();
Obrigado. Lucas
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
