Thread: Function PostgreSQL 9.2
Hi all,
I've got two tables:
- users
- companies
I'm trying to create a function that:
- if users.code is empty, it gives a default value
- 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"();
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"();
But still can't do that works.. What Am I missing?
Cheers
Hi all,I've got two tables:- users- companiesI'm trying to create a function that:
- if users.code is empty, it gives a default value
- 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...?
David J.
On 20 April 2016 at 10:38, David G. Johnston <david.g.johnston@gmail.com> wrote:
Hi all,I've got two tables:- users- companiesI'm trying to create a function that:
- if users.code is empty, it gives a default value
- 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...?
That's all I got David.. working on it and would like some help if possible...
Lucas
On 04/19/2016 03:23 PM, drum.lucas@gmail.com wrote:
> Hi all,
>
> I've got two tables:
>
> - users
> - companies
>
> I'm trying to create a function that:
>
> * if users.code is empty, it gives a default value
> * 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"();
>
>
>
>
>
> 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"();
>
>
>
> But still can't do that works.. What Am I missing?
Information. eg.:
The schema for the tables.
Why is not just adding a DEFAULT value to the users.code not an option?
What the default code should be or how it is to be calculated?
What is increment_client_code?
Does increment_client_code relate to users or some other table, say clients?
>
> Cheers
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Information. eg.:
The schema for the tables.
Why is not just adding a DEFAULT value to the users.code not an option?
The customer can add their own value to the users.code column.
That's why I can't have a default value.
What the default code should be or how it is to be calculated?
the default value is 1000.
So the customer can set their own code value. But if they don't do that, I've to provide the next available value. 1001, 1002, 1003, etc....
What is increment_client_code?
It's a column:
ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000;
Does increment_client_code relate to users or some other table, say clients?
nope.. there is no link between them
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
);
Just forgot to say:
[...]
Does increment_client_code relate to users or some other table, say clients?nope.. there is no link between them
If the users.code is empty/null, then the trigger has to get the last number from client_code_increment and put on the users.code column
[...]
This is such a poorly designed, hacked together “thing” – it isn’t a database, it’s someone’s idea of how to store data when they don’t know how to store data, like they moved it from Access or Excel.
Just start over and design a proper relational schema with best practices and you’ll save, oh, perhaps 10 years of wasted effort and 12 million emails. This is as close to bandaids on bandaids on steroids that it comes. Really – rethink your solution model.
Mike
From: drum.lucas@gmail.com Sent: Tuesday, April 19, 2016 7:40 PM
Just forgot to say:
Does increment_client_code relate to users or some other table, say clients?nope.. there is no link between them
If the users.code is empty/null, then the trigger has to get the last number from client_code_increment and put on the users.code column
On 04/19/2016 07:34 PM, drum.lucas@gmail.com wrote:
> Information. eg.:
>
>
> The schema for the tables.
>
> Why is not just adding a DEFAULT value to the users.code not an option?
>
>
>
> The customer can add their own value to the users.code column.
> That's why I can't have a default value.
That is contradicted by your next statement below.
>
> What the default code should be or how it is to be calculated?
>
>
> the default value is 1000.
See above.
>
> So the customer can set their own code value. But if they don't do that,
> I've to provide the next available value. 1001, 1002, 1003, etc....
Then why is users.code a varchar field?
>
>
> What is increment_client_code?
>
>
> It's a column:
> ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
> ALTER TABLE public.companies ALTER COLUMN client_code_increment SET
> DEFAULT 1000;
>
>
> Does increment_client_code relate to users or some other table, say
> clients?
>
>
> nope.. there is no link between them
Then what is its purpose?
I am with the other responses in this thread, this is a set up that is
not going to end well. More to the point, I still have no idea what you
are trying to achieve with your triggers and functions.
>
>
>
> 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
> );
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 21 April 2016 at 09:44, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/19/2016 07:34 PM, drum.lucas@gmail.com wrote:Information. eg.:
The schema for the tables.
Why is not just adding a DEFAULT value to the users.code not an option?
The customer can add their own value to the users.code column.
That's why I can't have a default value.
That is contradicted by your next statement below.
What the default code should be or how it is to be calculated?
the default value is 1000.
See above.
So the customer can set their own code value. But if they don't do that,
I've to provide the next available value. 1001, 1002, 1003, etc....
Then why is users.code a varchar field?
What is increment_client_code?
It's a column:
ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET
DEFAULT 1000;
Does increment_client_code relate to users or some other table, say
clients?
nope.. there is no link between them
Then what is its purpose?
I am with the other responses in this thread, this is a set up that is not going to end well. More to the point, I still have no idea what you are trying to achieve with your triggers and functions.
Well.. will try ONE more time then.
1 - The customer can add any value into users.code column
2 - The customer can chose between add or not add the value on users.code column
3 - If users.code is null (because the customer's chosen not to add any value in there), a trigger/function has to do the job.
4 - the function/trigger add the next available value, which the default is 1000 and it's stored on companies.client_code_increment
Lucas
On 4/20/2016 2:51 PM, drum.lucas@gmail.com wrote:
1 - The customer can add any value into users.code column2 - The customer can chose between add or not add the value on users.code column3 - If users.code is null (because the customer's chosen not to add any value in there), a trigger/function has to do the job.4 - the function/trigger add the next available value, which the default is 1000 and it's stored on companies.client_code_increment
why don't you use a SEQUENCE for this?
... DEFAULT nextval('sequencename') ...
initialize the sequence to 1000.
done.
still sounds pretty sketchy.
-- john r pierce, recycling bits in santa cruz
1 - The customer can add any value into users.code column2 - The customer can chose between add or not add the value on users.code column3 - If users.code is null (because the customer's chosen not to add any value in there), a trigger/function has to do the job.4 - the function/trigger add the next available value, which the default is 1000 and it's stored on companies.client_code_increment
And if the value you compute happens to be one the client choose themselves what happens?
The procedural logic for this shouldn't be too complicated. The documentation can give you syntax and capabilities.
The bigger problem is an ill-defined process and unexpected conflicts both with the data itself (my comment above) and with concurrency.
I wouldn't give the user a choice. Either they always pick values or they never pick values. I'd give them a function they can choose to utilize to auto-generate values if they do not wish to select their own. Document the algorithm and if they choose to mix-and-match they should avoid conflicting algorithms.
I'd probably use CREATE SEQUENCE and pull numbers from that using nextval instead of trying to code a custom sequence generator with meta-data stored on a company column. I'd relax the "one sequence per company" behavior if possible.
David J.
On 04/20/2016 02:51 PM, drum.lucas@gmail.com wrote:
>
>
>
>
> Well.. will try ONE more time then.
>
>
> 1 - The customer can add any value into users.code column
> 2 - The customer can chose between *add or no**t* add the value on
> users.code column
> 3 - If users.code is null (because the customer's chosen not to add any
> value in there), a trigger/function has to do the job.
> 4 - the function/trigger add the next available value, which the default
> is 1000 and it's stored on companies.client_code_increment
In addition to what John and David said, from your original post:
"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"();"
If I am following, this duplicates the information in
companies.client_code_increment, in that they both return the last
non-user code. Of course this assumes, as David mentioned, that the
client is not using a numeric code system. Then you are left trying to
figure whether a number is 'your' number or 'their' number?
>
> Lucas
>
--
Adrian Klaver
adrian.klaver@aklaver.com
If I am following, this duplicates the information in companies.client_code_increment, in that they both return the last non-user code. Of course this assumes, as David mentioned, that the client is not using a numeric code system. Then you are left trying to figure whether a number is 'your' number or 'their' number?
The customer can add any value into users.code:
code CHARACTER VARYING,
But he also can let it blank/null if he wants to.
That's when the trigger do its job.. Put a value (starting in 1000) in that column.
Of course that has to be unique, as nobody can use the same value of others.
- I was hoping you cans could help me to start doing the function...
On 4/20/2016 3:33 PM, drum.lucas@gmail.com wrote:
The customer can add any value into users.code:code CHARACTER VARYING,
what if he puts in a non-unique value ?
But he also can let it blank/null if he wants to.That's when the trigger do its job.. Put a value (starting in 1000) in that column.Of course that has to be unique, as nobody can use the same value of others.
thats really hard to do correctly under a concurrent workload
- I was hoping you cans could help me to start doing the function...
-- john r pierce, recycling bits in santa cruz
On 04/20/2016 03:33 PM, drum.lucas@gmail.com wrote: > > > If I am following, this duplicates the information in > companies.client_code_increment, in that they both return the last > non-user code. Of course this assumes, as David mentioned, that the > client is not using a numeric code system. Then you are left trying > to figure whether a number is 'your' number or 'their' number? > > > The customer can add any value into users.code: > > code CHARACTER VARYING, > > > But he also can let it blank/null if he wants to. > That's when the trigger do its job.. Put a value (starting in 1000) in > that column. Understood, but what happens if the customer has been using a code of: ... 998, 999, 1000 They then left the code null on the next two items and your function stuck in 1001 and 1002. Then they figured out what they wanted to do with the codes on their end but wanted the items to have codes of 1002, 1001 for the items you coded 1001, 1002 respectively. > > Of course that has to be unique, as nobody can use the same value of others. Unique within a customer, which is what your code implied or unique across all customers? > > > - I was hoping you cans could help me to start doing the function... Well, I am with David on this, either the customer is totally in charge of the codes or you are. The thought of mixing systems gives me a headache. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 20, 2016 at 03:55:50PM -0700, Adrian Klaver wrote:
> > If I am following, this duplicates the information in
> > companies.client_code_increment, in that they both return the last
> > non-user code. Of course this assumes, as David mentioned, that the
> > client is not using a numeric code system. Then you are left trying
> > to figure whether a number is 'your' number or 'their' number?
> >
> >
> >The customer can add any value into users.code:
> >
> > code CHARACTER VARYING,
> >
> >
> >But he also can let it blank/null if he wants to.
> >That's when the trigger do its job.. Put a value (starting in 1000) in
> >that column.
>
> Understood, but what happens if the customer has been using a code of:
>
> ... 998, 999, 1000
>
> They then left the code null on the next two items and your function stuck
> in 1001 and 1002. Then they figured out what they wanted to do with the
> codes on their end but wanted the items to have codes of 1002, 1001 for the
> items you coded 1001, 1002 respectively.
>
> >
> >Of course that has to be unique, as nobody can use the same value of others.
>
> Unique within a customer, which is what your code implied or unique across
> all customers?
>
> >
> >
> >- I was hoping you cans could help me to start doing the function...
>
> Well, I am with David on this, either the customer is totally in charge of
> the codes or you are. The thought of mixing systems gives me a headache.
How about _two_ columns (pseudo code)
.user_picked_code (can be null)
.assigned_code not null serial starts_with 1000
would that help any ? .assigned_code would always be set
from a sequence but .user_picked_code would be _used_ (say,
via a view) IF NOT NULL.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
This is what I've done:
-- 1 - Creating the Sequence:
CREATE SEQUENCE users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;
-- 2 - Setting the DEFAULT
ALTER TABLE public.users ALTER COLUMN code SET DEFAULT NEXTVAL('users_code_seq');
-- 3 - Setting the column as NOT NULL;
ALTER TABLE public.users ALTER COLUMN code SET NOT NULL;
-- 4 - Setting the trigger
CREATE TRIGGER public.update_code_column
BEFORE UPDATE OR INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE public.users_code_seq;
-- 5 - Creating a CONSTRAINT UNIQUE
ALTER TABLE public.users
ADD CONSTRAINT uc_users_code UNIQUE("code");
Is that right?
Am I missing something?
Cheers
Lucas
This is what I've done:-- 1 - Creating the Sequence:CREATE SEQUENCE users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;-- 2 - Setting the DEFAULTALTER TABLE public.users ALTER COLUMN code SET DEFAULT NEXTVAL('users_code_seq');-- 3 - Setting the column as NOT NULL;
ALTER TABLE public.users ALTER COLUMN code SET NOT NULL;-- 4 - Setting the triggerCREATE TRIGGER public.update_code_column
BEFORE UPDATE OR INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE public.users_code_seq;-- 5 - Creating a CONSTRAINT UNIQUEALTER TABLE public.users
ADD CONSTRAINT uc_users_code UNIQUE("code");Is that right?Am I missing something?
The definition for "public.users_code_seq" - which is an odd name to choose, especially given you already have a sequence of the same name.
David J.
On 3 May 2016 at 12:44, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
This is what I've done:-- 1 - Creating the Sequence:CREATE SEQUENCE users_code_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1000;
CACHE 1;-- 2 - Setting the DEFAULTALTER TABLE public.users ALTER COLUMN code SET DEFAULT NEXTVAL('users_code_seq');-- 3 - Setting the column as NOT NULL;
ALTER TABLE public.users ALTER COLUMN code SET NOT NULL;-- 4 - Setting the triggerCREATE TRIGGER public.update_code_column
BEFORE UPDATE OR INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE public.users_code_seq;-- 5 - Creating a CONSTRAINT UNIQUEALTER TABLE public.users
ADD CONSTRAINT uc_users_code UNIQUE("code");Is that right?Am I missing something?CheersLucas
Well.. I don't need to add a constraint if I already have a default value, that's right...
Anyway...
hmm.. actually.. it's a little bit different what I've done and what I need =(
1 - each user on the public.users table, is part of a company. Each company has a unique company_id
2 - Remember the default 1000 value? That value is per company.
Example:
Company Test1 - Company_id = 1
- user john01 = users.code: 1000
- user john02 = users.code: Nz
- user john03 = users.code: 1001
- user john04 = users.code: Nz
Company Test2 - Company_id = 2
- user matt01 = users.code: Text1
- user matt02 = users.code: 1000
- user matt03 = users.code: 1001
- user matt04 = users.code: 1002
Company Test3 - Company_id = 3
- user luke01 = users.code: 1000
- user luke02 = users.code: 1001
- user luke03 = users.code: Text2
- user luke04 = users.code: 1002
So, the default value is 1000 for EACH company. And the users must get the nextval value from there.
How can I do that?
Or at least if you guys can give me a direction...
Cheers
Well.. I don't need to add a constraint if I already have a default value, that's right...
Wrong
David J.
On Tue, May 3, 2016 at 1:21 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Well.. I don't need to add a constraint if I already have a default value, that's right...WrongDavid J.
What you need is a TRIGGER function & TRIGGER that will select and assign the next users_code based on company_id.
I'm not going to write the whole thing for you, but here is part of the trigger function logic.
ELSEIF NEW.company.id = 2 THEN
NEW.users_code = NEXTVAL(c2_users_code_seq);
ELSE
< something bad happened because NEW.company_id was not valid ?
END IF;
Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to design your database.
You need a firm understanding of logical design & flow, otherwise you will be wasting your time.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com> wrote:
On Tue, May 3, 2016 at 1:21 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:Well.. I don't need to add a constraint if I already have a default value, that's right...WrongDavid J.NEW.users_code = NEXTVAL(c1_users_code_seq);What you need is a TRIGGER function & TRIGGER that will select and assign the next users_code based on company_id.I'm not going to write the whole thing for you, but here is part of the trigger function logic.eg: IF NEW.company_id = 1 THENELSEIF NEW.company.id = 2 THENNEW.users_code = NEXTVAL(c2_users_code_seq);ELSE< something bad happened because NEW.company_id was not valid ?END IF;
Do I have to have one sequence peer company_id ? There will be thousands.. isn't there a better way to do that?
Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to design your database.You need a firm understanding of logical design & flow, otherwise you will be wasting your time.
That's what I'm doing.. Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com> wrote:On Tue, May 3, 2016 at 1:21 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:Well.. I don't need to add a constraint if I already have a default value, that's right...WrongDavid J.NEW.users_code = NEXTVAL(c1_users_code_seq);What you need is a TRIGGER function & TRIGGER that will select and assign the next users_code based on company_id.I'm not going to write the whole thing for you, but here is part of the trigger function logic.eg: IF NEW.company_id = 1 THENELSEIF NEW.company.id = 2 THENNEW.users_code = NEXTVAL(c2_users_code_seq);ELSE< something bad happened because NEW.company_id was not valid ?END IF;Do I have to have one sequence peer company_id ? There will be thousands.. isn't there a better way to do that?
Sequences have some very nice concurrency properties built into them. Using them is the least problematic solution though within the framework you are proposing they do have some tradeoffs to consider.
I would say that a proposal to create a huge if/else block is not very realistic. Sequences have names and you should be storing, passing around, and using those names. You cannot avoid having thousands of names and objects defined, and I have no clue how efficient sequence name lookup is (probably reasonably so), but at least the rest of the logic should be devoid of any huge conditional blocks like the above. If you think you need such a block typically you want to encode said information into a table instead.
You'll need the table regardless but if you want to avoid creating sequence objects you'd have to write custom functions, similar to (subset of...) those implemented for sequence manipulation, to talk query said table.
So, since sequence us just a record on a table, the only differences is it is system table and not a user table, I would recommend simply using sequences unless and until you can prove they are inadequate to your needs AND can prove that whatever custom implementation you write is better.
The only other reasonable option is change your model and requirements to something less complex.
Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to design your database.You need a firm understanding of logical design & flow, otherwise you will be wasting your time.
That's what I'm doing.. Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
The purpose of this list is whatever people are willing to make of it - within reason. I share the sentiment that your particular method of education is becoming burdensome to the people who volunteer their time on these lists to answer questions. As the alternative is to simply stop replying to your emails be grateful that someone was at least willing to tell you to try other avenues of education.
David J.
On 05/03/2016 02:27 PM, drum.lucas@gmail.com wrote: > > > On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com > <mailto:melvin6925@gmail.com>> wrote: > > > > On Tue, May 3, 2016 at 1:21 AM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > > Well.. I don't need to add a constraint if I already have a > default value, that's right... > > > Wrong > David J. > > > What you need is a TRIGGER function & TRIGGER that will select and > assign the next users_code based on company_id. > I'm not going to write the whole thing for you, but here is part of > the trigger function logic. > > eg: IF NEW.company_id = 1 THEN > NEW.users_code = NEXTVAL(c1_users_code_seq); > ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN > NEW.users_code = NEXTVAL(c2_users_code_seq); > ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN > NEW.users_code = NEXTVAL(c3_users_code_seq); > ... > ... > ELSE > < something bad happened because NEW.company_id was > not valid ? > END IF; > > > > > Do I have to have one sequence peer company_id ? There will be > thousands.. isn't there a better way to do that? > > Seriously, get yourself the books I have recommended and study them > BEFORE you continue attempting to design your database. > You need a firm understanding of logical design & flow, otherwise > you will be wasting your time. > > > That's what I'm doing.. Studying.. asking for some help to get a better > understand.... isn't this the purpose of this mail list? I think the intent being expressed was to: study --> sketch out design/schema --> ask list whether previous is correct --> make corrections or not --> implement. Instead what seems to have happened is: implement --> get painted into corner --> ask how to escape corner. Hence the frustration on the part of list members. -- Adrian Klaver adrian.klaver@aklaver.com
On 05/03/2016 02:27 PM, drum.lucas@gmail.com wrote: > > > On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com > <mailto:melvin6925@gmail.com>> wrote: > > > > On Tue, May 3, 2016 at 1:21 AM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > > Well.. I don't need to add a constraint if I already have a > default value, that's right... > > > Wrong > David J. > > > What you need is a TRIGGER function & TRIGGER that will select and > assign the next users_code based on company_id. > I'm not going to write the whole thing for you, but here is part of > the trigger function logic. > > eg: IF NEW.company_id = 1 THEN > NEW.users_code = NEXTVAL(c1_users_code_seq); > ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN > NEW.users_code = NEXTVAL(c2_users_code_seq); > ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN > NEW.users_code = NEXTVAL(c3_users_code_seq); > ... > ... > ELSE > < something bad happened because NEW.company_id was > not valid ? > END IF; > > > > > Do I have to have one sequence peer company_id ? There will be > thousands.. isn't there a better way to do that? A sequence just keeps on incrementing. If you want a gapless sequence for each company that each increment independently of each other then yes you will need a separate sequence for each. For an alternate strategy see this related thread: http://www.postgresql.org/message-id/2926B083-33C9-4648-8635-BC293C70ED45@ravnalaska.net in particular this link from the thread: http://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com > > Seriously, get yourself the books I have recommended and study them > BEFORE you continue attempting to design your database. > You need a firm understanding of logical design & flow, otherwise > you will be wasting your time. > > > That's what I'm doing.. Studying.. asking for some help to get a better > understand.... isn't this the purpose of this mail list? -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/03/2016 02:27 PM, drum.lucas@gmail.com wrote:
On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com
<mailto:melvin6925@gmail.com>> wrote:
On Tue, May 3, 2016 at 1:21 AM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
Well.. I don't need to add a constraint if I already have a
default value, that's right...
Wrong
David J.
What you need is a TRIGGER function & TRIGGER that will select and
assign the next users_code based on company_id.
I'm not going to write the whole thing for you, but here is part of
the trigger function logic.
eg: IF NEW.company_id = 1 THEN
NEW.users_code = NEXTVAL(c1_users_code_seq);
ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN
NEW.users_code = NEXTVAL(c2_users_code_seq);
ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN
NEW.users_code = NEXTVAL(c3_users_code_seq);
...
...
ELSE
< something bad happened because NEW.company_id was
not valid ?
END IF;
Do I have to have one sequence peer company_id ? There will be
thousands.. isn't there a better way to do that?
A sequence just keeps on incrementing. If you want a gapless sequence for each company that each increment independently of each other then yes you will need a separate sequence for each.
For an alternate strategy see this related thread:
http://www.postgresql.org/message-id/2926B083-33C9-4648-8635-BC293C70ED45@ravnalaska.net
in particular this link from the thread:
http://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com--
Seriously, get yourself the books I have recommended and study them
BEFORE you continue attempting to design your database.
You need a firm understanding of logical design & flow, otherwise
you will be wasting your time.
That's what I'm doing.. Studying.. asking for some help to get a better
understand.... isn't this the purpose of this mail list?
Adrian Klaver
adrian.klaver@aklaver.com
>Do I have to have one sequence peer company_id ? There will be thousands.. isn't there a better way to do that?
I agree that having thousands of sequences can be hard to manage,
especially in a function, but you did not state that fact before,
only that you wanted separate sequences for each company. That
being said, here is an alternate solution.
1. CREATE TABLE company_seqs
(company_id bigint NOT NULL,
last_seq bigint NOT NULL,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);
2. Every time you create a new company, you must insert the
corresponding company_id and last_seq [which will be 1}
into the company_seqs table.
3. Change the trigger function logic to something like below:
DECLARE
v_seq_num INTEGER;
BEGIN
SELECT last_seq
FROM company_seqs
WHERE company_id = NEW.company_id INTO v_seq_num;
UPDATE company_seqs
SET last_seq = last_seq + 1
WHERE company_id = NEW.company_id;
new.users_code = v_seq_num;
Now, just a quick comment. As has been said before, wanting a sequence with no gaps for
each user in each company is a bit unrealistic and serves no purpose. For example,
company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and is deleted?
As long as you have a unique user_code for each user, it does not matter.
>... Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
Yes, but at the same time, it is evident that you are trying to design the database before you have
a valid understanding of database design. To wit, you are putting the cart before the horse.
While this list is here to help you, it is not meant as a DATABASE 101 course.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I agree that having thousands of sequences can be hard to manage,
especially in a function, but you did not state that fact before,
only that you wanted separate sequences for each company. That
being said, here is an alternate solution.
Yep.. that was my mistake.
1. CREATE TABLE company_seqs
(company_id bigint NOT NULL,
last_seq bigint NOT NULL,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);
2. Every time you create a new company, you must insert the
corresponding company_id and last_seq [which will be 1}
into the company_seqs table.
ok that's right.. just a comment here...
the value inside the users.code column must start with 1000 and not 1.
So, it would be 1001, 1002, 1003, etc.
The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?
3. Change the trigger function logic to something like below:
DECLARE
v_seq_num INTEGER;
BEGIN
SELECT last_seq
FROM company_seqs
WHERE company_id = NEW.company_id INTO v_seq_num;
UPDATE company_seqs
SET last_seq = last_seq + 1
WHERE company_id = NEW.company_id;
new.users_code = v_seq_num;
not sure what v_seq_num is...
Now, just a quick comment. As has been said before, wanting a sequence with no gaps for
each user in each company is a bit unrealistic and serves no purpose. For example,
company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and is deleted?
As long as you have a unique user_code for each user, it does not matter.
>... Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
Yes, but at the same time, it is evident that you are trying to design the database before you have
a valid understanding of database design. To wit, you are putting the cart before the horse.
While this list is here to help you, it is not meant as a DATABASE 101 course.
Yep.. got it
On Tue, May 3, 2016 at 7:53 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
I agree that having thousands of sequences can be hard to manage,
especially in a function, but you did not state that fact before,
only that you wanted separate sequences for each company. That
being said, here is an alternate solution.Yep.. that was my mistake.
1. CREATE TABLE company_seqs
(company_id bigint NOT NULL,
last_seq bigint NOT NULL,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);
2. Every time you create a new company, you must insert the
corresponding company_id and last_seq [which will be 1}
into the company_seqs table.ok that's right.. just a comment here...the value inside the users.code column must start with 1000 and not 1.So, it would be 1001, 1002, 1003, etc.The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?
3. Change the trigger function logic to something like below:
DECLARE
v_seq_num INTEGER;
BEGIN
SELECT last_seq
FROM company_seqs
WHERE company_id = NEW.company_id INTO v_seq_num;
UPDATE company_seqs
SET last_seq = last_seq + 1
WHERE company_id = NEW.company_id;
new.users_code = v_seq_num;not sure what v_seq_num is...
Now, just a quick comment. As has been said before, wanting a sequence with no gaps for
each user in each company is a bit unrealistic and serves no purpose. For example,
company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and is deleted?
As long as you have a unique user_code for each user, it does not matter.
>... Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
Yes, but at the same time, it is evident that you are trying to design the database before you have
a valid understanding of database design. To wit, you are putting the cart before the horse.
While this list is here to help you, it is not meant as a DATABASE 101 course.Yep.. got it
>The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?
Really, how hard is it to change 1 to 1000?
INSERT INTO company_seqs
(company_id, last_seq )
(company_id, last_seq )
VALUES
( {whatever_new_company_id_id}, 1000};
Really, you need to start thinking for yourself, but first _learn database design_! That is why I recommended those books to you.
>not sure what v_seq_num is...
It is a variable in the TRIGGER FUNCTION, Again, you need to learn first.
Try reading the docs!
http://www.postgresql.org/docs/9.2/interactive/index.html
http://www.postgresql.org/docs/9.2/interactive/plpgsql.html
http://www.postgresql.org/docs/9.2/interactive/index.html
http://www.postgresql.org/docs/9.2/interactive/plpgsql.html
http://www.postgresqltutorial.com/postgresql-stored-procedures/
http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
Use google search for additional information on PostgreSQL
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, May 3, 2016 at 7:53 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:I agree that having thousands of sequences can be hard to manage,
especially in a function, but you did not state that fact before,
only that you wanted separate sequences for each company. That
being said, here is an alternate solution.Yep.. that was my mistake.
1. CREATE TABLE company_seqs
(company_id bigint NOT NULL,
last_seq bigint NOT NULL,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);
2. Every time you create a new company, you must insert the
corresponding company_id and last_seq [which will be 1}
into the company_seqs table.ok that's right.. just a comment here...the value inside the users.code column must start with 1000 and not 1.So, it would be 1001, 1002, 1003, etc.The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?
3. Change the trigger function logic to something like below:
DECLARE
v_seq_num INTEGER;
BEGIN
SELECT last_seq
FROM company_seqs
WHERE company_id = NEW.company_id INTO v_seq_num;
UPDATE company_seqs
SET last_seq = last_seq + 1
WHERE company_id = NEW.company_id;
new.users_code = v_seq_num;not sure what v_seq_num is...
Now, just a quick comment. As has been said before, wanting a sequence with no gaps for
each user in each company is a bit unrealistic and serves no purpose. For example,
company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and is deleted?
As long as you have a unique user_code for each user, it does not matter.
>... Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
Yes, but at the same time, it is evident that you are trying to design the database before you have
a valid understanding of database design. To wit, you are putting the cart before the horse.
While this list is here to help you, it is not meant as a DATABASE 101 course.Yep.. got it
>The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?Really, how hard is it to change 1 to 1000?INSERT INTO company_seqs
(company_id, last_seq )VALUES( {whatever_new_company_id_id}, 1000};Really, you need to start thinking for yourself, but first _learn database design_! That is why I recommended those books to you.
>not sure what v_seq_num is...It is a variable in the TRIGGER FUNCTION, Again, you need to learn first.Try reading the docs!
http://www.postgresql.org/docs/9.2/interactive/index.html
http://www.postgresql.org/docs/9.2/interactive/plpgsql.htmlhttp://www.postgresqltutorial.com/postgresql-stored-procedures/
http://www.postgresqltutorial.com/creating-first-trigger-postgresql/Use google search for additional information on PostgreSQL
+1
And I'll add to the doc list. Make sure to follow links around and not just read the linked pages.
From: David G. Johnston Sent: Tuesday, May 03, 2016 2:46 PM
To: drum.lucas@gmail.com
…The only other reasonable option is change your model and requirements to something less complex.
Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to design your database.
You need a firm understanding of logical design & flow, otherwise you will be wasting your time.
That's what I'm doing.. Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list?
The purpose of this list is whatever people are willing to make of it - within reason. I share the sentiment that your particular method of education is becoming burdensome to the people who volunteer their time on these lists to answer questions. As the alternative is to simply stop replying to your emails be grateful that someone was at least willing to tell you to try other avenues of education.
David J.
+1 +1 +1 +1: Lucas – David really nails it: you’ve stopped relying on yourself and are relying on the mercy and generosity of strangers to solve your problems. It takes a lot of time to read, dissect, understand and then comment on your (or anyone’s) emails, and you’ve proven adept at consuming much of the available oxygen on the list. Please a bit more considerate - you’ve received an amazing amount of solid guidance and advice. I can tell you that when I see your name as the author, I now normally delete the email – that’s NOT the response you want, right?
I’ve worked with a bunch of junior developers over the years, some of whom hit a wall and just sit there, waiting for someone to fix them. Those junior developers “never” become senior developers because their peers soon lose interest in collaborating with them, if you catch my drift…
Mike Sofen
- This is what I did...
-- Creating the table CREATE TABLE public.company_seqs (company_id BIGINT NOT NULL, last_seq BIGINT NOT NULL DEFAULT 1000, CONSTRAINT company_seqs_pk PRIMARY KEY (company_id) ); -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() RETURNS "trigger" AS ' BEGIN UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id; SELECT INTO NEW.code last_seq FROM public.company_seqs WHERE company_id = NEW.company_id; END IF; RETURN new; END ' LANGUAGE 'plpgsql' VOLATILE; -- Creating the trigger CREATE TRIGGER tf_users_code_seq BEFORE INSERT ON public.users FOR EACH ROW EXECUTE PROCEDURE users_code_seq();
When inserting data:
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test2@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test3@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','inserting my own data code column','1');On the first query, nothing happens on the users.code column. The column is null.
On the second query, I can see the "inserting my own data code column" inserted into the code column.
This means my Trigger function is not working.. I don't know why.
On Tuesday, May 3, 2016, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
This sounds like database pollution...
- This is what I did...
-- Creating the table CREATE TABLE public.company_seqs (company_id BIGINT NOT NULL, last_seq BIGINT NOT NULL DEFAULT 1000, CONSTRAINT company_seqs_pk PRIMARY KEY (company_id) ); -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() RETURNS "trigger" AS ' BEGIN UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id; SELECT INTO NEW.code last_seq FROM public.company_seqs WHERE company_id = NEW.company_id; END IF; RETURN new; END ' LANGUAGE 'plpgsql' VOLATILE; -- Creating the trigger CREATE TRIGGER tf_users_code_seq BEFORE INSERT ON public.users FOR EACH ROW EXECUTE PROCEDURE users_code_seq();INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test2@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1'); INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test3@test.com','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','inserting my own data code column','1');
On the first query, nothing happens on the users.code column. The column is null.
On the second query, I can see the "inserting my own data code column" inserted into the code column.
This means my Trigger function is not working.. I don't know why.
I'd suggest writing a psql script that can be executed against an empty database and does everything you want it to do. Execute it against an empty database. Capture the output to a file with echo all. Post the script and the output.
Also, try "update returning"
I would also advise adding STRICT.
David J.
> On Tuesday, May 3, 2016, drum.lucas@gmail.com <mailto:drum.lucas@gmail.com> <drum.lucas@gmail.com > <mailto:drum.lucas@gmail.com>> wrote: > > * This is what I did... > > |-- Creating the table > CREATE TABLE public.company_seqs > (company_id BIGINTNOT NULL, > last_seq BIGINTNOT NULL DEFAULT 1000, > CONSTRAINT company_seqs_pkPRIMARY KEY (company_id) > ); > > > -- Creating the function > > CREATE OR REPLACEFUNCTION users_code_seq() > RETURNS"trigger" AS > ' > BEGIN > UPDATE public.company_seqs > SET last_seq = (last_seq + 1) > WHERE company_id = NEW.company_id; > SELECT INTO NEW.code last_seq > FROM public.company_seqs WHERE company_id = NEW.company_id; > END IF; > RETURN new; > END > ' > > LANGUAGE'plpgsql' VOLATILE; > > -- Creating the trigger > CREATE TRIGGER tf_users_code_seq > BEFOREINSERT > ON public.users > FOR EACHROW > EXECUTE PROCEDURE users_code_seq();| 1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old post that the above looks an awful lot similar too, it sure would be nice to see some attribution rather than claiming it as your own with "...what *I* did..." > > > When inserting data: > > |INSERT INTO public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test2@test.com <javascript:_e(%7B%7D,'cvml','test2@test.com');>','bucefalo','0','2016-05-03 00:01:01','2016-05-0300:01:01',default,'1'); > > INSERT INTO public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'test3@test.com <javascript:_e(%7B%7D,'cvml','test3@test.com');>','bucefalo','0','2016-05-03 00:01:01','2016-05-0300:01:01','inserting my own data code column','1');| > > * > > On the first query, nothing happens on the users.code column. The column is null. > > * > > On the second query, I can see the "inserting my own data code column" inserted into the > code column. |This means my Trigger function is not working.. I don't know why.| 2) Does the public.company_seqs have any rows in it? 3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between company and company_seqs, put the last_seq column in the company table. -- Berend
1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old post that the above looks an awful lot similar too, it sure would be nice to see some attribution
rather than claiming it as your own with "...what *I* did..."
I would expect a minimum of respect from the members of this list, but seems you got none. If someone would need my help, I'd never insult him/her like you guys are doing.
If my questions are too "child" for you, please, do not answer them. Ignore the emails... Isn't that simple?
Talking to me like you guys are talking, is what I call "a ten-years old post".
3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between company and company_seqs, put the last_seq column in the company table.
There is no company table, my friend.
1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old post that the above looks an awful lot similar too, it sure would be nice to see some attribution
rather than claiming it as your own with "...what *I* did..."I would expect a minimum of respect from the members of this list, but seems you got none. If someone would need my help, I'd never insult him/her like you guys are doing.If my questions are too "child" for you, please, do not answer them. Ignore the emails... Isn't that simple?Talking to me like you guys are talking, is what I call "a ten-years old post".
I'm not sure is this was meant to be a play on words but the original use of the phrase meant "a post written 10 years ago" - not that you are acting like a 10 year old.
FWIW I didn't read any kind of "stealing of credit" in what you wrote - nor likely did most people. That particular observation came out of left field.
David J.
David G. Johnston wrote: > On Wed, May 4, 2016 at 2:57 PM, drum.lucas@gmail.com > ... > > I would expect a minimum of respect from the members of this list, > but seems you got none. If someone would need my help, I'd never > insult him/her like you guys are doing. > > If my questions are too "child" for you, please, do not answer them. > Ignore the emails... Isn't that simple? > > Talking to me like you guys are talking, is what I call "a ten-years > old post". > > > I'm not sure is this was meant to be a play on words but the original > use of the phrase meant "a post written 10 years ago" - not that you are > acting like a 10 year old. My apologies for causing that misunderstanding. The phrase was definitely not meant to imply the post of a 10-year old childs intelligence (although I suppose it would have been impressive) ... I was considerably older than that when I posted that example of compound sequences ... 10-years ago. > > FWIW I didn't read any kind of "stealing of credit" in what you wrote - > nor likely did most people. That particular observation came out of > left field. As I said, I might be over-sensitive on this ... being the original author of the copied example implementation.
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id;
ELSEIF NEW.code IS NULL THEN
SELECT last_seq INTO code FROM public.company_seqs WHERE company_id = NEW.company_id ORDER BY last_seq DESC;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
- The command above, does not insert the last_seq into users.code column. And I don't know why.
If I comment the line: ELSEIF NEW.code IS NULL THEN, the data is inserted into the users.code column.
But as the customer can add data into that column, I only insert the last_seq if he/she didn't insert anything.
So I need that ELSEIF NEW.code IS NULL THEN.
- What am I missing?
Thanks
Lucas
On 5 May 2016 at 16:56, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id;
ELSEIF NEW.code IS NULL THEN
SELECT last_seq INTO code FROM public.company_seqs WHERE company_id = NEW.company_id ORDER BY last_seq DESC;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;- The command above, does not insert the last_seq into users.code column. And I don't know why.If I comment the line: ELSEIF NEW.code IS NULL THEN, the data is inserted into the users.code column.But as the customer can add data into that column, I only insert the last_seq if he/she didn't insert anything.So I need that ELSEIF NEW.code IS NULL THEN.- What am I missing?ThanksLucas
I got it.. it's working now.
Thanks
On 5/4/2016 9:56 PM, drum.lucas@gmail.com wrote:
If I comment the line: ELSEIF NEW.code IS NULL THEN, the data is inserted into the users.code column.
in the table definition, whats the default value of 'code' ?
-- john r pierce, recycling bits in santa cruz
I'm just having some problem when doing:
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES (66,'test22@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01','15');
- see that I'm not providing the "code" column value? If I run the query above, I get the following error:
ERROR: query returned no rows
CONTEXT: PL/pgSQL function users_code_seq() line 7 at SQL statement
- If I include the code column with a default value:
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (4,'test4@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'2');
I get the same error
- Please, if anyone can help with that.. I'd appreciate it.
The final function code is:
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
IF NEW.code IS NULL THEN
SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
END IF;
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
companies.client_code_increment:
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;
Trigger:
CREATE TRIGGER tf_users_code_seq
BEFORE INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE users_code_seq();
Tests I'm doing:
1 - Insert data into companies table:
INSERT INTO public.companies(id,name,created_at,updated_at,client_code_increment) VALUES (1,'Company 1','2016-05-03 00:01:01','2016-05-03 00:01:01',default); - PASS
INSERT INTO public.companies(id,name,created_at,updated_at,client_code_increment) VALUES (2,'Company 2','2016-05-03 00:01:01','2016-05-03 00:01:01',default); - PASS
2 - insert data into users table:
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (1,'test1@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01','default','2'); - PASS
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (2,'test2@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1'); - NO PASS
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (3,'test3@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'2'); - NO PASS
Cheers
drum.lucas@gmail.com wrote: > I'm just having some problem when doing: > > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES > (66,'test22@test.com <mailto:test22@test.com>','password','0','2016-05-03 00:01:01','2016-05-03 > 00:01:01','15'); > > > - see that I'm not providing the "code" column value? If I run the query above, I get the following > error: > > ERROR: query returned no rows > CONTEXT: PL/pgSQL function users_code_seq() line 7 at SQL statement > > > - If I include the code column with a default value: > > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES > (4,'test4@test.com <mailto:test4@test.com>','password','0','2016-05-03 00:01:01','2016-05-03 > 00:01:01',default,'2'); > > I get the same error > > - Please, if anyone can help with that.. I'd appreciate it. > > *The final function code is:* > > CREATE OR REPLACE FUNCTION users_code_seq() > RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = > NEW.id ORDER BY client_code_increment DESC; I am pretty sure the above line is wrong. NEW.id refers to users.id, not the companies.id. Also, the implementation presents a potential race condition, e.g., if two different sessions attempt an insert almost simultaneously. > END IF; > IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE > id = NEW.id; Ditto w.r.t. NEW.id. > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql;
SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id =
NEW.id ORDER BY client_code_increment DESC;
I am pretty sure the above line is wrong. NEW.id refers to users.id, not the companies.id. Also, the implementation presents a potential race condition, e.g., if two different sessions attempt an insert almost simultaneously.
I don't think so..
Even because if I change that to company_id, I get the error:
ERROR: column "company_id" does not exist
END IF;
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE
id = NEW.id;
Ditto w.r.t. NEW.id.
Same as above
> On 05 May 2016, at 8:42, drum.lucas@gmail.com wrote:
> The final function code is:
>
> CREATE OR REPLACE FUNCTION users_code_seq()
> RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY
client_code_incrementDESC;
^^^^^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It probably gets interpreted as a column
name.
> END IF;
> IF (TG_OP = 'INSERT') THEN
> UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.id;
> END IF;
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
> On 05 May 2016, at 8:42, drum.lucas@gmail.com wrote:
> The final function code is:
>
> CREATE OR REPLACE FUNCTION users_code_seq()
> RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
^^^^^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It probably gets interpreted as a column name.
No, its a sanity check/assertion. If that trips its because there is no company having a value of NEW.id on the public.companies table. If that is OK then remove the STRICT but if you are indeed expecting a record to be present and it is not it is correctly telling you that there is a problem in the data. Namely that said company needs to be added to the table.
David J.
On 6 May 2016 at 02:29, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On 05 May 2016, at 8:42, drum.lucas@gmail.com wrote:
> The final function code is:
>
> CREATE OR REPLACE FUNCTION users_code_seq()
> RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
^^^^^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It probably gets interpreted as a column name.No, its a sanity check/assertion. If that trips its because there is no company having a value of NEW.id on the public.companies table. If that is OK then remove the STRICT but if you are indeed expecting a record to be present and it is not it is correctly telling you that there is a problem in the data. Namely that said company needs to be added to the table.David J.
Taking off the "STRICT", the errors were gone. But still, it's not working. Please have a look below.
If I use the other table:
CREATE TABLE public.company_seqs
(company_id BIGINT NOT NULL,
last_seq BIGINT NOT NULL DEFAULT 1000,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);
It works fine.. the problem is when I try to use the companies table.. which is already there and I just add another column named: client_code_increment
haven't found the problem yet...
On 6 May 2016 at 02:29, David G. Johnston <david.g.johnston@gmail.com> wrote:
> On 05 May 2016, at 8:42, drum.lucas@gmail.com wrote:
> The final function code is:
>
> CREATE OR REPLACE FUNCTION users_code_seq()
> RETURNS "trigger" AS $$
> DECLARE code character varying;
> BEGIN
> IF NEW.code IS NULL THEN
> SELECT client_code_increment INTO STRICT NEW.code FROM public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC;
^^^^^^^
There's your problem. I'm pretty sure the keyword STRICT isn't valid there. It probably gets interpreted as a column name.No, its a sanity check/assertion. If that trips its because there is no company having a value of NEW.id on the public.companies table. If that is OK then remove the STRICT but if you are indeed expecting a record to be present and it is not it is correctly telling you that there is a problem in the data. Namely that said company needs to be added to the table.David J.Taking off the "STRICT", the errors were gone. But still, it's not working. Please have a look below.
So, the error messages are gone - the underlying error still exists.
If I use the other table:CREATE TABLE public.company_seqs
(company_id BIGINT NOT NULL,
last_seq BIGINT NOT NULL DEFAULT 1000,
CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
);It works fine.. the problem is when I try to use the companies table.. which is already there and I just add another column named: client_code_incrementhaven't found the problem yet...
You make this hard to help without a fully self-contained example for people to read.
Berend already identified the problem for you.
1) You attached users_code_seq() to a trigger on the users table.
2) You have a where clause: company_id = NEW.id
3) NEW refers to users
4) NEW.id is obstensibly a USER ID
5) So you are basically saying: WHERE company_id = user_id
6) If you were to get match it would be entirely by accident - say because you used the same integer for both id values
Hope that helps.
David J.
1) You attached users_code_seq() to a trigger on the users table.
yes
2) You have a where clause: company_id = NEW.id3) NEW refers to users4) NEW.id is obstensibly a USER ID
No...
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.id;
END IF;
IF NEW.code IS NULL THEN
SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
I'm updating the companies table... The company table has a column called ID.
If I change that to company_id, I get the error: column "company_id" does not exist, because that column is inside USERS and not COMPANIES.
So as far as I can see here, that command is right.
- If I'm wrong, please, explain to me.
5) So you are basically saying: WHERE company_id = user_id6) If you were to get match it would be entirely by accident - say because you used the same integer for both id values
Just to be clear here:
1 -
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;
2 -
CREATE OR REPLACE FUNCTION users_code_seq()
RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE company_id = NEW.id;
END IF;
IF NEW.code IS NULL THEN
SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
3 -
CREATE TRIGGER tf_users_code_seq
BEFORE INSERT
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE users_code_seq();
2) You have a where clause: company_id = NEW.id3) NEW refers to users4) NEW.id is obstensibly a USER IDNo...
Which one of the three do you disagree with?
If I change that to company_id, I get the error: column "company_id" does not exist, because that column is inside USERS and not COMPANIES.
change that .... what is "that"
Provide the actual code you ran that resulted in "column "company_id" does not exist" Your attempts at brevity are making this harder that it has to be.
David J.
On Thu, May 5, 2016 at 6:17 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
If I change that to company_id, I get the error: column "company_id" does not exist, because that column is inside USERS and not COMPANIES.change that .... what is "that"Provide the actual code you ran that resulted in "column "company_id" does not exist" Your attempts at brevity are making this harder that it has to be.David J.
Drum,
I strongly suggest you learn how to debug your own code.--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
David G. Johnston wrote: > > Berend already identified the problem for you. > Thank you.
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
drum.lucas@gmail.com wrote: > 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 ... Think about it, will (TG_OP = 'INSERT') ever be untrue.
Just an update here:
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.company_id;
The line above was updating the client_code_increment even if the customer was inserting data by hiimself, which is wrong.
The client_code_increment must be updated IF is an insert AND if the customer did not insert data into the code column.
Correction:
IF (TG_OP = 'INSERT') AND NEW.code IS NULL THEN
UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.company_id;
Cheers
Lucas