And the increment_client_code in company should auto increment for the next client code
What I've done so far:
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"();
The need to do "WHERE users.code ~ '^\d+$' means your model is poorly specified.
CREATE OR REPLACE FUNCTION "public"."auto_generate_client_code_if_empty" () RETURNS "trigger" VOLATILE AS $dbvis$ BEGIN END; $dbvis$ LANGUAGE plpgsql;
It would be nice if you actually showed some work here...
CREATE TRIGGER "increment_client_code" BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE "auto_generate_client_code_if_empty"();
I'd question the need to execute this trigger on UPDATE...
But still can't do that works.. What Am I missing?
The stuff that goes between "BEGIN" and "END" in auto_generate_client_code_if_empty...?