Function PostgreSQL 9.2

On Tue, Apr 19, 2016 at 3:23 PM, drum.lucas@gmail.com <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"();

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:

On Tue, Apr 19, 2016 at 3:23 PM, drum.lucas@gmail.com <
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"();

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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 <mailto: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

Lucas

On 4/20/2016 2:51 PM, drum.lucas@gmail.com wrote:

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

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

On Wed, Apr 20, 2016 at 2:51 PM, drum.lucas@gmail.com <drum.lucas@gmail.com>
wrote:

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

​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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Mon, May 2, 2016 at 5:44 PM, 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 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?

​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 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

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.