Re: [GENERAL] Function PostgreSQL 9.2

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 cl

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@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 DEFAU

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: ​Berend already identified the problem for you. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 6:17 PM, David G. Johnston < david.g.johns...@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 actua

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

> > > > 2) You have a where clause: company_id = NEW.id >> 3) NEW refers to users >> 4) NEW.id is obstensibly a USER ID >> > > > No... > > ​Which one of the three do you disagree with? ​

Re: [GENERAL] Function PostgreSQL 9.2

> > > 1) You attached users_code_seq() to a trigger on the users table. > yes > 2) You have a where clause: company_id = NEW.id > 3) NEW refers to users > 4) NEW.id is obstensibly a USER ID > No... CREATE OR REPLACE FUNCTION users_code_seq() >RETURNS "trigger" AS $$ > DECLARE code charac

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 1:22 PM, drum.lu...@gmail.com wrote: > > > On 6 May 2016 at 02:29, David G. Johnston > wrote: > >> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys >> wrote: >> >>> >>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >>> >>> > The final function code is: >>> > >>> >

Re: [GENERAL] Function PostgreSQL 9.2

On 6 May 2016 at 02:29, David G. Johnston wrote: > On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > >> >> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >> >> > The final function code is: >> > >> > CREATE OR REPLACE FUNCTION users_code_seq() >> >RETURNS "trigger" AS $$ >> > D

Re: [GENERAL] Function PostgreSQL 9.2

On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys wrote: > > > On 05 May 2016, at 8:42, drum.lu...@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.co

Re: [GENERAL] Function PostgreSQL 9.2

> On 05 May 2016, at 8:42, drum.lu...@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.co

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@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,'tes...@test.com ','password','0','2016-05-03 00:01:01','2016-05-03 00:

Re: [GENERAL] Function PostgreSQL 9.2

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,'tes...@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

Re: [GENERAL] Function PostgreSQL 9.2

On 5/4/2016 9:56 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On 5 May 2016 at 16:56, drum.lu...@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; >> >>

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: On Wed, May 4, 2016 at 2:57 PM, drum.lu...@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 "

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com wrote: > >> >> 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 a

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com mailto:drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com wrote: > >- This is what I did... > > -- Creating the tableCREATE 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 functio

Re: [GENERAL] Function PostgreSQL 9.2

- This is what I did... -- Creating the tableCREATE 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' BE

Re: [GENERAL] Function PostgreSQL 9.2

From: David G. Johnston Sent: Tuesday, May 03, 2016 2:46 PM To: drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:06 PM, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 7:53 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 7:53 PM, drum.lu...@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 al

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver wrote: > On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: > >> >> >> On 4 May 2016 at 01:18, Melvin Davidson > > wrote: >> >> >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston >> mailto:david.g.johns...@gm

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 2:27 PM, drum.lu...@gmail.com wrote: > On 4 May 2016 at 01:18, Melvin Davidson wrote: >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> Well.. I don't need to add a constraint if I already have a default value,

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] Function PostgreSQL 9.2

On 4 May 2016 at 01:18, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < > david.g.johns...@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

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < david.g.johns...@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

Re: [GENERAL] Function PostgreSQL 9.2

> > > Well.. I don't need to add a constraint if I already have a default value, > that's right... > Wrong David J.

Re: [GENERAL] Function PostgreSQL 9.2

On 3 May 2016 at 12:44, drum.lu...@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 > >

Re: [GENERAL] Function PostgreSQL 9.2

On Mon, May 2, 2016 at 5:44 PM, drum.lu...@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 DEF

Re: [GENERAL] Function PostgreSQL 9.2

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('user

Re: [GENERAL] Function PostgreSQL 9.2

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 usin

Re: [GENERAL] Function PostgreSQL 9.2

On 04/20/2016 03:33 PM, drum.lu...@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 sys

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 3:33 PM, drum.lu...@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 t

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

On 04/20/2016 02:51 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 2:51 PM, drum.lu...@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 th

Re: [GENERAL] Function PostgreSQL 9.2

On 4/20/2016 2:51 PM, drum.lu...@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/fun

Re: [GENERAL] Function PostgreSQL 9.2

On 21 April 2016 at 09:44, Adrian Klaver wrote: > On 04/19/2016 07:34 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 07:34 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

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

Re: [GENERAL] Function PostgreSQL 9.2

On 04/19/2016 03:23 PM, drum.lu...@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

Re: [GENERAL] Function PostgreSQL 9.2

On 20 April 2016 at 10:38, David G. Johnston wrote: > On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com < > drum.lu...@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 gi

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@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

[GENERAL] 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_c